CODEWITHSUNDEEP
ruby-on-railspostgresql
Zac Butko
Zac Butko

Reputation: 19

Trouble migrating PostgreSQL Alter Table using conversion

I need to migrate a PG database for a rails app - I'm updating the type of one column of a table from boolean to int. Here's the code:

class ChangeAdminToInt < ActiveRecord::Migration
  def up
    execute '
      ALTER TABLE "users"
      ALTER COLUMN "admin"
      TYPE int USING ("admin"::int)
      SET DEFAULT "1"
      '
  end
end

When I run bundle exec rake db:migrate I get the error:

PG::Error: ERROR:  syntax error at or near "SET"
LINE 5:  SET DEFAULT "1"

BUT, when I switch around the code to:

class ChangeAdminToInt < ActiveRecord::Migration
  def up
    execute '
      ALTER TABLE users 
      ALTER COLUMN admin 
      TYPE int USING (admin::int);
      SET DEFAULT 1
      '
  end
end

I get the error:

PG::Error: ERROR:  syntax error at or near "TYPE"
LINE 5:  TYPE int USING ("admin"::int)

Because the problem doesn't stay with the function, but rather on line 5, I think that the database just doesn't like line 5. Is there a limit on quote length that the PG is only parsing part of my execute command? I've tried MANY types of syntax, single/double quotes, %q/%Q blocks, even putting the whole quote one one line, but the syntax error still points to the earlier of TYPE and SET DEFAULT. Surely the problem isn't with ALTER What's going on here?

Any help is appreciated. Let me know if I missed something completely obvious. BTW some Gemfile:

source 'https://rubygems.org'
ruby '1.9.3'

gem 'rails', '~> 4.0.1.rc3'
gem 'bootstrap-sass', '2.3.2.0'
gem 'bcrypt-ruby', '~> 3.1.1'
gem 'faker', '1.1.2'
gem 'will_paginate', '3.0.4'
gem 'bootstrap-will_paginate', '0.0.9'
gem 'safe_attributes'

group :development, :test do
  gem 'pg', '0.15.1'
  gem 'sqlite3'
  gem 'rspec-rails', '2.13.1'
  gem 'guard-rspec', '2.5.0'
  gem 'spork-rails', github: 'sporkrb/spork-rails'
  gem 'guard-spork', '1.5.0'
  gem 'childprocess', '0.3.6'
end

And the database.yml for development environment:

development:
  adapter: postgresql
  encoding: utf8
  database: project_development
  pool: 5
  username: postgres

Upvotes: 0

Views: 594

Answers (2)

Marek Takac
Marek Takac

Reputation: 3038

I don't think your USING clause is right. This is from postgre docs (http://www.postgresql.org/docs/8.0/static/sql-altertable.html):

The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.

So USING is used to tell postgres how to compute new value types from old ones. I really don't understand what USING ("admin"::int) means in your query. Try to omit USING clause or try something like this:

USING CASE WHEN "admin" = TRUE THEN 1 ELSE 0 END

Upvotes: 0

Zac Butko
Zac Butko

Reputation: 19

I figured it out!

This quote from PostgreSQL website http://www.postgresql.org/docs/8.1/static/sql-altertable.html

Because of this flexibility, the USING expression is not applied to the column's default value (if any); the result might not be a constant expression as required for a default. This means that when there is no implicit or assignment cast from old to new type, ALTER TYPE may fail to convert the default even though a USING clause is supplied. In such cases, drop the default with DROP DEFAULT, perform the ALTER TYPE, and then use SET DEFAULT to add a suitable new default. Similar considerations apply to indexes and constraints involving the column.

If I first try to alter the type, it doesn't like the old default. If I first set a new default, it doesn't know how to type convert. This article says that the correct way to do my specific operation is to first drop the default, alter the type and then set a new default

Here's the code that worked for me.

class ChangeAdminToInt < ActiveRecord::Migration
  def up
    execute %q(
      ALTER TABLE users 
      ALTER COLUMN admin DROP DEFAULT,
      ALTER COLUMN admin TYPE int USING ("admin"::int),
      ALTER COLUMN admin SET DEFAULT 1;
      )
  end
end

And that's all!

Upvotes: 1

Related Questions