2

I have a table with a column named prefix storing 'aaaa'. My application has a long string, eg. 'aaaabbbbccccdddd'. I want to select all rows in which a particular column is a prefix of 'aaaabbbbccccdddd'. The length of the prefix is variable.

How can I do it in Postgres?

I tried

SELECT * FROM my_table WHERE '%' || prefix || '%' ILIKE 'aaaabbbbccccdddd'

but it does not match.

1
7

First, your way of using [I]LIKE is wrong:

string LIKE pattern 

So, string first, pattern second.

Furthermore, as you are after prefixes only, the starting wildcard is not needed. Something like this should work:

SELECT * FROM my_table WHERE 'aaaabbbbccccdddd' ILIKE prefix || '%';

A small proof of this:

WITH my_table (prefix) AS (
VALUES ('abcde'),
       ('abcd'),
       ('abcE'),
       ('bcde')
)
SELECT * FROM my_table WHERE 'abcdefghijkl' ILIKE prefix || '%';

 prefix 
────────
 abcde
 abcd
0
6

Another option would be to create all the prefixes of the selected word and test them with equality =, (or ILIKE*), against the strings in the table:

select t.prefix 
from 
  (select 'aaaabbbbccccdddd'::text as word) as w,
  generate_series(0, length(w.word)) as g,
  lateral 
  (select t.prefix 
   from my_table as t
   where t.prefix = left(w.word,g)
   -- where t.prefix ilike left(w.word,g)
  ) as t ; 

This might be more efficient in big tables, doing only a few (as many as the length of word, +1) equality checks using index seeks and not performing a full index or table scan.

*: If you need case insensitive check, then ILIKE should be used as the commented code above. The improvement in this case from the 'abcdefghijkl' ILIKE prefix || '%' answer is that the prefix column is used on the left of ILIKE and not on the right side. So, the query will be performing a few column ILIKE 'aaabbbccdd' checks and not (an arbitrary number of) 'aaabbbccdd' ILIKE column||'%' checks.

Both ways we have a number of different ILIKE conditions but in one case that number is the length of the searching word and in the other the number of rows in the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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