CODEWITHSUNDEEP
postgresqldatabase-designindexingpattern-matchingpostgresql-performance
nourdine
nourdine

Reputation: 7597

Look up in a white space separated string in Postgres

I have a character varying field in postgres containing a 1-white-space-separated set of strings. E.g.:

I put --> and <-- to show where the strings start and end (they are not part of the stored string itself)

I need to query this field to find out if the whole string contains a certain word (apple for instance). A possible query would be 

SELECT * FROM table WHERE thefield LIKE '%apple%'

But it sucks and won't scale as b-tree indexes only scale if the pattern is attached to the beginning of the string while in my case the searched string could be positioned anywhere in the field.

How would you recommend approaching the problem?

Upvotes: 2

Views: 3321

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656952

Consider database-normalization first.

While working with your current design, support the query with a trigram index. That will be fast.

See:

Upvotes: 5

Related Questions