BUG #11705: \d(escribe) table shows incorrect check constraint

On Sat, Oct 18, 2014 at 4:42 AM, <finkel@sd-il.com> wrote:

We have a table with this constraint:

CONSTRAINT check_m_nullability CHECK (((m1 IS NULL) AND (m2 IS NULL)) OR
((m1 IS NOT NULL) AND (m2 IS NOT NULL)))

When we \d <table name> it is listed as:

Check constraints:
"check_m_nullability" CHECK (m1 IS NULL AND m2 IS NULL OR m1 IS NOT

NULL

AND m2 IS NOT NULL)"

The application of the constraint appears to be correct. The problem is
that \d is dropping important parentheses in its output; so it is showing

an

incorrect description.

The description is correct AFAIK, this can survive without parenthesis as
AND clauses take precedence on OR.

Note that psql uses pg_constraint_def to generate this description, with
pretty_bool set to true (2nd parameter of this function set to false by
default) to make the output more lisible, so you could always reuse it for
a custom query like that:
=# create table aa (a int, b int, check (a is null and b is null or a is
not null and b is not null));
CREATE TABLE
=# select pg_get_constraintdef(oid) from pg_constraint where conrelid =
'ab'::regclass;
pg_get_constraintdef
----------------------------------------------------------------------------------
CHECK ((((a IS NULL) AND (b IS NULL)) OR ((a IS NOT NULL) AND (b IS NOT
NULL))))
(1 row)
=# select pg_get_constraintdef(oid, true) from pg_constraint where conrelid
= 'ab'::regclass;
pg_get_constraintdef
--------------------------------------------------------------------
CHECK (a IS NULL AND b IS NULL OR a IS NOT NULL AND b IS NOT NULL)
(1 row)
--
Michael

"finkel@sd-il.com" <finkel@sd-il.com> wrote:

We have a table with this constraint:

CONSTRAINT check_m_nullability CHECK (((m1 IS NULL) AND
(m2 IS NULL)) OR ((m1 IS NOT NULL) AND (m2 IS NOT NULL)))

Michael is right, but in addition to that you might want to use the
simpler (and to my eye easier to read):

CONSTRAINT check_m_nullability CHECK ((m1 IS NULL) = (m2 IS NULL))

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs