tsquery | TypeScript AST query library | Parser library

We have a table of following structure in postgresql (simplified):

I am trying to figure out how to create TsQuery where when I query "Executive Sales" I get only results that have "Executive Sales" as the first and second word. For example

• Executive Sales Manager
• Executive Sales Representative etc.

I have tried with Executive <-> Sales:* but this gives me results like:

• Account Executive Sales

I have two models Author and Book which are related via m2m (one author can have many books, one book can have many authors)

Often we need to query and match records for ingests using text strings, across both models ie: "JRR Tolkien - Return of the King" when unique identifiers are not available.

I would like to test if using SearchVectorField with GIN indexes can improve full-text search response times - but since the search query will be SearchVector(author__name, book__title) It seems that both models need a SearchVectorField added.

This becomes more complicated when each table needs updating since it appears Postgres Triggers need to be set up on both tables, which might make updating anything completely untenable.

What is the modern best practice in Django for adopting vectorised full-text search methods when m2m related models are concerned? Should the SearchVectorField be placed through a table? Or in each model? How should triggers be applied?

I've been searching for guides on this specifically - but no one seems to mention m2ms when talking about SearchVectorFields. I did find this old question

Also, if Postgres is really not the way forward in modern Django I'd also gladly take direction in something better suited/supported/documented. In our case, we are using Postgres 11.6.

We've had great results using a K-NN search with a GiST index with gist_trgm_ops. Pure magic. I've got other situations, with other datatypes like timestamp where distance functions would be quite useful. If I didn't dream it, this is, or was, available through pg_catalog. Looking around, I can't find a way to search on indexes by such properties. I think what I'm after, in this case, is AMPROP_DISTANCE_ORDERABLE under-the-hood.

Just checked, and pg_am did have a lot more attributes than it does now, prior to 9.6.

Is there another way to figure out what options various indexes have with a catalog search?

jjanes' answer inspired me to look at the system information functions some more, and to spend a day in the pg_catalog tables. The catalogs for indexes and operators are complicated. The system information functions are a big help. This piece proved super useful for getting a handle on things:

https://postgrespro.com/blog/pgsql/4161264

I think the conclusion is "no, you can't readily figure out what data types and indexes support proximity searches." The relevant attribute is a property of a column in a specific index. However, it looks like nearest-neighbor searching requires a GiST index, and that there are readily-available index operator classes to add K-NN searching to a huge range of common types. Happy for corrections on these conclusions, or the details below.

https://www.postgresql.org/docs/current/gist-builtin-opclasses.html

From various bits of the docs, it sounds like there are distance (proximity, nearest neighbor, K-NN) operators for GiST indexes on a handful of built-in geometric types.

I want to build a table where each row contains a string and the number of rows where that string appears as a prefix

Basically I want

I face what semms to be stupid problem. Here is a reprex:

I am attempting to query a Postgres db through an Express REST API. One column (json_object_data) includes json, upon which I wish to query on the 'name' key.

It works fine if I enter the query directly into the db console. What's giving me an issue is passing a search term into an HTTP request to an Express route.

FYI, other requests to the Express app (GET, POST, PUT, and DELETE), that do not attempt to query the json column, work fine.

Node Express code:

I'm using postgres' tsquery function to search in a field that might contain letters in multiple languages and numbers. it seems that in every case the search works up to a part of the searched phrase and stops working until you write the full phrase.

for example: searching for the name '15339' outputs the right row when the search term is '15339' but if it's '153' it won't.

searching for Al-Alamya, if the term is 'al-' it will work and return the row, but adding letters after that, for example, 'al-alam' won't return it until I finish writing the full name ('Al-Alamya').

my query:

I'm working on an exercise for a Bootcamp and the function isn't producing the desired effect. So the goal is to query the database with a first name/part of a first name. I can successfully query the DB and return results. However, when I map the results it's not mapping everything that I need, it's only returning the id, while everything else is returning undefined. I have a similar function that grabs all data from the database, returns all information/functionality, and is working fantastically.

This is my DB query: