I have a table with a unique constraint across two columns, code
and print
, and an array of codes and prints. See below:
// these two arrays can get very big
codes: ('A', 'B', 'C', 'D')
prints: (1, 2, 3, 4)
Is there a way to get the rows with code A and print 1, code B and print 2, and so on, in one query? My initial thought was to use a SELECT * FROM table WHERE code IN (...)
query, but I'm not quite sure how to correctly "map" the print
to the correct code
.
Please let me know if something doesn't make sense and I'll do my best to clarify. Thanks!
CodePudding user response:
You can try this :
SELECT t.*
FROM table AS t
INNER JOIN unnest(array['A', 'B', 'C', 'D'], array[1, 2, 3, 4]) AS a(code, print)
ON t.code = a.code
AND t.print = a.print
Every element in both arrays are coupled two by two in the unnest()
function according to their order in their arrays.
CodePudding user response:
select the_table.*
from the_table
join unnest ('{A,B,C,D}'::text[], '{1,2,3,4}'::int[]) as t(a, b)
on (code, print) = (t.a, t.b);