PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How can I find the values if the passed parameter can be null?

I have a query select id from tasks where name = $1. The value for $1 is passed from the application and it can be null. As far as I understand, with name = null, the query will not give the correct result. Is there any way to modify the query so that it takes both the filled value and null? Or I need to write two queries like

if (name == null) {
   select id from tasks where name is null
} else {
   select id from tasks where name = $1
}

Update

maybe I didn’t explain it correctly. I have a table

id  |  name
____________
1   |  task1
2   |  null
3   |  null
4   |  task4

The application executes the code

query("select id from tasks where name = $1", name)

The variable name can be null, or it can be equal to another value.

Now it turns out like this:

if name = null: query("select id from tasks where name = $1", name) 
in db:  select id from tasks where name = null. Expected id =2 and id = 3. Actual - 0 rows

if name = task1: query("select id from tasks where name = $1", name) 
in db:  select id from tasks where name = 'task1'. Expected id =1. Actual id =1

WHERE name = $1 OR name IS NULL does not seem to be correct, since if name = task1, I will get id =1, 2, 3, but I need only 1

Tags:

2

Answers


  1. 0

    You seem to be looking for the IS NOT DISTINCT FROM operator instead of the = operator:

    SELECT id FROM tasks WHERE name IS NOT DISTINCT FROM $1;

    It’s a bit verbose but does what you want.

    Login or Signup to reply.
  2. 0

    Bergi’s answer is correct, but if you need an index to support the query, there is an alternative trick you could use.

    Identify a string constant that can never occur as name (I randomly pick @@@). Then you can use

    WHERE coalesce(name, '@@@') = coalesce($1, '@@@')

    The index that can speed that up is

    CREATE INDEX ON tasks (coalesce(name, '@@@'));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search