pg_get_constraintdef() doesn't always give an equal constraint

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_get_constraintdef() doesn't always give an equal constraint
Date: 2015-03-23 20:05:47
Message-ID: CAMp0ubcxvbnPevHz7LkTkRVDGvCgs+JTHCUn1pYMT-CK9cdJPg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

create table pt(a int, b int, c float8 check (c < '10.1'));
create table ct(a int, b int, c float8);
select pg_get_constraintdef(oid) from pg_constraint where conname='pt_c_check';
pg_get_constraintdef
--------------------------------------
CHECK ((c < 10.1::double precision))
(1 row)

-- copy constraint to "ct" using syntax given above
alter table ct add constraint pt_c_check CHECK ((c < 10.1::double precision));
select pg_get_constraintdef(oid) from pg_constraint where conname='pt_c_check';
pg_get_constraintdef
----------------------------------------
CHECK ((c < 10.1::double precision))
CHECK ((c < (10.1)::double precision))
(2 rows)

-- notice extra parenthesis above

-- now, we can't attach "ct" as an inheritance child of "pt"
alter table ct inherit pt;
ERROR: child table "ct" has different definition for check constraint
"pt_c_check"

Also, the pg_dump output is different for pt and ct.

Strangely, the "\d" output is the same, so I tried using
pg_get_constraintdef with pretty-printing mode, which works fine. But
that's contrary to the docs, which say:

"The pretty-printed format is more readable, but the default format is
more likely to be interpreted the same way by future versions of
PostgreSQL; avoid using pretty-printed output for dump purposes."

Regards,
Jeff Davis

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-03-23 23:32:33 Re: pg_get_constraintdef() doesn't always give an equal constraint
Previous Message Tom Lane 2015-03-23 14:48:03 Re: BUG #12885: The result of casting a double to an integer depends on the database version