Rails migration to change column type from text to json (Postgresql)

Question

I've been trying unsuccessfully to change a column type in my Postgres database from text to json. Here's what I've tried...

class ChangeNotesTypeInPlaces < ActiveRecord::Migration[5.0]
  def up
    execute 'ALTER TABLE places ALTER COLUMN notes TYPE json USING (notes::json)'
  end

  def down
    execute 'ALTER TABLE places ALTER COLUMN notes TYPE text USING (notes::text)'
  end
end

Also...

class ChangeNotesTypeInPlaces < ActiveRecord::Migration[5.0]
  def up
    change_column :places, :notes, 'json USING CAST(notes AS json)'
  end

  def down
    change_column :places, :notes, 'text USING CAST(notes AS text)'
  end
end

Both of these return the same error...

PG::InvalidTextRepresentation: ERROR:  invalid input syntax for type json

Are the notes column already had a value that is not a valid json? — jamesjaya
– jamesjaya, Commented Oct 30, 2016 at 2:07
That's a good question. I've never saved data in this column (in development, not the same case in production unfortunately), but I also don't have a not-null constraint on that column. Could it be that it has been saving an empty string into that column which is causing this? If yes, do you know how I can get around this? — soultrust
– soultrust, Commented Oct 30, 2016 at 2:18
If you don't have the not-null constraint, then null value would be just fine or you could just save empty json {}. It is not accepting empty string. — jamesjaya
– jamesjaya, Commented Oct 30, 2016 at 2:25

jean-baptiste · Accepted Answer · 2018-02-18 17:52:28Z

13

Using Rails 5.1.x and PostgreSQL 9.4, here is what worked for me when converting text columns (containing valid json) to jsonb columns :

class ChangeTextColumnsToJson < ActiveRecord::Migration[5.1]
  def change
    change_column :table_name, :column_name, :jsonb, using: 'column_name::text::jsonb'
  end
end

answered Feb 18, 2018 at 17:52

jean-baptiste

7147 silver badges21 bronze badges

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

2 Comments

ipopa Over a year ago

This worked in postgres 9.6 rails 5.1, i had to add the reverse: def down change_column :table_name, :column_name, :text end

Carlos J García Over a year ago

this same solution works for AR::Migration[6.1], as always, make sure the data is valid, otherwise advise add column, copy data rename column

jsmartt · Accepted Answer · 2017-07-28 20:23:00Z

3

I was able to accomplish it using:

def change
  change_column :places, :notes, :json, using: 'notes::JSON'
end

This won't be reversible though; I imagine you can split it out into separate up and down definitions, but I didn't feel the need to.

answered Jul 28, 2017 at 20:23

jsmartt

1,4831 gold badge15 silver badges25 bronze badges

1 Comment

rj487 Over a year ago

Should separate up and down.

Eric Simonton · Accepted Answer · 2017-07-31 14:23:36Z

0

I just solved a similar problem. Trying to adapt it for your question, it would look (mostly?) like this.

class ChangeNotesTypeInPlaces < ActiveRecord::Migration[5.0]
  def up
    change_column :places, :notes, :jsonb, using: 'CAST(value AS JSON)'
  end

  def down
    change_column :places, :notes, :text
  end
end

answered Jul 31, 2017 at 14:23

Eric Simonton

6,0793 gold badges43 silver badges56 bronze badges

1 Comment

Eli R. Over a year ago

works for me if the word 'value' in the CAST is replaced with 'notes'

Collectives™ on Stack Overflow

Rails migration to change column type from text to json (Postgresql)

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related