How to get exact match when the column used for filtering is comma-separated list in postgresql

Question

I have a column which will have values like:

FC
FCW
FC,FCW
FX,FC

My input used for filtering should give correct result (match the exact value in the column) ex:

FC like 'FC,FCW' --> true
FC like 'FCW'  --> false

when I pass FC--> only 1st, 3rd and last record should be returned. (i.e, only FCW should not be matched)

Splitting the column using regexsplit and then checking the value is one way of doing. Since the table data is huge, this way takes lot of time. Is there any other simple way of doing this operation?

Answer 1

This is a really bad data model and your performance problem is a direct result of that bad design.

One way to do this, is to convert the bad comma separated string to a slightly less bad array:

select *
from the_table
where string_to_array(the_column, ',') @> array['FC']

This could be supported by a GIN index:

create index on the_table using gin (string_to_array(the_column, ','));