Why is my tsquery not returning results for this word?

Questions / Help
1

Here’s the record that I’m trying to get back by searching for playstation.

%MyApp.Listings.Listing{
    __meta__: #Ecto.Schema.Metadata<:loaded, "listings">,
    category: #Ecto.Association.NotLoaded<association :category is not loaded>,
    category_id: "5429ca91-ebb6-4343-8262-a483d2c378ad",
    description: "BUenos dias!",
    id: "afa361d9-f1a3-46d9-b226-7b30bb96bb5f",
    inserted_at: ~N[2021-02-28 22:31:34],
    price: 100,
    search_tsvector: [
      %Postgrex.Lexeme{positions: [{4, :A}], word: "1"},
      %Postgrex.Lexeme{positions: [{3, :A}], word: "1tb"},
      %Postgrex.Lexeme{positions: [{2, :A}], word: "4"},
      %Postgrex.Lexeme{positions: [{5, nil}], word: "buen"},
      %Postgrex.Lexeme{positions: [{6, nil}], word: "dias"},
      %Postgrex.Lexeme{positions: [{1, :A}], word: "playstation"}
    ],
    slug: "playstation-4-1tb-1",
    title: "Playstation 4 1TB 1)",
    updated_at: ~N[2021-02-28 22:31:34],
    user: #Ecto.Association.NotLoaded<association :user is not loaded>,
    user_id: "692682a4-a5cc-4265-aa4f-eb8982f8e3f2"
  }

If I search for playstation the query is returning no records.

[debug] QUERY OK source="listings" db=0.6ms idle=8026.1ms
SELECT l0."id", l0."title", l0."slug", l0."description", l0."price", l0."search_tsvector", l0."user_id", l0."category_id", l0."inserted_at", l0."updated_at" FROM "listings" AS l0 WHERE (l0."search_tsvector" @@ to_tsquery('playstation')) []
[]

However if I search for buen, the record I am expecting comes back.

[debug] QUERY OK source="listings" db=0.5ms idle=8517.5ms
SELECT l0."id", l0."title", l0."slug", l0."description", l0."price", l0."search_tsvector", l0."user_id", l0."category_id", l0."inserted_at", l0."updated_at" FROM "listings" AS l0 WHERE (l0."search_tsvector" @@ to_tsquery('buen')) []

I must be misunderstanding how the Postgres ts_vectors are being created or used by the sql query I’m using. What am I doing wrong? :thinking:

execute("CREATE OR REPLACE FUNCTION listings_search_tsvector_trigger() RETURNS trigger AS $$
      begin
        new.search_tsvector :=
          setweight(to_tsvector('pg_catalog.spanish', coalesce(new.title,'')), 'A') ||
          setweight(to_tsvector('pg_catalog.spanish', coalesce(new.description,'')), 'D');
        return new;
      end
      $$ LANGUAGE plpgsql")

    #- Create the trigger to call the above stored procedure on inserts or updates
    execute("DROP TRIGGER IF EXISTS listings_search_tsvector_update ON listings")
    execute("CREATE TRIGGER listings_search_tsvector_update
              BEFORE INSERT OR UPDATE
              ON listings
              FOR EACH ROW EXECUTE PROCEDURE listings_search_tsvector_trigger()")
2

I think I found the problem.

If you notice I created the tsvector with language spanish and the query I was using didn’t specify the language so it was using the default language english.

Being specific and using spanish for both query and tsvector creation makes it work as expected.

Hope this helps would-be Googlers. This has been kicking my ass haha

2 Likes