Alvaro Herrera <alvhe...@commandprompt.com> wrote: > Just over a year ago, I posted a patch (based on a previous patch > by Bernd Helmle) that attempted to add pg_constraint rows for NOT > NULL > constraints. > http://archives.postgresql.org/message-id/20110707213401.ga27...@alvh.no-ip.org > That patch was rather long and complex, as it tried to handle all > the hairy issues directly with a completely new 'contype' value > for NOT NULL constraints; so the code had to deal with inheritance > of constraints, pg_dump issues, and a lot of nitty-gritty. In the > end it was killed by a simple realization of Peter Eisentraut's: > "Why not just transform these into the equivalent CHECK > constraints instead?" That ended up being discussing at length, > and this patch, much smaller than the previous one, is an attempt > to do things that way. > > This patch is not final yet, because there are some issues still > open; but the interesting stuff already works. Simply declaring a > column as NOT NULL creates a CHECK pg_constraint row; similarly, > declaring a CHECK (foo IS NOT NULL) constraint sets the > pg_attribute.attnotnull flag. If you create a child table, the > NOT NULL constraint will be inherited. Don't forget the peculiarities of columns with record types. Semantically, these three things are different: colname rectype not null colname rectype check (colname is not null) colname rectype check (not (colname is null)) test=# create table t (id int primary key, v1 a not null, v2 a check (v2 is not null), v3 a check (not (v3 is null))); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_pkey" for table "t" CREATE TABLE test=# insert into t values (1, (1,1), (1,1), (1,1)); INSERT 0 1 test=# insert into t values (2, (1, null), (1, null), (1,1)); ERROR: new row for relation "t" violates check constraint "t_v2_check" DETAIL: Failing row contains (2, (1,), (1,), (1,1)). test=# insert into t values (3, (1, null), (1,1), (1, null)); INSERT 0 1 test=# insert into t values (4, (null, null), (1,1), (1, null)); INSERT 0 1 test=# insert into t values (5, (null, null), (1,1), (null, null)); ERROR: new row for relation "t" violates check constraint "t_v3_check" DETAIL: Failing row contains (5, (,), (1,1), (,)). -Kevin
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
