Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Searching array by unique pair constraint

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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;
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading