Re: MATCH FULL flawed?

Alvaro Herrera <alvherre(at)atentus(dot)com> writes:
> I'm trying MATCH FULL and it looks like it doesn't work (does anyone use
> it anyway?).

You're not using it right. You specified a separate MATCH FULL
constraint for each column:

> encuentro=> create table test2 (a integer references test (a) match full, b integer references test (b) match full);

This is essentially a no-op, since MATCH FULL and MATCH PARTIAL are only
meaningfully different for a multi-column key. What you want is

regression=# create table test2 (a integer, b integer,
regression(# foreign key (a,b) references test(a,b) match full);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)

which has the behavior you're after:

regression=# insert into test2 values (null, 1);
ERROR: <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
regression=# insert into test2 values (1, null);
ERROR: <unnamed> referential integrity violation - MATCH FULL doesn't allow mixing of NULL and NON-NULL key values
regression=# insert into test2 values (null, null);
INSERT 262789 1
regression=# insert into test2 values (1, 1);
INSERT 262790 1
regression=#

regards, tom lane