PostgreSQL dynamic conditions in WHERE

Question

We have one table: categories

|---------------------|------------------|
|     category_id     |        name      |
|---------------------|------------------|
|          1          |     'text 1'     |
|---------------------|------------------|
|          2          |     'text 2'     |
|---------------------|------------------|

We need to do this:

Pseudo SQL code:

SELECT * FROM categories WHERE case when $1 != 0 then category_id = $1 end;

$1 - input parameter's value

Is It possible to do it in PostgreSQL with one SQL query?

It is possible, but usually this leads to inefficient (=slow) execution plans. — user330315
– user330315, Commented Jan 27, 2020 at 7:30
Or WHERE $1 !=0 AND $1 = category_id in which case the comment above (by Nick) applies. — clamp
– clamp, Commented Jan 27, 2020 at 8:32
That won't work as written, since the CASE clause will return a null value when $1 = 0, and that's not a boolean value. — The Impaler
– The Impaler, Commented Jan 27, 2020 at 11:41

Gordon Linoff · Accepted Answer · 2020-01-27 12:27:36Z

1

I am guessing that you want:

SELECT c.*
FROM categories c
WHERE $1 = 0 OR c.category_id = $1;

This returns all rows if the input parameter is 0 and just the matching row otherwise.

answered Jan 27, 2020 at 12:27

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Ali Mamedov Over a year ago

GordonLinoff, thx for the answer. @a_horse_with_no_name adviced me to avoid using such SQL queries because of performance issues. Programmatically generating SQLs are more preferable in case of highload apps.

Collectives™ on Stack Overflow

PostgreSQL dynamic conditions in WHERE

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related