CODEWITHSUNDEEP
postgresqlfull-text-searchexact-match
Romper
Romper

Reputation: 2257

Full-Text Search ordered by exact match in PostgreSQL

I have a query like

select * from mytable where posttext @@ to_tsquery('Intelence');

I have texts with 'intelence' word and with 'intel'.

I want to return results where exact match of the keyword 'Intelence' will be ordered before results with 'intel' ?

Upvotes: 0

Views: 604

Answers (1)

Oto Shavadze
Oto Shavadze

Reputation: 42813

Maybe not most optimal and performant way, but using regex match in order by should do what you want

with mytable( posttext ) as(
select 'a Intelence' union all
select 'intel someIntelence' union all
select 'a intel' union all
select 'a Intelence b' 
)

select * 
from mytable 
where posttext @@ to_tsquery('Intelence')  
order by posttext ~* '(^|\s)intelence(\s|$)' desc

Upvotes: 1

Related Questions