Set default value for Postgres JSON column in Rails < 4

Manuel Meurer picture Manuel Meurer · Oct 31, 2013 · Viewed 18.4k times · Source

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

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.

Answer

janfoeh picture janfoeh · Dec 23, 2013

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"

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

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:

  1. Objects

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

  2. Arrays

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

  3. Scalars

    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:

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