CODEWITHSUNDEEP
postgresqlfull-text-search
Justin
Justin

Reputation: 530

Order full text search by exact match THEN prefixed matches

How can I ensure my full text search results are ordered by exact matches and THEN prefixed matches?

SELECT ticker, name, ts_rank(document, to_tsquery('english', 'MAT:*')) AS rank
FROM (
  SELECT *, setweight(to_tsvector('english', ticker), 'A') || setweight(to_tsvector('english', name), 'B') AS document
  FROM ( VALUES
     ('MATI-R' , 'MATICHON PCL.NVDR')
    ,('MATCH-R', 'MATCHING MAXIMIZE SLN. NVDR')
    ,('MATV'   , 'MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06')
    ,('MAT'    , 'MATISSE HOLDINGS DEAD - 03/10/06')
    ,('MAT'    , 'MATTEL')
  ) data (ticker,name)
) ss ORDER BY rank DESC

I tried a few of the suggestions on https://www.postgresql.org/docs/9.5/static/datatype-textsearch.html like to_tsquery('english', 'MAT:A & MAT:*B') but none seem to give me the ordering I'm looking for. The current output is

 ticker  |                           name                           |   rank   
---------+----------------------------------------------------------+----------
 MATI-R  | MATICHON PCL.NVDR                                        |  1.45903
 MATCH-R | MATCHING MAXIMIZE SLN. NVDR                              |  1.27665
 MATV    | MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06 |  1.09427
 MAT     | MATISSE HOLDINGS DEAD - 03/10/06                         | 0.851098
 MAT     | MATTEL                                                   | 0.851098

when I want something more like 

 ticker  |                           name                           |   rank
---------+----------------------------------------------------------+----------
 MAT     | MATTEL                                                   | ??
 MAT     | MATISSE HOLDINGS DEAD - 03/10/06                         | ??
 MATCH-R | MATCHING MAXIMIZE SLN. NVDR                              | ??
 MATI-R  | MATICHON PCL.NVDR                                        | ??
 MATV    | MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06 | ??

Upvotes: 4

Views: 2060

Answers (1)

klin
klin

Reputation: 121784

Use LIKE or ILIKE: 

SELECT ticker, name, ts_rank(document, to_tsquery('english', 'MAT:*')) AS rank
FROM (
  SELECT *, setweight(to_tsvector('english', ticker), 'A') || setweight(to_tsvector('english', name), 'B') AS document
  FROM ( VALUES
     ('MATI-R' , 'MATICHON PCL.NVDR')
    ,('MATCH-R', 'MATCHING MAXIMIZE SLN. NVDR')
    ,('MATV'   , 'MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06')
    ,('MAT'    , 'MATISSE HOLDINGS DEAD - 03/10/06')
    ,('MAT'    , 'MATTEL')
  ) data (ticker,name)
) ss ORDER BY name LIKE concat('%', ticker, '%') desc, rank DESC

 ticker  |                           name                           |   rank   
---------+----------------------------------------------------------+----------
 MAT     | MATISSE HOLDINGS DEAD - 03/10/06                         | 0.851098
 MAT     | MATTEL                                                   | 0.851098
 MATI-R  | MATICHON PCL.NVDR                                        |  1.45903
 MATCH-R | MATCHING MAXIMIZE SLN. NVDR                              |  1.27665
 MATV    | MATAV-CABLE SYS.MEDIA SPN.ADR 1:2 DEAD - DELIST.03/07/06 |  1.09427
(5 rows)

Upvotes: 5

Related Questions