Using postgres ENUM with rails yields `PG::DatatypeMismatch`

Question

Trying to update the value of a Postgres ENUM column throws the following exception:

ActiveRecord::StatementInvalid Exception: PG::DatatypeMismatch: ERROR: column "interesting_column" is of type interesting_thing but expression is of type integer

LINE 1: UPDATE "interesting_table" SET "interesting_column" = 0, "updated_a...

HINT: You will need to rewrite or cast the expression.

InterestingTable.first.update_attributes!(normal_column: 'food')
  # => perfectly fine
InterestingTable.first.update_attributes!(interesting_column: 'foo')
  # => above exception

Here is the migration to create the table:

class CreateInterestingTables < ActiveRecord::Migration
  def up
    execute <<-SQL
      CREATE TYPE normal_thing AS ENUM ('food', 'water', 'shelter');
      CREATE TYPE interesting_thing AS ENUM ('foo', 'bar', 'baz');
    SQL

    create_table :interesting_tables do |t|
      t.column :normal_column, :normal_thing
      t.column :interesting_column, :interesting_thing
    end
  end

  def down
    drop_table :interesting_tables
    execute 'DROP TYPE normal_thing'
    execute 'DROP TYPE interesting_thing'
  end
end

ndnenkov · Accepted Answer · 2016-06-30 07:10:57Z

The problem is that while the column has the correct type in the database, active record will try to interpret it as integer. You can verify that by running:

InterestingTable.columns
# => [#<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x007f7567f82260
#      @coder=nil,
#      @default=nil,
#      @limit=nil,
#      @name="id",
#      @null=false,
#      @precision=nil,
#      @primary=true,
#      @scale=nil,
#      @sql_type="integer",
#      @type=:integer>,
#     #<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x007f7568075690
#      @coder=nil,
#      @default=nil,
#      @limit=nil,
#      @name="normal_column",
#      @null=true,
#      @precision=nil,
#      @primary=false,
#      @scale=nil,
#      @sql_type="normal_thing",
#      @type=nil>,
#     #<ActiveRecord::ConnectionAdapters::PostgreSQLColumn:0x007f7568075668
#      @coder=nil,
#      @default=nil,
#      @limit=nil,
#      @name="interesting_column",
#      @null=true,
#      @precision=nil,
#      @primary=false,
#      @scale=nil,
#      @sql_type="interesting_thing",
#      @type=:integer>]

Note how the type for the second column is nil, while that of the last one is integer. String#to_i returns 0 if your string didn't start with a number, hence you get the error that you are trying to assign 0.

But why is that? The reason - interesting_thing contains the substring int, which the adapter considers an integer. This seems to be a long existing bug that hasn't been fixed up until rails 4.2. The offending method.

Possible solutions:

Migrate to rails 4.2+
Rename your ENUM to something that wont be matched
Monkey patch the adapter. Here is a quick fix.

Collectives™ on Stack Overflow

Using postgres ENUM with rails yields `PG::DatatypeMismatch`

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related