what is the migration to convert a column template_id of data_type array of string to integer?
Example:
A row has template_id with value ["2"]. I want it to be 2.
All value of template_id has one element in array.
2 Answers
I'll recommend the following approach to do this
- Create a new field
temp_template_idwith datatypeint - Insert all the values of the
template_idto thetemp_template_id - Remove column
template_id - Rename column
temp_template_idtotemplate_id
a. rails g migration AddTempTemplateDataToTableName
def change
add_column :table_name, :temp_template_id, :integer
end
b. rails g migration RenameColumnTemptemplateID
def self.up
# Script to fill column `temp_template_id` from 'template_id'
remove_column :table_name, :template_id
rename_column :table_name, :temp_template_id, :template_id
end
def self.down
rename_column :table_name, :template_id, :temp_template_id
add_column :table_name, :template_id, :array, :default => []
# Script to fill column `template_id` from 'temp_template_id'
end
Comments
the SQL migration would be as easy as using n[1]:
t=# create table t3(n int[]);
CREATE TABLE
t=# insert into t3 select array[2];
INSERT 0 1
t=# select * from t3;
n
-----
{2}
(1 row)
t=# alter table t3 alter COLUMN n type int using n[1];
ALTER TABLE
t=# select * from t3;
n
---
2
(1 row)
and in case you had array not as int[], you will need double cast to avoiod
ERROR: result of USING clause for column "template_id" cannot be cast automatically to type integer HINT: You might need to add an explicit cast.
alter table t3 alter COLUMN n type int using n[1]::text::int;
should do the trick, here's example:
t=# create table t3(n text[]);
CREATE TABLE
t=# insert into t3 select array[2];
INSERT 0 1
t=# select * from t3;
n
-----
{2}
(1 row)
t=# alter table t3 alter COLUMN n type int using n[1]::text::int;
ALTER TABLE
t=# select * from t3;
n
---
2
(1 row)
2 Comments
rohan kharvi
ERROR: result of USING clause for column "template_id" cannot be cast automatically to type integer HINT: You might need to add an explicit cast. Here template_id is 'n'
Vao Tsun
try adding
::text::int as in my asnwer