clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1044295 fiddles created (9414 in the last week).

CREATE TABLE search (id int PRIMARY KEY, search_on text, comment text); INSERT INTO search (id, search_on, comment) VALUES ( 1, 'abc123456789', 'leading') , ( 2, '123abc456789', 'nested') , ( 3, '123456789abc', 'trailing') , ( 4, 'abc123abc456', 'leading, nested 1x') , ( 5, '123abc456abc', 'trailing,nested 1x') , ( 6, 'abcabcabc123', 'leading, nested 2x') , ( 7, '123abcabcabc', 'trailing nested 2x') , ( 8, '1abcabcabc23', 'nested 3x') , (10, 'abc12' , 'leading short') , (11, '12abc' , 'trailing short') , (12, '1abc2' , 'nested short'); CREATE INDEX index_search_search_on ON search USING gist (search_on gist_trgm_ops);
11 rows affected
 hidden batch(es)


SET pg_trgm.similarity_threshold = .01; -- show weak matches, too
 hidden batch(es)


-- show trigrams (SELECT search_on, show_trgm(search_on) FROM search LIMIT 3) UNION ALL SELECT 'abc', show_trgm('abc');
search_on show_trgm
abc123456789 {" a"," ab",123,234,345,456,567,678,789,"89 ",abc,bc1,c12}
123abc456789 {" 1"," 12",123,23a,3ab,456,567,678,789,"89 ",abc,bc4,c45}
123456789abc {" 1"," 12",123,234,345,456,567,678,789,89a,9ab,abc,"bc "}
abc {" a"," ab",abc,"bc "}
 hidden batch(es)


-- similarity is already biased in your direction SELECT *, search_on <-> 'abc' AS distance FROM search WHERE search_on % 'abc' ORDER BY search_on <-> 'abc';
id search_on comment distance
10 abc12 leading short 0.571429
6 abcabcabc123 leading, nested 2x 0.7
11 12abc trailing short 0.75
4 abc123abc456 leading, nested 1x 0.769231
1 abc123456789 leading 0.785714
7 123abcabcabc trailing nested 2x 0.818182
5 123abc456abc trailing,nested 1x 0.857143
3 123456789abc trailing 0.866667
12 1abc2 nested short 0.888889
8 1abcabcabc23 nested 3x 0.916667
2 123abc456789 nested 0.9375
 hidden batch(es)


-- *always* sort leading matches on top SELECT * , search_on <-> 'abc' AS distance , search_on ILIKE 'abc%' AS prefix FROM search WHERE search_on % 'abc' ORDER BY search_on NOT ILIKE 'abc%' -- prefix matches first , search_on <-> 'abc'; -- then sort by distance
id search_on comment distance prefix
10 abc12 leading short 0.571429 t
6 abcabcabc123 leading, nested 2x 0.7 t
4 abc123abc456 leading, nested 1x 0.769231 t
1 abc123456789 leading 0.785714 t
11 12abc trailing short 0.75 f
7 123abcabcabc trailing nested 2x 0.818182 f
5 123abc456abc trailing,nested 1x 0.857143 f
3 123456789abc trailing 0.866667 f
12 1abc2 nested short 0.888889 f
8 1abcabcabc23 nested 3x 0.916667 f
2 123abc456789 nested 0.9375 f
 hidden batch(es)


-- prefix, suffix, then the rest: SELECT * , search_on <-> 'abc' AS distance , search_on ILIKE 'abc%' AS prefix , search_on ILIKE '%abc' AS suffix FROM search WHERE search_on % 'abc' ORDER BY search_on NOT ILIKE 'abc%' -- prefix matches first , search_on NOT ILIKE '%abc' -- suffix matches next , search_on <-> 'abc'; -- then sort by distance
id search_on comment distance prefix suffix
10 abc12 leading short 0.571429 t f
6 abcabcabc123 leading, nested 2x 0.7 t f
4 abc123abc456 leading, nested 1x 0.769231 t f
1 abc123456789 leading 0.785714 t f
11 12abc trailing short 0.75 f t
7 123abcabcabc trailing nested 2x 0.818182 f t
5 123abc456abc trailing,nested 1x 0.857143 f t
3 123456789abc trailing 0.866667 f t
12 1abc2 nested short 0.888889 f f
8 1abcabcabc23 nested 3x 0.916667 f f
2 123abc456789 nested 0.9375 f f
 hidden batch(es)