I have a postgres database table containing a unique constraint on field1 and field2.
I have an array of values of field1 and a fixed value for field2 that I wish to use for searching.
How can I ‘pair’ the array of field1 values to the fixed field2 value and search which ‘pairs’ match the unique constraint?
So if I have records
ID: 1
field1: 1
field2: 2
ID: 2
field1: 2
field2: 2
ID: 3
field1: 3
field2: 2
And the array of field1 values
1,3,5
and fixed value 2 for field2, then my search should return records 1 and 3, since the unique constraints of field1,field2 are 1,2 and 3,2
EDIT: I can manually ‘pair’ the values in the array if that makes the query easier to create
>Solution :
Not sure what you mean by "pairing", you can just use normal WHERE filters.
SELECT *
FROM YourTable t
WHERE t.field1 IN ANY(ARRAY[1, 3, 5])
AND t.field2 = 2;
Or using a normal IN clause
SELECT *
FROM YourTable t
WHERE t.field1 IN (1, 3, 5)
AND t.field2 = 2;