How can I show all rows that are null or not null?

Question

I have a users ill table.

id | user_id | health_at (null or timestmap)

I have a preapred statement variable $1. it can be NULL or NOT NULL.

if is NULL I want to show all rows where HEALTH_AT IS NULL. if he select NOT NULL I want to show all rows where health_at IS NOT NULL.

How I do it with one Row ?

if(filter_data === 'is_back') {
  let variable = 'NOT NULL'
} else {
  let variable = 'NULL';
}

          query = await pg.query(`
            SELECT 

            ui.id, ui.health_at, ui.created_at

            FROM users_ill ui

            INNER JOIN users u
            ON u.id = c.user_id

            WHERE ui.health_at = $1
            
            LIMIT 50`,
        [variable]

if is NOT NULL I got error. so how can I do it on my where clause ?

Answer 1

You can use a CASE in the WHERE condition, and compare the string input to see what to do.

SELECT  ui.id
     ,  ui.health_at
     ,  ui.created_at
FROM users_ill ui
    INNER JOIN users u ON u.id = ui.user_id
WHERE
    CASE WHEN $1 = 'NULL' THEN ui.health_at IS NULL
        ELSE ui.health_at IS NOT NULL
    END
ORDER BY ui.id -- always use ORDER BY when using LIMIT
LIMIT 50;

Your code also used the wrong alias c, which was not defined. Don't forget to use ORDER BY when using a LIMIT to avoid surprises in the results.