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.