Re: BUG #14296: weird check constraint clause in pg_constraint - Mailing list pgsql-bugs

>>>>> "ralf" == ralf  <ralf@rw7.de> writes:

 ralf> I created two tables

 ralf> CREATE Table tabgood ( col integer, CONSTRAINT checker CHECK ( col>0 ))
 ralf> CREATE Table tabbad  ( col integer, CONSTRAINT checker CHECK ( col>-1 ))

 ralf> and fetch the check constraints from pg_constraint

 ralf> SELECT ut.relname,uc.conname,uc.consrc

As a rule you should never look at consrc, adsrc, etc because they
become stale the instant anything gets renamed. The right way to get the
constraint definition is to use pg_get_constraintdef(oid), or
pg_get_expr(conbin,conrelid) for just the check expression.

 ralf> "tabgood";"checker";"(col > 0)"
 ralf> "tabbad";"checker";"(col > '-1'::integer)"

 ralf> The second clause is weird, although working. it should be just
 ralf> "col > -1".

 ralf> This problem is observed seen on PostgresSQL 9.5.4, it was not
 ralf> observed on 9.3.14.

This is intentional.

Quoth the comments in ruleutils.c:

            /*
             * INT4 can be printed without any decoration, unless it is
             * negative; in that case print it as '-nnn'::integer to ensure
             * that the output will re-parse as a constant, not as a constant
             * plus operator.  In most cases we could get away with printing
             * (-nnn) instead, because of the way that gram.y handles negative
             * literals; but that doesn't work for INT_MIN, and it doesn't
             * seem that much prettier anyway.
             */

And the accompanying commit message: (from 542320c2b)

    Be more careful about printing constants in ruleutils.c.

    The previous coding in get_const_expr() tried to avoid quoting integer,
    float, and numeric literals if at all possible.  While that looks nice,
    it means that dumped expressions might re-parse to something that's
    semantically equivalent but not the exact same parsetree; for example
    a FLOAT8 constant would re-parse as a NUMERIC constant with a cast to
    FLOAT8.  Though the result would be the same after constant-folding,
    this is problematic in certain contexts.  In particular, Jeff Davis
    pointed out that this could cause unexpected failures in ALTER INHERIT
    operations because of child tables having not-exactly-equivalent CHECK
    expressions.  Therefore, favor correctness over legibility and dump
    such constants in quotes except in the limited cases where they'll
    be interpreted as the same type even without any casting.

    This results in assorted small changes in the regression test outputs,
    and will affect display of user-defined views and rules similarly.
    The odds of that causing problems in the field seem non-negligible;
    given the lack of previous complaints, it seems best not to change
    this in the back branches.

So the objective is not to preserve the original input, but to return
text that will be parsed to produce the same expression tree that was
stored.

--
Andrew (irc:RhodiumToad)