Wednesday, March 03, 2010

How to alter columns in postgresql

So here's a goodie for those of you using postgresql.

Let's say you have a migration to do where you need to change a column from an integer to a boolean. So if you're doing a rails migration, you might write something like

And then postgresql throws up (well, it does on Rails 2.3.5):

== ChangeDatatableAttributes: migrating ======================================
-- change_column(:datatables, :is_numeric, :boolean)
rake aborted!
An error has occurred, this and all later migrations canceled:

PGError: ERROR: column "is_numeric" cannot be cast to type boolean
: ALTER TABLE "datatables" ALTER COLUMN "is_numeric" TYPE boolean

Well, that's no good. The web was not exactly much help here, but I managed to find this in the postgresql docs:

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 might fail to convert the default even though a USING clause is supplied.

Looks like you need to drop the default also, and then set it afterwards.

Or if you'd like, create a method that you can throw into a module to mixin.

And use it like so:

Or if all else fails, you can just follow Heroku's advice and just drop the column and create another. Tip!

Posted via web from The Web and all that Jazz

3 comments:

  1. There is one simple solution to alter table

    1. For changing type of column:
    class AlterUserColumn < ActiveRecord::Migration
    def self.up
    change_column :users, :admin, :integer, :default=>nil
    end

    def self.down

    end
    end

    2.To Alter whole table structure :
    class AlterUserColumn < ActiveRecord::Migration

    def self.up
    execute 'SIMPLE mysql alter query'
    end

    def self.down
    execute 'opposite query to above written query'
    end
    end

    ReplyDelete
  2. Great post! Thanks for the help.

    ReplyDelete
  3. To check for Postgres adapter you may do that:

    > ActiveRecord::Base.retrieve_connection.kind_of?(ActiveRecord::ConnectionAdapters::PostgreSQLAdapter)
    => true

    ReplyDelete