CODEWITHSUNDEEP
sqlarrayspostgresql
IrinaB
IrinaB

Reputation: 33

How to formulate a WHERE condition with array input?

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?

Upvotes: 2

Views: 116

Answers (3)

Stefanov.sm
Stefanov.sm

Reputation: 13069

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
);

Upvotes: 0

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659207

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:

Upvotes: 1

Brian K. Burge
Brian K. Burge

Reputation: 175

use the IN clause

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

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

Upvotes: 0

Related Questions