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
– mu is too short, Commented Oct 31, 2013 at 19:58
Yeah, right now I use a after_initialize callback but I generally don't like those... — Manuel Meurer
– Manuel Meurer, Commented Oct 31, 2013 at 20:06
try "", I believe that is what is used for hstore to denote an empty hash — John Bachir
– John Bachir, Commented Dec 11, 2013 at 1: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
– Manuel Meurer, Commented Dec 12, 2013 at 10:27

Community · Accepted Answer · 2017-05-23 10:29:27Z

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, 2017 at 10:29

CommunityBot

11 silver badge

answered Dec 23, 2013 at 15:13

janfoeh

10.3k3 gold badges33 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Manuel Meurer Over a year ago

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.

janfoeh Over a year ago

@ManuelMeurer I believe you need a recent version of Postgres for that; I am on 9.3.2.

Manuel Meurer Over a year ago

Yeah it was added in 9.3. Could you mention that in your answer? Then I can accept it.

Manuel Meurer Over a year ago

One more tiny thing, can you change the > to >=? Cheers!

Offirmo Over a year ago

This syntax also works : SET DEFAULT '{}'::JSON; I find it cleaner.

|

Anton Orel · Accepted Answer · 2014-04-13 21:11:40Z

5

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

answered Apr 13, 2014 at 21:11

Anton Orel

5767 silver badges8 bronze badges

Comments

Olalekan Sogunle · Accepted Answer · 2020-05-07 10:21:09Z

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 7, 2020 at 10:21

answered May 6, 2020 at 20:54

Olalekan Sogunle

2,3671 gold badge24 silver badges27 bronze badges

1 Comment

Manuel Meurer Over a year ago

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?

Collectives™ on Stack Overflow

Set default value for Postgres JSON column in Rails < 4

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related