1

I have a table that has data that looks like this:

id  oe
--  ---------------------------------------------------
 1  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 2  64526915083,64526953474
 3  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 4  F4DH8C607AA,F4DHAA
 5  F4DH8C607AA,F4DHAA
 6  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
 7  64526915083,64526953474
 8  F4DH8C607AA,F4DHAA
 9  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
11  04715SNAA90,04715SNAA90ZZ,71501SNAA00,71501SNAA00ZZ
12  64526915083,64526953474
13  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14  F4DH8C607AA,F4DHAA
15  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA

How can I write a query where I can pass an array of values and return rows where that column contains any of the values in the array?

This works because I get another value from a lookup table and I'd rather not do it that way as there is no index on the hol column in the pubnet table.
[please don't suggest adding one as I cannot modify DB]

SELECT * FROM pubnet 
where hol in (select hol from id2hol where id in ('F3DH19860AA', 'F4DHAA'))

This would be the result set:

id  oe
--  ---------------------------------------------------
 4  F4DH8C607AA,F4DHAA
 5  F4DH8C607AA,F4DHAA
 8  F4DH8C607AA,F4DHAA
 9  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
10  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
13  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
14  F4DH8C607AA,F4DHAA
15  F3DH19860AA,F3DHAA,F4DH19860AA,F4DHAA
  • 1
    Remember to disclose your version of Postgres. And clarify table and column names hol or oe? A CREATE TABLE statement showing names, data types and constraints is always best, and we can easily build a test case from it. – Erwin Brandstetter Apr 27 '18 at 2:56
1

You need to convert the comma separated values to an array, then you can use the overlaps operator &&

select *
from pubnet
where string_to_array(oe, ',') && array['F3DH19860AA', 'F4DHAA']
  • That is MUCH slower than my other query, 16+ sec to < 3 sec. – MB34 Apr 26 '18 at 19:27
  • 1
    @MB34: shrug. If you violate the rules of normalization then what do you expect? But could create an index on string_to_array(oe, ',') – a_horse_with_no_name Apr 26 '18 at 19:29
  • Yeah, it also doesn't return other records with the same hol value(s) that have no eo values. Around 40 more records. I'll try to write it a little cleaner, I guess. – MB34 Apr 26 '18 at 19:55
  • While not good for my uses, I'll accept anyway. – MB34 Apr 26 '18 at 21:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.