CODEWITHSUNDEEP
postgresql
Winfield Trail
Winfield Trail

Reputation: 5695

Select from any of multiple values from a Postgres field

I've got a table that resembles the following:

WORD    WEIGHT   WORDTYPE
a       0.3      common
the     0.3      common
gray    1.2      colors
steeple 2        object

I need to pull the weights for several different words out of the database at once. I could do:

SELECT * FROM word_weight WHERE WORD = 'a' OR WORD = 'steeple' OR WORD='the';

but it feels ugly and the code to generate the query is obnoxious. I'm hoping that there's a way I can do something like (pseudocode):

SELECT * FROM word_weight WHERE WORD = 'a','the';

Upvotes: 32

Views: 75250

Answers (3)

shubham mishra
shubham mishra

Reputation: 1234

If you are not sure about the value and even not sure whether the field will be an empty string or even null then,

.where("column_1 ILIKE ANY(ARRAY['','%abc%','%xyz%']) OR column_1 IS NULL")

Above query will cover all possibility.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425371

If you want to pass the whole list in a single parameter, use array datatype:

SELECT  *
FROM    word_weight
WHERE   word = ANY('{a,steeple,the}'); -- or ANY('{a,steeple,the}'::TEXT[]) to make explicit array conversion

Upvotes: 16

Mark Wenzel
Mark Wenzel

Reputation: 766

You are describing the functionality of the in clause.

select * from word_weight where word in ('a', 'steeple', 'the');

Upvotes: 65

Related Questions