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?

Improve this question

1 Answer 1

Reset to default
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, ','));
Improve this answer
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. Commented Jan 19, 2023 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 Commented Jan 19, 2023 at 14:45

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.