Setweight(to_tsvector('pg_catalog.english', coalesce(new.title,'')), 'A') || (In practice I'm using a GIN indexed preprocessed vector column)Īny Solutions and insights will be hugely appreciated.Įdit: here's the function and trigger used to populate the vector column CREATE FUNCTION vector_column_trigger() RETURNS trigger AS $$ Where to_tsvector(title) plainto_tsquery('The Big Bang') The query for reference: select * from some_table Modifying the vector settings and search types did not yield any results as well, though like I mentioned, the tsvectors are identical between the two databases so it should not behave this way regardless. I've tried experimenting with some test instances of various versions and playing around with the Aurora DB configs without any success. This has led me to the conclusion that there is some configuration that's different because the data is completely identical, but I may be wrong of course. In the local instance, searching for "The Big Bang" returns the row with "Big Bang" in the title as expected, but doing the exact same search in the Aurora DB instance returns no results, unless "The" is dropped from the search. An example from the docs SELECT to_tsquery('english', 'The & Fat & Rats') When converting the text to ts_vectors or ts_queries, words like "the" and "and" are supposed to be completely ignored when the text is normalized. Say I have a table with a "title" column and entry that has the title "Big Bang". One major example is searching for a phrase that has "the" in it. To support text search capabilities in my API I've decided to use Postgres's built in text search engine and I've ran into some strange behaviors that only occur on the Aurora DB instance but not the local Postgres instance. I have an instance of a Postgres v14 database running on an on premise server, replicating data to a Postgres compatible Aurora Serverless V2 DB on AWS.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |