We have a table with names and their IDs like:

 1 | John
 2 | Mary
 3 | Ann
 ...

An array of IDs is given. For example, {1,4,7}. I need to get an array of names, which corresponds to these IDs.

How should I formulate the WHERE condition?
And how to return an array?

CodePudding user response：

use the IN clause

Select name from table1 where id in (1,4,7)
 

https://www.w3schools.com/sql/sql_in.asp

CodePudding user response：

To pass an array and receive an array:

SELECT ARRAY (SELECT name FROM tbl WHERE id = ANY ('{1,4,7}'));

I am passing an array literal (a constant). Works without cast while the element type of the array can be coerced to the type derived from the compared column type. Else, add an explicit type cast to the array:

SELECT ARRAY (SELECT name FROM tbl
              WHERE id = ANY ('{2021-01-03, 2021-01-04}'::date[]))

About IN vs. = ANY:

• How to use ANY instead of IN in a WHERE clause with Rails?
• IN vs ANY operator in PostgreSQL

About the array constructor:

• Why is array_agg() slower than the non-aggregate ARRAY() constructor?