How to create a Postgres GIST Index (gist_trgm_ops) with two concatenated fields using Prisma ORM

70 Views Asked by At

Let's assume I have table with multiple columns. For instance:

id int 
first_name varchar(50) 
first_name varchar(50)

example record would be

+----+------------+------------+
| id | first_name | first_name |
+----+------------+------------+
|  1 | John       | Doe        |
+----+------------+------------+

This project uses Prisma ORM to manage the database and migrations:

model table_test {
  id                    String                        @id
  first_name            String                        @db.VarChar(50)
  last_name             String                        @db.VarChar(50)
}

I need to use the Postgres extension "pg_trgm"

It is necessary to try to point out possible duplicate full names already existing in the table... So I have a person's full name and I need to look that up in the database.

For this I'm trying to create an Index by concatenating the fields "first_name" and "last_name" which I can manually do as follows:

CREATE INDEX table_test_full_name ON table_test using gist ((first_name || ' ' || last_name) gist_trgm_ops);

And with that I can perform a database search like this:

SELECT first_name, last_name, similarity('John Doni', first_name || ' ' || last_name) sml 
FROM table_test
WHERE ((first_name || ' ' || last_name) <-> 'John Doni') < 0.5 
ORDER BY ((first_name || ' ' || last_name) <-> 'John Doni') 
LIMIT 10;

and this works properly... I can see through "EXPLAIN"/"ANALYSE" that the index is used.


My doubt now is: How to replicate this same concatenated index in prism The best I could get using the prism syntax was something like this:

@@index([first_name(ops: raw("gist_trgm_ops")), last_name(ops: raw("gist_trgm_ops"))], name: "table_test_full_name_gist", type: Gist)

Generated migration:

CREATE INDEX "table_test_full_name_gist" ON "table_test" USING GIST ("first_name" gist_trgm_ops, "last_name" gist_trgm_ops);

But this does not represent the same index. I don't want a multi-column index... but rather an index with concatenated values.

0

There are 0 best solutions below