1

I'm trying to use pg_search's function :trigram to find names similar to what I store in my DB. Unfortunately, as I'm still learning SQL, this error is a bit too much for me to handle at the moment.

My Card model:

class Card < ApplicationRecord
  include PgSearch
  pg_search_scope :spelled_like,
                  :against => :name,
                  :using => :trigram
end

What I try to do in rails C:

2.4.1 :005 > c = "Timeslream Navigator"
 => "Timeslream Navigator" 
2.4.1 :006 > Card.spelled_like(c)
  Card Load (1.9ms)  SELECT  "cards".* FROM "cards" INNER JOIN (SELECT "cards"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("cards"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'Timeslream' || ' ''') && to_tsquery('simple', ''' ' || 'Navigator' || ' ''')), 0)) AS rank FROM "cards" WHERE (((coalesce("cards"."name"::text, '')) % 'Timeslream Navigator'))) AS pg_search_6ac9a32c0ca8e84e5bf0c3 ON "cards"."id" = pg_search_6ac9a32c0ca8e84e5bf0c3.pg_search_id ORDER BY pg_search_6ac9a32c0ca8e84e5bf0c3.rank DESC, "cards"."id" ASC LIMIT $1  [["LIMIT", 11]]
ActiveRecord::StatementInvalid: PG::UndefinedFunction: ERROR:  operator does not exist: text % unknown
LINE 1: ...rds" WHERE (((coalesce("cards"."name"::text, '')) % 'Timeslr...

To clarify - the answer should be TimesTReam Navigator (and it does exist in my DB)

Postgres gives me a hint:

HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT  "cards".* FROM "cards" INNER JOIN (SELECT "cards"."id" AS pg_search_id, (ts_rank((to_tsvector('simple', coalesce("cards"."name"::text, ''))), (to_tsquery('simple', ''' ' || 'Timeslream' || ' ''') && to_tsquery('simple', ''' ' || 'Navigator' || ' ''')), 0)) AS rank FROM "cards" WHERE (((coalesce("cards"."name"::text, '')) % 'Timeslream Navigator'))) AS pg_search_6ac9a32c0ca8e84e5bf0c3 ON "cards"."id" = pg_search_6ac9a32c0ca8e84e5bf0c3.pg_search_id ORDER BY pg_search_6ac9a32c0ca8e84e5bf0c3.rank DESC, "cards"."id" ASC LIMIT $1

But when I try to call it I get another error that doesn't tell me much at this point:

SyntaxError: (irb):7: syntax error, unexpected tCONSTANT, expecting end-of-input
  "cards".* FROM "cards" INNER JOIN (SELECT "cards"."id" AS 
                              ^

Card schema:

ActiveRecord::Schema.define(version: 2018_05_31_102526) do

  # These are extensions that must be enabled in order to support this database
    enable_extension "plpgsql"
    enable_extension "pg_trgm"

  create_table "cards", force: :cascade do |t|
    t.string "name"
    t.string "set"
    t.float "price"
    t.string "image"
    t.string "type_line"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

end

Any ideas?

Improve this question
3
  • Can you add your schema for the cards table? Commented May 31, 2018 at 15:30
  • @DanielWestendorf Added :) Commented May 31, 2018 at 15:50
  • did you install the trigram extension? Commented Jun 1, 2018 at 12:15

2 Answers 2

Reset to default
7

To answer my own question:

  • pg_search is awesome!
  • In order to use trigram function you obviously have to install it.
  • My problem was, I installed it directly in psql, but did not run a proper migration inside my Rails app.

Here's the migration snippet that allowed me to fix my issue and start enjoying trigram's amazing functionality:

class InstallPgTrgmContribPackage < ActiveRecord::Migration[5.2]
  def up
    execute "CREATE EXTENSION pg_trgm;"
  end

  def down
    execute "DROP EXTENSION pg_trgm;"
  end
end
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

My issue was a bit different. Even though my extension was already installed, I would get this error prod AWS environments but not the lower ones. So I dropped the extension and re-created it.
That worked. Here was my error:

PG::UndefinedFunction: ERROR: operator does not exist: unknown <% text Caused by the pg_search gem using trigrams.

Alternatively you can disable using trigrams, but I wanted to keep them, so I kept researching

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.