Re: how to create a non-inherited CHECK constraint in CREATE TABLE - Mailing list pgsql-hackers

Hi,

So, I have a patch for this. This patch introduces support for

CHECK ONLY syntax while doing a CREATE TABLE as well as during the usual ALTER TABLE command.

Example:

create table atacc7 (test int, test2 int CHECK ONLY (test>0), CHECK (test2>10));
create table atacc8 () inherits (atacc7);

postgres=# \d+ atacc7
                Table "public.atacc7"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 test   | integer |           | plain   |
 test2  | integer |           | plain   |
Check constraints:
    "atacc7_test2_check" CHECK (test2 > 10)
    "atacc7_test_check" CHECK ONLY (test > 0)
Child tables: atacc8
Has OIDs: no

postgres=# \d+ atacc8
                Table "public.atacc8"
 Column |  Type   | Modifiers | Storage | Description
--------+---------+-----------+---------+-------------
 test   | integer |           | plain   |
 test2  | integer |           | plain   |
Check constraints:
    "atacc7_test2_check" CHECK (test2 > 10)
Inherits: atacc7
Has OIDs: no


This patch removes the support for :

ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);

and uses

ALTER TABLE constraint_rename_test ADD CONSTRAINT con2 CHECK ONLY (b > 0);

Is this what we want? Or we would want the earlier support in place for backward compatibility as well? We are actually introducing this in 9.2 so I guess we can remove this.

This is a much cleaner implementation and we might not even need the changes in pg_dump now because the pg_get_constraintdef can provide the info about the ONLY part too. So some cleanup can be done if needed.

I know it's a bit late in the commitfest, but if this patch makes this feature more "complete", maybe we should consider...

Thoughts?

P.S Here's the discussion thread in its entirety for reference:
http://postgresql.1045698.n5.nabble.com/how-to-create-a-non-inherited-CHECK-constraint-in-CREATE-TABLE-td5152184.html

Regards,
Nikhils

On Thu, Feb 2, 2012 at 1:32 AM, Peter Eisentraut
<peter_e@gmx.net> wrote: