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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
