On 20.6.2012. 21:10, Tom Lane wrote: > rikard.pave...@zg.htnet.hr writes: >> create type t AS (i int); create type complex as (t t, i int); create table >> bad(i int, c complex); --This doesn't work as expected select * from bad >> where c is not null; > What do you consider to be "expected"? Have you read the documentation where > it points out that IS NULL and IS NOT NULL are not inverses for composite > values? > http://www.postgresql.org/docs/9.1/static/functions-comparison.html (I'm not > that thrilled with this behavior either, but it is per SQL standard AFAICT.) > regards, tom lane
I understand the concept behind if one composite property is null then then IS NULL check returns NULL (instead true or false). I can even understand IS NULL check returning false. I can use ::text to get what I expected, but Postgres still seems inconsistent in handling NULL checks. create type complex as (i int, j int); create table t (i int, c complex not null); --error as expected insert into t values(1, null); --unexpected - passed!? insert into t values(1, (null,4)); -- this is false - I think it would be better if it's null, but lets move on select (null, 4) is not null --lets try again with check constraint alter table t add check(c is not null); --error as expected from is not null check above insert into t values(1, (null,4)); It seems that check constraint behaves differently. Docs say: (http://www.postgresql.org/docs/9.1/static/ddl-constraints.html) "A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL)" And at least there is more notes required ;( Let's try some more. create table x (i int, c complex); insert into x values(1,null); insert into x values(2,(1,null)); insert into x values(3,(1,2)); --first row - ok select * from x where c is null; --last row - ok select * from x where c is not null; --unexpected result again select c is null from x; I must admit I was expecting true null false Regards, Rikard -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
