Postgres full text search for partial word

Question

I've been seeing a lot of examples around like this one: postgres full text search like operator

They all specify that you can do a prefix search like this:

SELECT * 
FROM eventlogging 
WHERE description_tsv @@ to_tsquery('mess:*');

and it will retrieve a word like: "message"

However, what I do not see anywhere is whether or not there is a way to search for different parts of a word, such as a suffix?

The example that I am having trouble with right now is this:

CREATE TABLE IF NOT EXISTS project (
    id VARCHAR NOT NULL,
    org_name VARCHAR NOT NULL DEFAULT '',
    project_name VARCHAR NOT NULL DEFAULT ''
);

insert into project(id, org_name, project_name) values ('123', 'org', 'proj');
insert into project(id, org_name, project_name) values ('456', 'huh', 'org');
insert into project(id, org_name, project_name) values ('789', 'orgs', 'project');


CREATE OR REPLACE FUNCTION get_projects(query_in VARCHAR)
RETURNS TABLE (id VARCHAR, org_name VARCHAR, project_name VARCHAR) AS $$
BEGIN
  RETURN QUERY
  SELECT * FROM project WHERE (
    to_tsvector('simple', coalesce(project.project_name, '')) || 
    to_tsvector('simple', coalesce(project.org_name, ''))
  ) @@ to_tsquery('simple', query_in);
END;
$$ LANGUAGE plpgsql;

The following example returns:

select * from get_projects('org');

id  org_name    project_name
----------------------------
123 org         proj
456 huh         org

My question is: why does it not return orgs? Similarly, if I search for proj, I only get the project named "proj" but not the one named "project."

Bonus points: how can I get results if I search for a substring? For example, if I search for the string jec, I would like to get back the project named project. I'm not really looking for fuzzy searching, but I would say that I am looking for substring searching.

Am I completely wrong to be using to_tsquery? I also tried plainto_tsquery and I tried using english instead of simple, but several references said to stick with simple.

Laurenz Albe · Accepted Answer · 2022-02-15 17:15:19Z

3

Full text search is different from substring search. Full text search is about searching whole words, omitting frequent words from indexing, ignoring inflection and the like. PostgreSQL full text search extends that somewhat by allowing prefix searches.

To search for substrings, you have to search with a condition like

WHERE word ~ 'suffix\M'

(This would be a suffix search with the regular expression matching operator ~.)

To speed up a search like that, create a trigram index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ON tab USING gin (doc gin_trgm_ops);

answered Feb 15, 2022 at 17:15

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Trevor Hodde Over a year ago

Thank you for the clarification. I see that it is possible to search multiple columns using the regex matching operation like this: WHERE (project.project_name || project.org_name) ~ query_in. However, is it possible to search multiple columns across different tables this way?

Laurenz Albe Over a year ago

Sure, if you concatenate two columns from a join result - but there is no way to speed that up with an index. I recommend the UNION ALL of several queries, each on a single table.

jjanes · Accepted Answer · 2022-02-15 18:42:49Z

3

So-called prefix searching doesn't really thematically belong in full text searching. I think it was tossed in because, given that tokens would be stored in a btree anyway, adding that "feature" was free. No other types of partial matching are mentioned in the context of FTS because they don't exist.

You discuss the partial matching that does exist with FTS, the :* notation. But then in your example, you don't actually use it. That is why you don't see it working, because you don't use it. If you do use it, it does work:

select * from get_projects('org:*');

But given your description, it sounds like you don't want FTS in the first place. You want LIKE or regex, perhaps with index support from pg_trgm.

but several references said to stick with simple.

It is hard to know how good the judgement of anonymous references are, but if you only want to use 'simple' than most likely you shouldn't be using FTS in the first place. 'simple' is useful for analyzing or learning or debugging real FTS situations, and can be used as a baseline for building up more complex configurations.

answered Feb 15, 2022 at 18:42

jjanes

44.9k5 gold badges39 silver badges48 bronze badges

1 Comment

Trevor Hodde Over a year ago

Yes, you are absolutely right, it turns out that I didn't actually want FTS, I was conflating the meanings. I was able to solve this using a regex. I didn't realize how FTS worked under the hood, but this makes a lot of sense. Thank you for the detailed explanation.

Collectives™ on Stack Overflow

Postgres full text search for partial word

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related