How to use TSVECTOR for PostgreSQL ?

[NicoSan20]

Hello everyone ! Do you know if there is a documentation on the setting up of the TSVECTOR for PostgreSQL ? (I'm trying to use the full-text search)

If I understand correctly, I have to use the DataTypes.TSVECTOR. So far, so good.

But when I add data, do I need to use the "to_tsvector" function too ? Or the fact that DataTypes.TSVECTOR, it does it automatically ?

We also agree that I need to set up an index on my column for it to work, right ?

[ephys]

You do need to use to_tsvector manually. I suppose it would be a potential improvement to do it automatically, but the impact of such a change need to be researched first

You also need to use to_tsquery when comparing

I can't say whether the column should be indexed, but I would say yes?

[NicoSan20]

@ephys : Thanks for your answer, it gives me a good idea of what to do.

After some research, I can confirm that adding "to_tsvector" automatically would be a very good improvement (it seems to me that prisma already does it). I hope taht you can add this on your roadmap.

Thank you again for your help.

[ephys]

The only issue being that doing it automatically may lead people to skip specifying the language parameter that to_tsvector uses.

Maybe if we added an option to the constructor of the datatype. e.g. DataTypes.TSVECTOR('english')

Some draft ideas:

const User = sequelize.define('User', {
  search: DataTypes.TSVECTOR('english'),
})

User.create({
  search: 'text',
});

// = to_tsvector('english', 'text')

User.create({
  search: fn('to_tsvector', 'french', 'text'),
});

// = to_tsvector('french', 'text')

I think it'd be a nice addition, let's open a feature request for it