Rails 迁移以将列类型从文本更改为 json (Postgresql) [英] Rails migration to change column type from text to json (Postgresql)

问题描述

我一直在尝试将 Postgres 数据库中的列类型从文本更改为 json，但未成功.这是我尝试过的...

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

还有...

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

推荐答案

使用 Rails 5.1.x 和 PostgreSQL 9.4，以下是将文本列(包含有效的 json)转换为 jsonb 列时对我有用的方法:

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

这篇关于Rails 迁移以将列类型从文本更改为 json (Postgresql)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！