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 ?

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 ?

• node.js
• postgresql

• Use IS NULL or IS NOT NULL, but no equation =. See also postgresql.org/docs/current/functions-comparison.html – Frank Heikens Commented Feb 6 at 15:40
• @FrankHeikens I know but I need is null or is not null in one query how can I do it ? WHERE health_at IS $1 , then I get error when the value is "NOT NULL" – Metin SevTHP Commented Feb 6 at 15:41

2 Answers 2

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.

IS NULL and IS NOT NULL are keywords of the SQL language, you cannot inject them via a statement variable.

But you can set the statement variable to the value of filter_data and use the following where clause:

WHERE ('is_back' =  $1 AND ui.health_at IS NOT NULL)
   OR ('is_back' <> $1 AND ui.health_at IS NULL)

There is another problem with the code that you posted: The variable is undefined because each of the two let variable = ... statements is confined to a { ... } block:

if (true) {
  let variable = 'NOT NULL';
} else {
  let variable = 'NULL';
}
console.log(variable);