PostgreSQL NULL check after adding attribute on UDT

Question

I have a problem that in Postgres 14 a NULL check on my UDT does no longer work after I modify the type.

My composite UDT that has two attributes.

create type A as (
  foo text
);

create type B as (
  bar text
);

create type AorB as (
  a a,
  b b
);

create table elements (
    attr AorB
)

I will only ever set one of the two attributes and want to access them based on which one it is.

insert into elements (attr)
values 
(
  (
    ROW('aa')::A,
    NULL
  )::AorB
),
(
  (
    NULL,
    ROW('bb')::B
  )::AorB
);

select * from elements where (attr).a IS NOT NULL;

The query gives me the correct answer

attr
("(aa)",)

However, if I modify a type like this

alter type A add attribute bla text;

and repeat the same query I get an empty result. I do, however, see all the data if I drop the where clause. Here is a playground with my example: https://www.db-fiddle.com/f/4jyoMCicNSZpjMt4jFYoz5/10961

What could be the issue and how can I resolve it?

user7070613 · Accepted Answer · 2023-11-17 21:02:17Z

1

select row('test', null) is not null; f

Per Comparison operators:

If the expression is row-valued, then IS NULL is true when the row expression itself is null or when all the row's fields are null, while IS NOT NULL is true when the row expression itself is non-null and all the row's fields are non-null. Because of this behavior, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions; in particular, a row-valued expression that contains both null and non-null fields will return false for both tests.

alter type A add attribute bla text; adds the attribute with a DEFAULT of NULL. As the above shows that makes is not null return false.

answered Nov 17, 2023 at 21:02

user7070613

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL NULL check after adding attribute on UDT

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related