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 <pete...@gmx.net> wrote: > On ons, 2012-01-18 at 18:17 -0500, Robert Haas wrote: > > I agree with Peter that we should have we should have CHECK ONLY. > > ONLY is really a property of the constraint, not the ALTER TABLE > > command -- if it were otherwise, we wouldn't need to store it the > > system catalogs, but of course we do. The fact that it's not a > > standard property isn't a reason not to have proper syntax for it. > > Clearly, we will eventually want to support inherited and non-inherited > constraints of all types. Currently, each type of constraint has an > implicit default regarding this property: > > check - inherited > not null - inherited > foreign key - not inherited > primary key - not inherited > unique - not inherited > exclusion - not inherited > > As discussed above, we need to have a syntax that is attached to the > constraint, not the table operation that creates the constraint, so that > we can also create these in CREATE TABLE. > > How should we resolve these different defaults? > > Also, in ALTER TABLE, if you want to add either an inherited or not > inherited constraint to a parent table, you should really say ALTER > TABLE ONLY in either case. Because it's conceivably valid that ALTER > TABLE foo ADD CHECK () NOINHERIT would add an independent, not inherited > check constraint to each child table. > > So, there are all kinds of inconsistencies and backward compatibility > problems lurking here. We might need either a grand transition plan or > document the heck out of these inconsistencies. > > >
check_constraint_create_table_support.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers