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 > > 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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs