11

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
soultrust
  • 581
  • 1
  • 7
  • 17
  • Are the `notes` column already had a value that is not a valid json? – jamesjaya Oct 30 '16 at 02: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 Oct 30 '16 at 02:18
  • 1
    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 Oct 30 '16 at 02:25

3 Answers3

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
jean-baptiste
  • 674
  • 7
  • 18
  • 1
    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 – ipopa Mar 07 '18 at 10:04
  • 1
    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 – Carlos J García Jan 27 '23 at 17:29
2

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.

jsmartt
  • 1,404
  • 1
  • 15
  • 22
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
Eric Simonton
  • 5,702
  • 2
  • 37
  • 54