Re: pg_get_constraintdef() doesn't always give an equal constraint

On Mon, 23 Mar 2015, Tom Lane wrote:

> Jeff Davis <pgsql(at)j-davis(dot)com> writes:
>> 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)
>
> A larger issue is that I have a nasty feeling that this isn't the
> only place where ruleutils.c output might be read in a way that's
> functionally equivalent to the original, but not the exact same
> parsetree.

Jeff pointed this out to me and as we discussed it, it seems like a good
time to add a regression test to make us aware of the current parse +
deparse behavior, and call attention to any changes in that behavior
later.

Attached is a Perl script that generates many combinations of CHECK
constraints like Jeff's, but with various types, and the result of running
it in the regression test suite.

Would something like this be welcome in the regression test suite?

Jon

--
Jon Jensen
End Point Corporation
https://www.endpoint.com/