On 21.6.2012. 6:03, Pavel Stehule wrote: > 2012/6/21 Rikard Pavelic <rikard.pave...@zg.htnet.hr>: >> 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 > but C is not one value - it is composite - and composite in SQL is not > pointer like C or C++, but it is list of values - and composite is > null (list is null) when all fields are null. > > Regards > > Pavel >
Yeah, I said I'm fine with this behavior. The only inconsistent thing is check constraint, which behaves as NOT column IS NULL instead of column IS NOT NULL as docs says. I even prefer that behavior. Thanks, Rikard -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs