0

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?

1 Answer 1

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, ','));
2
  • Actually the table is not the direct table in DB. Its the output of another common function which is used for further calculation. Thanks for bringing up the concept of array to use here. Researched further on this and used && instead of @> which suits more for the requirement.
    – Ross
    Jan 19 at 14:33
  • Well, if it's the output of a query, then you won't be able to create an index for that. But maybe it makes more sense to filter those values in that query instead of afterwards
    – user1822
    Jan 19 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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