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?
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:
About the array constructor:
use the IN clause
Select name from table1 where id in (1,4,7)
An alternative: unnest (normalize) the array, join with the table and then aggregate:
select array_agg(t."name")
from the_table t
join unnest('{1,4,7}'::integer[]) aid on t.id = aid;
or using array constructor
select array
(
select t."name"
from the_table t
join unnest('{1,4,7}'::integer[]) aid on t.id = aid
);