2

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
IrinaB
  • 31
  • 1

3 Answers3

1

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0

use the IN clause

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

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

Brian K. Burge
  • 114
  • 1
  • 10
0

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
);
Stefanov.sm
  • 11,215
  • 2
  • 21
  • 21