Select repeated values using an array containing repeated elements-CodePudding

How can I select rows using an array of input values, where any repeated input values get a corresponding repeated output?

Example:

myTable is:

 key | name  
----- -------
   1 | Alice
   2 | Bob
   3 | Chuck

and

SELECT name FROM myTable WHERE key IN (1,1,2,3,3);

gives:

 name  
-------
 Alice
 Bob
 Chuck
(3 rows)

This ignores the repetitions in the input array. I want to get this output:

 Alice
 Alice
 Bob
 Chuck
 Chuck
(5 rows)

More explanation:

We can select rows using an array of input values and the IN operator.

But repeated values in the array are ignored: it is effectively using the distinct input values as a set.

I want to get N outputs from N input values, even if some inputs are repeated.

Note: I am using PostgreSQL 12.9 and psycopg2 from python3, though that presumably makes no difference.

CodePudding user response：

you can unnest an array and join it with your table :

select * 
from myTable m
join unnest(array[1,1,2,3,3]) keys (k)
on m.key = keys.k

db<>fiddle here

CodePudding user response：

You can select a union of your keys in a CTE, then join it to your table:

with keys as
(select 1 as key
union all select 1
union all select 2
union all select 3
union all select 3)
select name
from keys natural join myTable;

Fiddle