Question 300653 in Debian Question posted in
The oficial documentation can be found here.

Debian – 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.

experiments-2=# 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
(1 row)

experiments-2=# 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
(3 rows)

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

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    The problem is a.email_verified_at is a nullable field. Comparison results involving NULL return NULL, which I didn't know. The solution is to check for NULL before comparing to NOW():

    experiments-2=# SELECT a.email_verified_at <= NOW() AS "emailVerified", (a.email_verified_at IS NOT NULL AND a.email_verified_at <= NOW()) AS "emailVerified2" FROM profiles AS p INNER JOIN accounts AS a ON p.id = a.profile_id;
 emailVerified | emailVerified2 
---------------+----------------
               | f
 t             | t
               | f
(3 rows)

  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search