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

	class ChangeDatatableAttributes < ActiveRecord::Migration
	def self.up
	change_column :datatables, :is_numeric, :boolean
	end
	def self.down
	raise ActiveRecord::IrreversibleMigration.new
	end
	end

view raw change_column_migration.rb hosted with ❤ by GitHub

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.

via postgresql.org

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

	-- 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;

view raw alter_column.sql hosted with ❤ by GitHub

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

	# 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

view raw alter_column.rb hosted with ❤ by GitHub

And use it like so:

	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

view raw 20100304010357_change_datatable_attributes.rb hosted with ❤ by GitHub

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