Re: BUG #15256: Comparing if a rowtype is null

On Fri, Jun 29, 2018 at 2:06 PM, PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15256
> Logged by: Anderson Antunes
> Email address: anderson(dot)ant(dot)oli(at)gmail(dot)com
> PostgreSQL version: 9.3.5
>

​You should seriously consider upgrading in the near future.

> Operating system: Windows
> Description:
>
> Hello. My name is Anderson Antunes. I'm from Brazil. I'd like to get more
> information about the behavior of the query below using comparison
> operators. From what I understand in the query result below, a composite
> type variable is only null when all values are null. Similarly, a variable
> is not null when all values are not null. However, when we have written a
> null value and a non-null value, the "is_null" and "is not null" columns
> remain false. I believe that if we have at least a pre-set value, the
> operator should recognize that this variable is not null.
> I hope you have understood what I think.
>
> SELECT
> r,
> r IS NULL AS "is null",
> r IS NOT NULL AS "is not null"
> FROM (VALUES(NULL,NULL), (5, NULL), (5, 5)) r
>

​What you wrote is basically what is written in the documentation and by
definition documented behavior is not buggy.

It is documented that "IS NULL" and "IS NOT NULL" are not inverses of each
other when provided a composite types.

https://www.postgresql.org/docs/10/static/functions-comparison.html
​
This is not going to change.

Write:

NOT (composite_type IS NULL) if you need the inverse.

David J.
​