Kysely•17mo ago

Postgres Full Text Search

Hello, I am trying to search queries using Postgres full-text search, and here is my current implementation:

await this.db.selectFrom("city")
            .where(sql`to_tsvector('english', name) @@ to_tsquery('english', 'nas:*')`)
            .selectAll()
            .execute();

await this.db.selectFrom("city")
            .where(sql`to_tsvector('english', name) @@ to_tsquery('english', 'nas:*')`)
            .selectAll()
            .execute();

the above implementation is working but my IntelliJ lint seems unhappy. I was wondering if is there any better way to write the same in a more typesafe way? Thank you!

Solution:

You can probably get rid of it like this

return sql`to_tsvector(${sql.lit('english')}, ${expr})`

return sql`to_tsvector(${sql.lit('english')}, ${expr})`

...

Jump to solution

10 Replies

koskimas•17mo ago

You can create a helper function like this https://kyse.link/?p=s&i=HOdRqqCjc03sUld0I6Yp Well, that is type-safe. How do you think it could be more type-safe? Actually this is a bit more type-safe. You can't accidentally pass in a string thinking it would be interpreted as a column https://kyse.link/?p=s&i=uKcVav7KKs0SKagW3gG4 Any comments?

SwapOP•17mo ago

Hey @koskimas , thanks for the reply, now its far better than my current implementation 😄 my lint is still complaining for the 'english' but seems managable for now, but for now i am getting error for the eb expression TypeError: eb is not a function

koskimas•17mo ago

You need to update to 0.26.x Why is it complaining about 'english'? If updating is not an option, use eb.cmpr(...) in place of eb(...)

SwapOP•17mo ago

Error is resolved after upgrading to the latest one, thanks its showing <query expression> expected, got ''english''

koskimas•17mo ago

Could you show me the code you have?

SwapOP•17mo ago

Here is the code

koskimas•17mo ago

Do you have some kind of extension that interprets SQL?

SwapOP•17mo ago

I dont have installed any, but seems Intellij Idea has builtin to check sql

Solution

koskimas•17mo ago

You can probably get rid of it like this

return sql`to_tsvector(${sql.lit('english')}, ${expr})`

return sql`to_tsvector(${sql.lit('english')}, ${expr})`

SwapOP•17mo ago

Seems everyone is happy now :), thanks for your help and time @koskimas

Gaming

Programming

Postgres Full Text Search