Drizzle Team•2y ago

Implement full text search in postgres

Hi. I stumbled upon this issue (https://github.com/drizzle-team/drizzle-orm/issues/247) which was very useful. I then wanted to add support for weights. I was able to achieve 99% of the task but the last 1% is where I'm stuck and need help. Generated sql:

ALTER TABLE "table_name" ADD COLUMN "vec" "tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED";

ALTER TABLE "table_name" ADD COLUMN "vec" "tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED";

Expected:

ALTER TABLE "table_name" ADD COLUMN "vec" tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED;

ALTER TABLE "table_name" ADD COLUMN "vec" tsvector GENERATED ALWAYS AS (setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(description, '')), 'B')) STORED;

https://github.com/drizzle-team/drizzle-orm/issues/247#issuecomment-1573541065

GitHub

Add Full-Text Search capability · Issue #247 · drizzle-team/drizzle...

This is a feature request for Full-Text Search functionality to perform a fulltext search on specific fields. Ideally with support for using GIN/GIST indexes to speed up full text search.

2 Replies

Valentin Galfre•12mo ago

Hey @Shubham-Sinha, sorry for bothering. How would I use this column "vec" to search through my users? I already added the column with the custom dataType

Shubham-SinhaOP•12mo ago

@Valentin Galfre You may want to use it like this

sql` ${users.vec} @@ to_tsquery(${"'" + searchTerm + "'"}) `

sql` ${users.vec} @@ to_tsquery(${"'" + searchTerm + "'"}) `

Gaming

Programming

Implement full text search in postgres