Can't run rake db:migrate a tsvector GIN index for Postgresql in Rails using Railscast sample

Question

I'm following along on Ryan Bates' excellent tutorial on using the built-in PostgresQL full-text search in Rails. I'm currently using pg_search gem un-indexed no problem but I need to improve the performance. I am using tsvector with "english" dictionary specified.

I'm using PostgreSQL version 9.1.4

Per Ryan's instructions, I have run a new migration with this code specifying two new indexes that I would like to create. Here is the schema first:

create_table "references", :force => true do |t|
  t.string   "title"
  t.string   "type"
  t.datetime "created_at",         :null => false
  t.datetime "updated_at",         :null => false
  t.string   "public_url"
  t.string   "content_type"
  t.integer  "file_size"
  t.text     "overview"
  t.text     "body"
  t.text     "full_text"
  t.integer  "folder_id"
end

My migration looks like this:

def up
  execute "create index references_title on references using gin(to_tsvector('english', title))"
  execute "create index references_full_text on references using gin(to_tsvector('english', full_text))"
end

def down
  execute "drop index references_title"
  execute "drop index references_full_text"
end

I have also gone ahead and uncommented the :sql option in application.rb

config.active_record.schema_format = :sql

I continue to get the same rake aborted error:

==  AddSearchIndexesToReferences: migrating ===================================
-- execute("CREATE INDEX references_title on references using gin(to_tsvector('english',    title))")
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  syntax error at or near "references"
LINE 1: CREATE INDEX references_title on references using gin(to_tsv...
                                     ^
: CREATE INDEX references_title on references using gin(to_tsvector('english', title))

Spangler

for Rails >= 5 : #40006958 – Infection

Answer 1

REFERENCES is a keyword used with foreign keys so you can't use it as a table name unless you double quote it:

def up
  execute %q{create index references_title on "references" using gin(to_tsvector('english', title))}
  execute %q{create index references_full_text on "references" using gin(to_tsvector('english', full_text))}
end

You'll also have to double quote that table name anywhere you use it in an SQL snippet. ActiveRecord will do the quoting for you if it is building the SQL though. If you're expecting to use the table name in a lot of SQL snippets then I'd recommend renaming the table so that you don't have to care about the quoting issue.

Tweedy