In 8.1 I have a situation where nullability of user defined datatypes
does not seem to be enforced. Using the following steps I end up with a
table that has a column that should not be nullable, but has nulls in
it. Here's a pared down outline of the steps:
-- create a datatype that should enforce not null values
gp_test=# create domain boolean_not_null as boolean not null;
-- a table with a field using the datatype
gp_test=# create table a (id integer, test boolean_not_null);
gp_test=# insert into a values (1, true);
gp_test=# select * from a;id | test
----+------ 1 | t
-- as expected no nulls are allowed
gp_test=# insert into a values (100, null);
ERROR: domain boolean_not_null does not allow null values
-- a second table (a parent)
gp_test=# create table b (id integer);
gp_test=# insert into b values (1);
gp_test=# insert into b values (2);
gp_test=# select * from b;id
---- 1 2
-- now create a table based on a left join
-- this creates a table with a not-nullable column (datatype
-- inherited from the original table) which contains nulls;
-- even though insertion of new nulls is not allowed
gp_test=# create table m as select id, test from b left join a using
(id);
gp_test=# \d m; Table "public.m"Column | Type | Modifiers
--------+------------------+-----------id | integer | test | boolean_not_null |
gp_test=# select * from m;id | test
----+-------- 1 | t 2 | <NULL>
gp_test=# insert into m values (100, null);
ERROR: domain boolean_not_null does not allow null values
gp_test=# insert into m (id) values (100);
ERROR: domain boolean_not_null does not allow null values
gp_test=# update m set test = test; -- note no error here!
gp_test=# update m set test = (test and true);
ERROR: domain boolean_not_null does not allow null values
I would have expected failure at the table creation step, but it
proceeds (and inserts the nulls). Interestingly, I do see a failure
after I try to restore the table from a dump (using pg_dump/pg_restore).
George