在Rails<中为Postgres JSON列设置默认值. 4 [英] Set default value for Postgres JSON column in Rails < 4

问题描述

因此，我现在开始使用Postgres JSON数据类型，因为有很多乐趣可以做到的事情. 在我尚未使用Rails 4的Rails应用程序中(其中支持已添加Postgres JSON )我添加了这样的JSON列:

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:

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

但是我不知道如何为该列设置默认值. 我尝试了所有变体，例如{}，'{}'，'{}'::json，'[]'::json等.但是我要么在迁移运行时收到错误消息，要么根本无法正常工作，这意味着迁移在运行，但是当我创建一个新的Foo，bar是nil.

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.

推荐答案

虽然有点晚，但对我来说却很有效(需要Postgres> = 9.3):

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

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

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

编辑:该答案用于倡导使用to_json('[]'::text)而不是'[]'::JSON-感谢

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

旧方法的问题在于，它实际上并没有像预期的那样将数组或对象定义为默认值，而是一个看起来像 scalar (字符串)的默认值.为什么这么重要?

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允许将三种类型的值插入JSON列:

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

1. 对象

1. Objects

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

数组

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

标量

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

问题在于，如果在同一列中混合使用这三种，则会失去使用JSON运算符的能力.如果使用先前提倡的方法将默认值设置为[[]"并查询数组元素，则遇到具有标量默认值的单行将中止整个查询，并出现错误:

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:

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

这篇关于在Rails<中为Postgres JSON列设置默认值. 4的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！