From: | "George Pavlov" <gpavlov(at)mynewplace(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | null values in non-nullable column |
Date: | 2006-12-05 00:33:19 |
Message-ID: | 8C5B026B51B6854CBE88121DBF097A8661E3FC@ehost010-33.exch010.intermedia.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-12-05 02:52:46 | Re: null values in non-nullable column |
Previous Message | Ezequias Rodrigues da Rocha | 2006-12-04 17:08:24 | Re: [SQL] Grants |