Postgresl : Loop a list of values to create SELECT with mulitple WHERE clause

Question

I have a simple SQL query such as

SELECT * from tableName
WHERE (field = 'value1') OR (field = 'value2') OR (field = 'value3') ...

I am lazy enough to write hardcode write the SQL query like above. I want to extract value1, value2 , ... in an array ( or any collections)

How to write the query above so that it can loop the list of values ?

# declare array

# loop for each element in the array
SELECT * from tableName WHERE (field = element) 

# or even better
# build the WHERE clause with a loop
whereClause = (field = 'value1') OR (field = 'value2') OR (field = 'value3') ...

SELECT * from tableName WHERE whereClause

where field = any(array)?

user330315
– user330315

2021-11-15 14:12:58 +00:00
Commented Nov 15, 2021 at 14:12 — user330315
– user330315, Commented Nov 15, 2021 at 14:12
@a_horse_with_no_name You are a genius

Raymond Chenon
– Raymond Chenon

2021-11-15 14:40:26 +00:00
Commented Nov 15, 2021 at 14:40 — Raymond Chenon
– Raymond Chenon, Commented Nov 15, 2021 at 14:40

borisdonchev · Accepted Answer · 2021-11-15 14:36:15Z

1

As a_horse_with_no_name has mentioned you can

Check if the field is in an array

SELECT *
FROM   tableName 
WHERE  field = ANY(ARRAY['value1', 'value2'])

and you can use a language of your choice to fill these values dynamically

or if those values are already in a table you can

SELECT * 
FROM   tableName
WHERE  field IN (SELECT field FROM otherTableName)

answered Nov 15, 2021 at 14:36

borisdonchev

1,2542 gold badges11 silver badges24 bronze badges

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

Collectives™ on Stack Overflow

Postgresl : Loop a list of values to create SELECT with mulitple WHERE clause

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related