postgres: select rows where a parameter is anything

Question

i am passing parameters in a query in the WHERE clause. I would like to create a case where any param in the where is fine (like obmitting the value).

for example:

SELECT * FROM USERS WHERE lang=%s

i can pass in the placeholder strings like 'en', 'it', 'es'. but what about if i want to take everything in the lang column? like if i didn't mention the lang in the where clause?

is there something like:

SELECT * FROM USERS WHERE lang=ANYTHING

??

Something similar to: WHERE lang=%s OR %s IS NULL

jarlh
– jarlh

2018-08-21 12:58:50 +00:00
Commented Aug 21, 2018 at 12:58 — jarlh
– jarlh, Commented Aug 21, 2018 at 12:58

sticky bit · Accepted Answer · 2018-08-21 13:01:19Z

2

You can use NULL as an indicator you don't care about the language. Then you can use IS NULL to check if the value is null and therefore include all rows. Use OR to append the equality check. Like that, if the value isn't null, it must match the language for a row to be returned.

SELECT *
       FROM users
       WHERE %s IS NULL
              OR lang = %s;

answered Aug 21, 2018 at 13:01

sticky bit

37.7k12 gold badges34 silver badges46 bronze badges

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

2 Comments

91DarioDev Over a year ago

thank you. and i pass always the same value to both those palceholders right?

sticky bit Over a year ago

@91DarioDev: Yes.

Collectives™ on Stack Overflow

postgres: select rows where a parameter is anything

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related