Set the default value for the Postgres JSON column in Rails <4

Question

So, I'm starting to use the Postgres JSON data type, now there is a lot of interesting things that you can do with it . In one of my Rails applications, not yet Rails 4 (where Postgres JSON support was added ) I added a JSON column as follows:

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

but I cannot figure out how to set the default value for a column. I've tried all the options, such as {}, '{}', '{}'::json, '[]'::jsonand so on, but I either get an error message when you run the migration, or simply does not work, which means that you are migrating, but when I create a new Foo, barequal nil.

Answer 1

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

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

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

EDIT: this answer was used for to_json('[]'::text)instead of '[]'::JSON- @Offirmo for a hint.

The problem with the old method was that it did not actually define the array or object as the default, as you might expect, but a scalar (string) that looked like one. Why does it matter?

Postgres allows you to insert three types of values ​​in JSON columns:

• The objects

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

• Arrays

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

• Scalars

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

, , JSON-. "[]" , :

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

Answer 2

PostgreSQL 9.3.4 Rails 3.2.17

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

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