Re: [SQL] Prevent double entries ... no simple unique index
Am 12.07.2012 07:14, schrieb Andreas Kretschmer: Marc Mamin wrote: A partial index would do the same, but requires less space: create unique index on log(state) WHERE state IN (0,1); OK, nice :) What if I have those states in a 3rd table? So I can see a state-history of when a state got set by whom. objects ( id serial PK, ... ) events ( id serial PK, object_id integer FK on objects.id, ... ) event_states ( id serial PK, event_id integer FK on events.id, state integer ) There still should only be one event per object that has state 0 or 1. Though here I don't have the object-id within the event_states-table. Is it still possible to have a unique index that needs to span over a join of events and event_states? -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Prevent double entries ... no simple unique index
On Jul 12, 2012, at 4:44, Andreas wrote: > Am 12.07.2012 07:14, schrieb Andreas Kretschmer: >> Marc Mamin wrote: >> >>> A partial index would do the same, but requires less space: >>> >>> create unique index on log(state) WHERE state IN (0,1); >> > > > OK, nice :) > > What if I have those states in a 3rd table? > So I can see a state-history of when a state got set by whom. > > > objects ( id serial PK, ... ) > events ( id serial PK, object_id integer FK on objects.id, ... ) > > event_states ( id serial PK, event_id integer FK on events.id, state > integer ) > > There still should only be one event per object that has state 0 or 1. > Though here I don't have the object-id within the event_states-table. > > Is it still possible to have a unique index that needs to span over a join of > events and event_states? > No, all index columns must come from the same table. You would need to use a trigger-based system to enforce your constraint. You can either have the triggers simply perform validation or you can create a materialized view and create the partial index on that. You could also consider creating an updatable view and avoid directly interacting with the three individual tables. You could also just turn event states into a history table and leave the current state on the event table. David J. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] How does Numeric division determine precision?
Hi, I'm using Postgres 9.1, and wanted to understand how some of the numeric operations work. It seems that is 9.1, numerics that don't have a specified precision and scale are arbitrary scale/precision. For many operations this is straightforward. However, when doing a division operation that does not terminate, I'm curious about how the number of digits is determined. It seems like there is some minimum precision, e.g. >select 1/3::numeric 0. However, when operating on numbers with larger precision: >select .5353535353355353535353/74::numeric 0.0072345072342639912640 .5353535353355353535353 has 22 digits .0072345072342639912640 also has 22 digits, but should the first two 0's after the decimal point count as "precision"? If I then, do the same operation, but move the decimal point on the divisor, I get a different amount of precision: >select .5353535353355353535353/.0074::numeric 72.3450723426399126399054 .5353535353355353535353 still has 22 digits 72.3450723426399126399054 now has 24 digits For the most part, this seems correct, but I'm interested in knowing how you determine precision and scale for the result of a divide. Is there a well known algorithm? Thanks, --Will -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How does Numeric division determine precision?
Will Pugh writes: > It seems that is 9.1, numerics that don't have a specified precision > and scale are arbitrary scale/precision. > For many operations this is straightforward. However, when doing a > division operation that does not terminate, I'm curious about how the > number of digits is determined. According to select_div_scale() in src/backend/utils/adt/numeric.c, /* * The result scale of a division isn't specified in any SQL standard. For * PostgreSQL we select a result scale that will give at least * NUMERIC_MIN_SIG_DIGITS significant digits, so that numeric gives a * result no less accurate than float8; but use a scale not less than * either input's display scale. */ I wouldn't necessarily claim that that couldn't be improved on, but that's what it does now. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
