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

On Sat, Oct 18, 2014 at 4:42 AM, <finkel(at)sd-il(dot)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