在Rails

So I'm starting to use the Postgres JSON datatype, now that there's a lot of fun stuff you can do with it. In one of my Rails apps which is not yet Rails 4 (where support for Postgres JSON has been added) I added a JSON column like this:

所以我开始使用Postgres JSON数据类型，现在你可以用它做很多有趣的事情。在我的一个Rails应用程序中还没有Rails 4（已经添加了对Postgres JSON的支持），我添加了一个这样的JSON列：

create_table :foo do |t|
  t.column :bar, :json
end

but I can't figure out how to set a default value for the column. I tried all variations like {}, '{}', '{}'::json, '[]'::json etc. but I either get an error when the migration runs or it simply doesn't work, meaning the migration runs but, when I create a new Foo, bar is nil.

但我无法弄清楚如何为列设置默认值。我尝试了所有变体，如{}，'{}'，'{}':: json，'[]':: json等。但我在迁移运行时遇到错误或者它根本不起作用，这意味着迁移运行但是，当我创建一个新的Foo时，bar是零。

2 个解决方案

#1

Although a bit late, this worked for me (requires Postgres >= 9.3):

虽然有点晚，但这对我有用（需要Postgres> = 9.3）：

create_table :foo do |t|
  t.column :bar, :json
end

execute "ALTER TABLE foo ALTER COLUMN bar SET DEFAULT '[]'::JSON"

EDIT: this answer used to advocate for to_json('[]'::text) instead of '[]'::JSON - thanks to @Offirmo for the hint.

编辑：这个答案用于提倡to_json（'[]':: text）而不是'[]':: JSON - 感谢@Offirmo的提示。

The problem with the old method was that it didn't actually define an array or an object as the default value as one would expect, but a scalar (string) that looked like one. Why does that matter?

旧方法的问题在于它实际上没有将数组或对象定义为人们期望的默认值，而是一个看起来像一个的标量（字符串）。为什么这么重要？

Postgres allows three kinds of values to be inserted into JSON columns:

Postgres允许将三种值插入JSON列：

Objects

对象

INSERT INTO foo (bar) VALUE('{}')

INSERT INTO foo（bar）VALUE（'{}'）
Arrays

数组

INSERT INTO foo (bar) VALUE('[]')

INSERT INTO foo（bar）VALUE（'[]'）
Scalars

标量

INSERT INTO foo (bar) VALUE('"string"')

INSERT INTO foo（bar）VALUE（'“string”'）

The problem is that if you mix these three kinds in the same column, you lose the ability to use the JSON operators. If you set a default of '[]' using the previously advocated method and queried for an array element, encountering a single row with a scalar default value would abort the whole query with an error:

问题是如果在同一列中混合使用这三种类型，则会失去使用JSON运算符的能力。如果使用先前提倡的方法设置默认值“[]”并查询数组元素，则遇到具有标量默认值的单行将中止整个查询并显示错误：

=# SELECT * FROM foo WHERE bar->>1 = 'baz';
ERROR:  cannot extract element from a scalar

#2

Code below works for PostgreSQL 9.3.4 and Rails 3.2.17

下面的代码适用于PostgreSQL 9.3.4和Rails 3.2.17

class YourModel < ActiveRecord::Base
...
  serialize :your_column, JSON
  before_create do
    self.your_column ||= {}
  end
...
end

migration code

迁移代码

add_column :your_table, :your_column, :json
execute "ALTER TABLE your_table ALTER COLUMN your_column SET DEFAULT '{}'"
execute "UPDATE your_table SET your_column = '{}';"

application.rb

application.rb中

config.active_record.schema_format = :sql

#1