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

1
2
3
4
5
6
7
8
class ChangeDatatableAttributes < ActiveRecord::Migration
  def self.up
    change_column :datatables, :is_numeric, :boolean
  end
  def self.down
    raise ActiveRecord::IrreversibleMigration.new
  end
end

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
-- change a column named "is_numeric" from integer to boolean
ALTER TABLE sometable ALTER COLUMN is_numeric DROP DEFAULT;
ALTER TABLE sometable ALTER COLUMN is_numeric TYPE boolean
  USING CASE is_numeric
    WHEN '1' THEN true
    ELSE 0 END;
ALTER TABLE sometable ALTER COLUMN is_numeric SET DEFAULT true;

-- change a column named "multiplier" from string to integer
ALTER TABLE sometable ALTER COLUMN multiplier DROP DEFAULT;
ALTER TABLE sometable ALTER COLUMN multiplier TYPE integer
  USING CAST(multiplier as INTEGER);
ALTER TABLE sometable ALTER COLUMN multiplier SET DEFAULT 1;

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
# used to alter columns in postgresql
module AlterColumn
  def alter_column(table_name, column_name, new_type, mapping, default = nil)
    drop_default = %Q{ALTER TABLE #{table_name} ALTER COLUMN #{column_name} DROP DEFAULT;}
    execute(drop_default)
    # puts drop_default

    base = %Q{ALTER TABLE #{table_name} ALTER COLUMN #{column_name} TYPE #{new_type} }
    if mapping.kind_of?(Hash)
      contains_else = mapping.has_key?("else")
      else_mapping = mapping.delete("else")
      when_mapping = mapping.map { |k, v| "when '#{k}' then #{v}" }.join("n")
      
      base += %Q{ USING CASE #{column_name} #{when_mapping} } unless when_mapping.blank?
      base += %Q{ ELSE #{else_mapping} } unless contains_else.blank?
      base += %Q{ END } if !when_mapping.blank? or !contains_else.blank?
    elsif mapping.kind_of?(String)
      base += mapping
    end
    base += ";"
    
    execute(base);
    # puts base
    
    unless default.blank?
      set_default = %Q{ALTER TABLE #{table_name} ALTER COLUMN #{column_name} SET DEFAULT #{default};}
      execute(set_default)
      # puts set_default
    end
  end
  module_function :alter_column
end

And use it like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
class ChangeTableAttributes < ActiveRecord::Migration
  class << self
    include AlterColumn
  end
 
  def self.up
    alter_column :sometables, :is_numeric, :boolean, { "1" => true, "else" => false }, true
    alter_column :sometables, :multiplier, :integer, "USING CAST(multiplier AS integer)", 1
  end

  def self.down
    raise ActiveRecord::IrreversibleMigration.new
  end
end

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s