-1

This is how I have been doing searches for my own use:

SELECT * FROM table WHERE lower(textfield) ~ ('***=' || lower($1)) ORDER BY timestamp DESC LIMIT 100

That is, it case-insensitively finds any exact instance of "test string" inside the textfield column and sorts by the timestamp column. This makes sense for me, most of the time, but others expect very different behaviour. And sometimes, I admit I also would like it to be more lenient or more flexible.

People in general expect to be able to search for "croccodhiles" and find records containing "crocodile" and "crocs" and whatnot, sorted by perceived "relevance" or "similarity".

Is there a nice and robust query I can use to accomplish this, in the vein of what I already have, and which orders the results by "how similar they are" rather than by the timestamp? Something like:

SELECT * FROM table WHERE textfield SMARTMATCHES $1 ORDER BY RELEVANCE DESC LIMIT 100

I need for this to be done with only the built-in PostgreSQL features, as dealing with extensions is a nightmare to me.

  • 2
    You are looking for full text search – a_horse_with_no_name Feb 19 at 14:31
  • @a_horse_with_no_name: That link sadly does not help me. What is the actual query I need? – Elon Mautino Feb 19 at 15:14
  • Which version of Postgres are you using? – Colin 't Hart Feb 19 at 15:50
  • Welcome to the nightmare. The only thing that can help you is the pg_trgm extension, and you'll have to read the documentation. That is, once you have figured out the exact requirements - even fuzzy search needs a specification. – Laurenz Albe Feb 19 at 16:07
  • 1
    @ElonMautino You will find this site easier to use if you register and log in. Otherwise, you will need to always use the same device and browser because your credentials are cookie-based. That's why you couldn't comment on your own question (two separate unregistered accounts). Once you have registered, see dba.stackexchange.com/help/merging-accounts to get everything tidied up. – Paul White 9 Feb 19 at 16:36
1

The link provided does actually help , this tells you how to create queries that are Full Text Searches and a means to score the the results just like a search engine.

It is allot to take in at one time and its not a simple query

to get started read over

https://www.postgresql.org/docs/current/textsearch-intro.html#TEXTSEARCH-MATCHING

a very basic query looks like this

SELECT textfield::tsvector @@ 'User Search String'::tsquery;

This converts the column to specific data type that contains the list of words and the number of times the word is in the document/row.column

To rank the results use the ts_rank function

https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-RANKING

select textfield::tsvector @@ 'User Search String'::tsquery;
    ts_rank_cd(textfield::tsvector, 'User Search String'::tsquery) AS rank

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