Thread: BUG #15256: Comparing if a rowtype is null
The following bug has been logged on the website: Bug reference: 15256 Logged by: Anderson Antunes Email address: anderson.ant.oli@gmail.com PostgreSQL version: 9.3.5 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
The following bug has been logged on the website:
Bug reference: 15256
Logged by: Anderson Antunes
Email address: anderson.ant.oli@gmail.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.
This is not going to change.
Write:
NOT (composite_type IS NULL) if you need the inverse.
David J.