PostgreSQL NOTNULL vs IS NOT NULL vs NOT ISNULL

Today I’ve found out a flavor of PostgreSQL when checking if a composite type column is not null. There are different ways of checking for not null columns:

• NOTNULL
• IS NOT NULL
• NOT ISNULL

For primitive values any of the above works, while for the composite types there is a particularity.

Given a table of users with a deleted column of a composite type action, and some users marked as deleted, I need all users that are deleted.

create type action as (
	user_id int,
	reason text
)

create table users (
	id serial primary key,
	email varchar not null unique,
	deleted action null
)

insert into users (email) values
	('jd@domain.tld'),
	('john@domain.tld'),
	('doe@domain.tld')

update users set deleted = (1, 'reason to deleted') where id = 2
update users set deleted = (1, null) where id = 3

All the users are:

select id, email, deleted::text from users

id	email	deleted
1	jd@domain.tld	NULL
2	john@domain.tld	(1,”reason to deleted”)
3	doe@domain.tld	(1,)

The deleted users are 2 and 3, and I want to write a query which retrieves them.

NOTNULL retrieves only user 2

select id, email, deleted::text from users where deleted notnull

id	email	deleted
2	john@domain.tld	(1,”reason to deleted”)

 

IS NOT NULL also retrieves only user 2

select id, email, deleted::text from users where deleted is not null

id	email	deleted
2	john@domain.tld	(1,”reason to deleted”)

 

NOT ISNULL retrieves both deleted users, 2 and 3

select id, email, deleted::text from users where not deleted isnull

id	email	deleted
2	john@domain.tld	(1,”reason to deleted”)
3	doe@domain.tld	(1,)

 

While both users 2 and 3 are marked as deleted, user 3 has a null value for the reason field of composite type column deleted.

NOT ISNULL is the way to go when the need is to check if a composite column has a value, no matter if a field is null.