Converting comma separated string to integer array in Postgres

Question

I am trying to convert the Comma separated string into an integer array (integer[]) to use in Where clause.

I have tried cast, ::Int which didn't work. Appreciate your input

Example

Table A   |  Table B
ID        |  Set_id
2         |  14,16,17
1         |  15,19,20
3         |  21

My Query:

Select * 
from Table a, table b 
where a.id in b.set_id

Answer 1

You need to convert the string to a proper integer array if you want to use that for a join condition.

Select * 
from Table a
  join table b on a.id = any(string_to_array(b.set_id, ',')::int[]);

---

But a much better solution would be to properly normalize your tables (or at least stores those IDs in an integer array, not a varchar column)