On Fri, 2005-07-15 at 13:46 +0300, Andrus wrote: > I have table > > CREATE TABLE test( col1 CHAR NOT NULL, col2 CHAR, > UNIQUE (col1, col2) ); > > This table allows to insert duplicate rows if col2 is NULL: > > INSERT INTO test VALUES ( '1', NULL ); > INSERT INTO test VALUES ( '1', NULL ); > > does NOT cause error! The two rows are not duplicated. NULL means "any value" in that context, so you can only say that the values for the first column are equal. Nothing can be said about the values in the second column. The database can't say they are the same, and can't say they are different either. http://en.wikipedia.org/wiki/Null_%28SQL%29 > How to create constraint so that NULL values are treated equal and second > insert is rejected ? I think you can do that with special operators (such as IS DISTINCT FROM) but you're using NULL as a normal value, that is not what it's meant to be in the first place, and I advice to be careful: http://www.postgresql.org/docs/8.0/interactive/functions-comparison.html please read the paragraphs on ... = NULL, IS NULL, and IS DISTINCT FROM. NULL is meant to represent the lack of knowledge ("unknown"). If you are ever treating NULL as a real value (i.e. comparing it to other values or or other NULLs), you must think twice about your design. IMVHO, the only place for IS NULL and IS DISTINCT FROM are meta-operations on data, administrative tasks and so on. There should be no need to use them in "normal" queries. Unless you're coding quick and dirty hacks when you really know what you're doing but don't care about the correctness of your design, of course. .TM. -- ____/ ____/ / / / / Marco Colombo ___/ ___ / / Technical Manager / / / ESI s.r.l. _____/ _____/ _/ Colombo@xxxxxx ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
