Set default value for Postgres JSON column in Rails < 4

Question

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.

Have you tried setting the default value by hand inside the model? AR generally butchers or ignores defaults that it doesn't understand. — mu is too short, Oct 31 '13 at 19:58
Yeah, right now I use a `after_initialize` callback but I generally don't like those... — Manuel Meurer, Oct 31 '13 at 20:06
try `""`, I believe that is what is used for hstore to denote an empty hash — John Bachir, Dec 11 '13 at 01:02
```-- add_column(:foo, :bar, :json, {:default=>""}) rake aborted! An error has occurred, this and all later migrations canceled: PG::InvalidTextRepresentation: ERROR: invalid input syntax for type json DETAIL: The input string ended unexpectedly. CONTEXT: JSON data, line 1: : ALTER TABLE "foo" ADD COLUMN "bar" json DEFAULT '' ``` — Manuel Meurer, Dec 12 '13 at 10:27
Well, it's `null` by default... by default. I don't want it to be `null`, I want it to be `{}`. — Manuel Meurer, Dec 30 '13 at 09:01

score 31 · Accepted Answer · edited May 23 '17 at 10:29

31

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:

Objects

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

INSERT INTO foo (bar) VALUE('[]')
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

edited May 23 '17 at 10:29

Community

1
1

answered Dec 23 '13 at 15:13

janfoeh

10,243
2
31
56

1

I get the following error: `PG::UndefinedFunction: ERROR: function to_json(text) does not exist HINT: No function matches the given name and argument types. You might need to add explicit type casts.` – Manuel Meurer Dec 29 '13 at 09:10
@ManuelMeurer I believe you need a recent version of Postgres for that; I am on 9.3.2. – janfoeh Dec 29 '13 at 10:07
1

Yeah it was added in 9.3. Could you mention that in your answer? Then I can accept it. – Manuel Meurer Dec 29 '13 at 14:33
One more tiny thing, can you change the `>` to `>=`? Cheers! – Manuel Meurer Dec 30 '13 at 21:43
4

This syntax also works : `SET DEFAULT '{}'::JSON;` I find it cleaner. – Offirmo Jun 10 '14 at 13:08
@Offirmo thank you! Your answer is the one that _actually_ works - mine was subtly broken. – janfoeh Sep 17 '14 at 11:22
@ManuelMeurer if you are still working with the JSON datatype, you might be interested in the updated answer. – janfoeh Sep 17 '14 at 11:23

score 5 · Answer 2 · answered Apr 13 '14 at 21:11

Code below works for PostgreSQL 9.3.4 and 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

config.active_record.schema_format = :sql

Olalekan Sogunle · Answer 3 · 2020-05-07T10:21:09.640

0

You can basically just do something like this

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

State the default with default: [] or default: {} or any other thing you feel comfortable with.

Update: Note that this is for Rails 4+

edited May 07 '20 at 10:21

answered May 06 '20 at 20:54

Olalekan Sogunle

2,299
1
20
26

As mentioned in my question 6.5 years ago, the app was still on Rails 3, so this won't (didn't) work. Could you improve your answer by mentioning from which Rails version on your suggestion works? – Manuel Meurer May 07 '20 at 10:00

Set default value for Postgres JSON column in Rails < 4

3 Answers3

Linked