Select repeated values using an array containing repeated elements

Question

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.

eshirvana · Accepted Answer · 2022-03-13 18:01:10Z

2

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

answered Mar 13, 2022 at 18:01

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mr Central Over a year ago

Thanks, that's excellent. And in psycopg2 it becomes: cursor.execute("SELECT name from myTable m join unnest(%s) keys on m.key = keys",([1,1,2,3,3],))

Mr Central Over a year ago

No: passing in a Python list makes the "ARRAY" keyword appear magically! See "lists adaptation" here psycopg.org/docs/usage.html

Zakaria · Accepted Answer · 2022-03-13 17:53:22Z

0

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

answered Mar 13, 2022 at 17:53

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

1 Comment

Mr Central Over a year ago

Thank you, this works. However, it is best if the array is kept together, as in the other answer.

Collectives™ on Stack Overflow

Select repeated values using an array containing repeated elements

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related