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.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.