CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordattributes
banditKing
banditKing

Reputation: 9579

Changing the data type of an entire column in a PostgreSQL DB in Rails 3.2.8

I have a DB table for my Rails 3.2.8 app as a PostgreSQL database. In this DB I have a column of type boolean. The column has values filled in. I want to change the values from boolean to all strings.

I found out that I can run a migration to change the type of the column. However, I don't want to do this as yet as I'm not sure what happens to the values when I run such a migration. So would the boolean value false get changed to "False" and a boolean true to "True"?

If not then what is the best way of accomplishing this task?

Upvotes: 1

Views: 858

Answers (2)

mu is too short
mu is too short

Reputation: 434615

You'll probably get 'true' and 'false':

psql> create table t (c boolean not null);
psql> insert into t (c) values (TRUE), (FALSE);
psql> alter table t alter column c type text;
psql> select * from t;
   c   
-------
 true
 false

I'm not sure if that's guaranteed and I can't find an authoritative reference for what a boolean to text cast does so you might want to check what these have to say in your version of PostgreSQL:

psql> select TRUE::text;
psql> select FALSE::text;

If you want to guarantee 'True' and 'False' then you can use USING to manually specify the conversion:

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.

So you could say this to force the issue:

alter table t
alter column c type text
using case c when TRUE then 'True' when FALSE then 'False' else NULL end

If you let Rails do the conversion with a standard migration then you'll get the default boolean-to-text casts, if you do the type change by hand with an SQL ALTER TABLE then you can control what happens. You can do either one inside a migration:

def up
    change_column :t, :c, :text
end

versus

def up
    connection.execute(%q{
        alter table ...
    })
end

As an aside, I'd have to question the logic of abandoning boolean columns for text columns, seems a bit backwards to me.

Upvotes: 3

Trent Earl
Trent Earl

Reputation: 3607

You should be fine with:

change_column :table_name, :column_name, :string

I just just tried it on 9.1. Just back it up pg_dump test_db > test.sql

You can simulate the sql on a test table to see how your version of Postgresql will react, rails should generate something like

ALTER TABLE my_table ALTER COLUMN col TYPE varchar(128);

Upvotes: 0

Related Questions