null values in non-nullable column
От | George Pavlov |
---|---|
Тема | null values in non-nullable column |
Дата | |
Msg-id | 8C5B026B51B6854CBE88121DBF097A8661E3FC@ehost010-33.exch010.intermedia.net обсуждение исходный текст |
Ответы |
Re: null values in non-nullable column
Re: null values in non-nullable column |
Список | pgsql-sql |
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
В списке pgsql-sql по дате отправления: