CODEWITHSUNDEEP
ruby-on-railspostgresqlmigration
rohan kharvi
rohan kharvi

Reputation: 118

Migration to convert array of string to integer

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.

Upvotes: 0

Views: 655

Answers (2)

Salil
Salil

Reputation: 47472

I'll recommend the following approach to do this

  1. Create a new field temp_template_id with datatype int
  2. Insert all the values of the template_id to the temp_template_id
  3. Remove column template_id
  4. Rename column temp_template_id to template_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

Upvotes: 2

Vao Tsun
Vao Tsun

Reputation: 51446

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)

Upvotes: 1

Related Questions