postgresql

Why is my application receiving null instead boolean?


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?


Solution

  • 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.