Re: cataloguing NOT NULL constraints

Alvaro Herrera <alvherre@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.GA27098@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