Home > Net >  Postgres IN clause on a two column unique constraint?
Postgres IN clause on a two column unique constraint?

Time:12-25

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