Rails migration fails for`tsvector` with `ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error`

Question

I'm trying to run migrations on an existing Rails 7 app implementing search functionality in PostgreSQL. Unfortunately, I'm having a problem with getting the migration to add the tsvector column.

Error when trying to run bundle exec rake db:migrate --trace:

rails aborted!
ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "("
LINE 1: ...earchable_index_col" tsvector GENERATED ALWAYS AS (to_tsvect...

Link to full console output: Public gist

The full migration file:

class AddIndexForSearchableFieldsInDevelopers < ActiveRecord::Migration[7.0]
  def up
    execute <<-SQL
      ALTER TABLE developers
        ADD COLUMN textsearchable_index_col tsvector
          GENERATED ALWAYS AS (to_tsvector('simple', coalesce(hero, '') || ' ' || coalesce(bio, ''))) STORED;
    SQL

    add_index :developers, :textsearchable_index_col, using: :gin, name: :textsearchable_index
  end

  def down
    remove_index :developers, name: :textsearchable_index
    remove_column :developers, :textsearchable_index_col
  end
end

Ruby: ruby 3.1.2p20 (2022-04-12 revision 4491bb740a) [x86_64-darwin21]
PostgreSQL: psql (PostgreSQL) 14.3
Rails: Rails 7.0.2.4
MacBook: On MacOS Monterey MacBook Pro (13-inch, 2020) 12.3.1

Answer 1

Found it! The root cause was because that syntax isn't supported by my old PostgreSQL version (installed via Homebrew).

Upgrading like so fixed all the things:

Installed PostgreSQL via Homebrew?

Check your PostgreSQL version like so:

psql [username with correct permissions]

Then, check your version like so:

select version();

Your version should be at least 13.7. Here's mine:

 PostgreSQL 13.7 on x86_64-apple-darwin21.3.0, compiled by Apple clang version 13.1.6 (clang-1316.0.21.2), 64-bit

If your version is lower than 13.7, upgrade like so:

brew install postgresql@13