Rails Migration changing column to use Postgres arrays

Question

I am trying to change a column in my database so that it can use the Postgres array data type. Currently the table column is of type string.

I am using the following migration to convert it:

def change
  change_column :table, :dummy_column, :text, array: true, default: []
end

But I get the following error:

bundle exec rake db:migrate
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  column "dummy_column" cannot be cast automatically to type     character varying[]
HINT:  Specify a USING expression to perform the conversion.
: ALTER TABLE "table" ALTER COLUMN "dummy_column" TYPE character varying(255) 
Tasks: TOP => db:migrate

you can use change_column :table, :dummy_column, :string, array: true, default: [] instead of text it may resolve your problem — Veeru
– Veeru, Commented Mar 21, 2014 at 4:29

Craig Ringer · Accepted Answer · 2014-03-21 05:37:28Z

37

PostgreSQL doesn't know how to automatically convert a column of varchar into an array of varchar. It doesn't know what you might intend, because it has no way to know what format you think the current values are in.

So you need to tell it; that's what the USING clause is for.

ActiveRecord doesn't seem to explicitly support the USING clause (not surprising, as it barely supports even the most basic database features). You can specify your own SQL text for the migration, though.

Assuming your strings are comma separated and may not themselves contain commas, for example:

def change
  change_column :table, :dummy_column, "varchar[] USING (string_to_array(dummy_column, ','))"
end

(I don't use Rails myself and haven't tested this, but it's consistent with the syntax used in examples elsewhere).

edited Mar 21, 2014 at 5:37

answered Mar 21, 2014 at 4:53

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

lrrthomas · Accepted Answer · 2016-02-01 19:02:59Z

22

Using Rails 4.2 on postgresql 9.4 I was looking to do this and preserve my pre-existing string data as the first element in one element arrays.

It turns out that postgresql cannot coerce a string into a text array without a USING expression to tell it how.

After much fiddling with delicate postgres syntax, I found a good middle way with active record:

def change
  change_column :users, :event_location, :text, array: true, default: [], using: "(string_to_array(event_location, ','))"
end

The only direct postgresql there is the (string_to_array() ) function call. Here are the docs on that--note that you have to supply a delimiter.

answered Feb 1, 2016 at 19:02

lrrthomas

2292 silver badges3 bronze badges

Comments

tolbard · Accepted Answer · 2017-03-15 00:25:51Z

5

Using Rails 4.2 on postgresql 9.4 with a down and a up, base on lrrthomas response. Note: your starting column should have a default of nil

class ChangeEmailAndNumberColumnForContact < ActiveRecord::Migration
  def up
    change_column :contacts, :mobile_number, :text, array: true, default: [], using: "(string_to_array(mobile_number, ','))"
    change_column :contacts, :email, :text, array: true, default: [], using: "(string_to_array(email, ','))"
  end

  def down
    change_column :contacts, :mobile_number, :text, array: false, default: nil, using: "(array_to_string(mobile_number, ','))"
    change_column :contacts, :email, :text, array: false, default: nil, using: "(array_to_string(email, ','))"
  end
end

edited Mar 15, 2017 at 0:25

answered Mar 14, 2017 at 23:54

tolbard

1,27315 silver badges20 bronze badges

Comments

Craig Ringer · Accepted Answer · 2014-03-21 04:54:04Z

3

def change

    change_column :table, :dummy_column, :string, array: true, default: '{}'

end

Notice:

it's specified as data type :string with array: true to default the column to an empty array ( [] ), you use default: '{}'

edited Mar 21, 2014 at 4:54

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

answered Mar 21, 2014 at 4:33

Veeru

3564 silver badges13 bronze badges

3 Comments

rii Over a year ago

This does not work for me, I actually tried it before. I get the same error.

Veeru Over a year ago

@ril Sorry I am not sure why you are getting same problem, but I have tried in my local system it was worked

SureshCS Over a year ago

May be the rails version - Is this supported only in Rails 4?

Felipe Cabargas · Accepted Answer · 2020-03-05 11:36:20Z

2

It can be done like below:

change_column :table, :column, :string, array: true, default: {}, using: "(string_to_array(column, ','))"

edited Mar 5, 2020 at 11:36

Felipe Cabargas

9867 silver badges10 bronze badges

answered Feb 13, 2020 at 16:12

Lakshya Garg

212 bronze badges

1 Comment

dr_ermio Over a year ago

Thanks for your answer. To improve readability, please format your block code appropriately.

Aakanksha Jaiswal · Accepted Answer · 2023-02-16 08:25:04Z

1

add_column :table, :dummy_column, :string, array: true
change_column_default :table, :dummy_column, []

This fixed it for me.

edited Feb 16, 2023 at 8:25

answered Feb 16, 2023 at 8:24

Aakanksha Jaiswal

112 bronze badges

1 Comment

ltrainpr Over a year ago

I can confirm this works.

Collectives™ on Stack Overflow

Rails Migration changing column to use Postgres arrays

6 Answers 6

Comments

Comments

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related