SELECT WHERE column values are in array

Question

Suppose I have a table called 'myTable':

columnA|    columnB|    columnC
   1   |     Yellow|     Apple
   3   |     Red   |     Grape
   8   |     Blue  |     Banana
   6   |     Green |     Orange

The above table is a simplification of an actual table for demo purposes. Imagine actual table is 100K + rows. Now, I want to select only the rows where columnB is in a list/array: ex - ['Red', 'Blue', 'Green']. I am not sure the right syntax to use here.

 SELECT * FROM myTable WHERE columnB IN Array['Red', 'Blue', 'Green']

Whats the proper syntax to achieve this?

`SELECT * FROM myTable WHERE columnB IN ('Red', 'Blue', 'Green')` see the [manual](https://www.postgresql.org/docs/14/queries-table-expressions.html#QUERIES-WHERE) — Edouard, Dec 15 '21 at 18:10

sairfan · Accepted Answer · 2023-08-09T16:53:14.423

Here is the example, I believe its for MS SQL

SELECT p.FirstName, p.LastName, e.JobTitle  
FROM Person.Person AS p  
    JOIN HumanResources.Employee AS e  
    ON p.BusinessEntityID = e.BusinessEntityID  
WHERE e.JobTitle IN ('Design Engineer', 'Tool Designer', 'Marketing Assistant');

Or in simple words (pay attention to text if its in lower, upper or proper base).

SELECT * FROM design WHERE Color IN ('Red', 'Green', 'Blue');

score 2 · Answer 2 · answered Dec 15 '21 at 18:13

In Postgres, you can use an array, too, with = ANY ():

SELECT * FROM myTable WHERE columnB = ANY (ARRAY['Red', 'Blue', 'Green']);

Or with a literal array constant as input:

SELECT * FROM myTable WHERE columnB = ANY ('{Red, Blue, Green}'::text[]);

It's equivalent to:

SELECT * FROM myTable WHERE columnB IN ('Red', 'Blue', 'Green');

IN vs ANY operator in PostgreSQL

SELECT WHERE column values are in array

2 Answers2