Why is my application receiving null instead boolean?

Question

I'm building a web app, and I noticed that one of my queries is returning a null where I expect a boolean. However the database is indicating it's actually returning a boolean.

I can see in the documentation that Postgres considers NULL to be an unknown boolean value. I'm confused why the <= operator would return t/NULL instead of t/f though.

select version();

version
PostgreSQL 13.13 (Debian 13.13-0+deb11u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

SELECT a.email_verified_at <= NOW() AS "emailVerified",
 pg_typeof(a.email_verified_at <= NOW()) AS "emailVerifiedType", 
 pg_typeof((a.email_verified_at <= NOW())::boolean) AS "emailVerifiedType2" 
FROM profiles AS p 
INNER JOIN accounts AS a ON p.id = a.profile_id;

emailVerified	emailVerifiedType	emailVerifiedType2
	boolean	boolean
t	boolean	boolean
	boolean	boolean

How can I tell Postgres to return an explicit t/f to my application?

Answer 1

SQL has a three-valued logic: a boolean expression can be TRUE, FALSE or NULL (unknown). If you compare NULL (unknown) with any value, the result is NULL.

There are several ways to rewrite your comparison so that it returns FALSE if email_verified_at is a NULL value:

1. use coalesce():

   coalesce(email_verified_at, 'infinity') <= current_timestamp
   

2. use an explicit NULL check

   email_verified_at IS NOT NULL AND email_verified_at <= current_timestamp
   

Another option would be to forbid NULL values in email_verified_at and store infinity if the e-mail address was never verified. This solution would simplify the comparison, but it may be undesirable for other reasons.