How to manage NULL strings or dates in sql queries (PostgreSQL)

Question

PostgreSQL 11.1 With the below sql query, where $1 and $2 are strings and $3 is a timestamp, how can the below query be rewritten so that a null value in $3 allows for every date to be selected (not just null dates).

   SELECT lastname, firstname, birthdate FROM patients 
           WHERE UPPER(lastname) LIKE UPPER($1)||'%' and UPPER(firstname) LIKE UPPER($2)||'%' AND birthdate::date = $3::date
        UNION
        SELECT lastname, firstname, birthdate FROM appointment_book 
           WHERE UPPER(lastname) LIKE UPPER($1)||'%' and UPPER(firstname) LIKE UPPER($2)||'$' and birthdate::date = $3::date

That is, if $3 is null, then this should reduce to:

SELECT lastname, firstname, birthdate FROM patients 
               WHERE UPPER(lastname) LIKE UPPER($1)||'%' and UPPER(firstname) LIKE UPPER($2)||'%' 
            UNION
            SELECT lastname, firstname, birthdate FROM appointment_book 
               WHERE UPPER(lastname) LIKE UPPER($1)||'%' and UPPER(firstname) LIKE UPPER($2)||'$'

Abe Voelker · Accepted Answer · 2019-05-09 00:43:12Z

1

Untested but I think you can handle that with a CASE expression

SELECT lastname, firstname, birthdate FROM patients p
  WHERE UPPER(p.lastname) LIKE UPPER($1)||'%'
  AND   UPPER(p.firstname) LIKE UPPER($2)||'%'
  AND   (CASE WHEN $3 IS NULL THEN TRUE
         ELSE                      p.birthdate::date = $3::date
         END)
UNION
SELECT lastname, firstname, birthdate FROM appointment_book ab
  WHERE UPPER(ab.lastname) LIKE UPPER($1)||'%'
  AND   UPPER(ab.firstname) LIKE UPPER($2)||'%'
  AND   (CASE WHEN $3 IS NULL THEN TRUE
         ELSE                      ab.birthdate::date = $3::date
         END);

answered May 9, 2019 at 0:43

Abe Voelker

31.9k14 gold badges85 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to manage NULL strings or dates in sql queries (PostgreSQL)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related