What's the difference between % and ILIKE in PostgresSQL WHERE query?-CodePudding

Question says it all really... I've been able to use both of these but can't tell what the difference is. Using pg_trgm module...

SELECT * from tbl WHERE postcode % 'w4%' LIMIT 10;


SELECT * from tbl WHERE postcode ILIKE 'w4%' LIMIT 10;

CodePudding user response：

ILIKE and the % operator are quite different.

% is the similarity operator used by pg_trgm. Its outputs depends on the set similarity threshold.

set pg_trgm.similarity_threshold = .6;
select 'abcdef' % 'abZZef';
--> false

set pg_trgm.similarity_threshold = .1;
select 'abcdef' % 'abZZef';

--> true

On the other hand Ilike looks for (partial) string equality

select 'abcdef' ilike 'abc%';
--> true
select 'abcdef' ilike 'abZ%';
--> false