The following bug has been logged online:

Bug reference: 1436
Logged by: Florin Borsa
Email address: fborsa (AT) saratoga (DOT) ro
PostgreSQL version: 8.0
Operating system: Windows XP
Description: not null condition is not respected
Details:

I ceate a table with some integer fields in this way:

create table homes ( nr int not null, name char(4))

I can insert in this table zero and null values for nr field.

where is the error, because I do not want to permit insertion of zero or
null values for nr field in homes table ?

Thank you !

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly

On Sun, Jan 23, 2005 at 05:39:05PM +0000, Florin Borsa wrote:

Zero isn't null, so a NOT NULL constraint wouldn't prohibit it as
a value. If you don't want to allow zero then use a CHECK constraint
(or a domain with a CHECK constraint):

CREATE TABLE homes (
nr integer NOT NULL CHECK (nr <> 0),
name char(4)
);

INSERT INTO homes (name) VALUES ('test');
ERROR: null value in column "nr" violates not-null constraint

INSERT INTO homes (nr, name) VALUES (NULL, 'test');
ERROR: null value in column "nr" violates not-null constraint

INSERT INTO homes (nr, name) VALUES (0, 'test');
ERROR: new row for relation "homes" violates check constraint "homes_nr_check"

INSERT INTO homes (nr, name) VALUES (1, 'test');
INSERT 0 1

Can you provide a test case demonstrating the insertion of NULL
into a column with a NOT NULL constraint?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match