[GENERAL] unexpected check constraint violation
Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d>=0.00603)); insert into t values (0.00603); ERROR: new row for relation "t" violates check constraint "t_d_check" thanks Jacek -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
Jacek Becla wrote: create table t(d real, check(d>=0.00603)); insert into t values (0.00603); ERROR: new row for relation "t" violates check constraint "t_d_check" Because equality is not well-defined for "real" values? - Jeremy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
On Mar 23, 2009, at 1:41 PM, Jeremy Harris wrote: Because equality is not well-defined for "real" values? That was my first thought, too, but why would two identical real literals evaluate to different bit patterns? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote: Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d>=0.00603)); insert into t values (0.00603); ERROR: new row for relation "t" violates check constraint "t_d_check" thanks Jacek try this: insert into t values (0.00603::real); Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
On Mon, Mar 23, 2009 at 1:54 PM, Jacek Becla wrote: > Hi, > > Can someone explain why postgres complains in this case: > > create table t(d real, check(d>=0.00603)); > insert into t values (0.00603); > > ERROR: new row for relation "t" violates check constraint "t_d_check" Without any casting, 0.00603 likely evaluates to a numeric. select 0.00603::numeric > 0.00603::real; ?column? -- t So, this works: create table t(d real, check(d>=0.00603::real)); insert into t values (0.00603); INSERT 0 1 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
Thanks Ries. Do you know if that is a postgres feature or a bug? In practice, I wanted to load the data from a file using COPY FROM. Modifying a large csv file in impractical and not very elegant. thanks, Jacek ries van Twisk wrote: On Mar 23, 2009, at 2:54 PM, Jacek Becla wrote: Hi, Can someone explain why postgres complains in this case: create table t(d real, check(d>=0.00603)); insert into t values (0.00603); ERROR: new row for relation "t" violates check constraint "t_d_check" thanks Jacek try this: insert into t values (0.00603::real); Ries -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
On Mon, Mar 23, 2009 at 2:52 PM, Jacek Becla wrote: > Thanks Ries. Do you know if that is a postgres feature or a bug? It's not a bug, it's lack of precision in the definition on your part being interpreted by pgsql. When you create the table, you get this: create table t(d real, check(d>=0.00603)); \d t Table "public.t" Column | Type | Modifiers +--+--- d | real | Check constraints: "t_d_check" CHECK (d >= 0.00603::double precision) Note that having not been told the type for the check constraint, pgsql defaults to double precision. So, in effect, your table creation was this: create table t(d real, check(d>=0.00603::double precision)); You can either cast the check constraint, or change the field type to match double precision. create table t(d double precision, check(d>=0.00603::double precision)); create table t(d real, check(d>=0.00603::real)); Either of those will work properly. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unexpected check constraint violation
Scott Marlowe writes: > You can either cast the check constraint, or change the field type to > match double precision. The short answer here is that 0.00603::double precision and 0.00603::real are unlikely to be exactly the same value, and which one is greater is a matter of which direction the real got rounded off in. On my machine the former is a bit larger: regression=# select 0.00603::double precision - 0.00603::real; ?column? -- 1.85072421797494e-10 (1 row) but on another platform it could be the other way around. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
