null values in non-nullable column

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

Responses

Browse pgsql-sql by date

  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