Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Set default value for Postgres JSON column in Rails < 4

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.

like image 376
Manuel Meurer Avatar asked Oct 31 '13 19:10

Manuel Meurer


People also ask

How do I change the default value of a column in PostgreSQL?

Changing a Column's Default Value. To set a new default for a column, use a command like: ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77; Note that this doesn't affect any existing rows in the table, it just changes the default for future INSERT commands.

Is Postgres Jsonb fast?

Because JSONB stores data in a binary format, queries process significantly faster. Storing data in binary form allows Postgres to access a particular JSON key-value pair without reading the entire JSON record. The reduced disk load speeds up overall performance. Support for indexing.

How is JSON stored in Postgres?

PostgreSQL offers two types for storing JSON data: json and jsonb . To implement efficient query mechanisms for these data types, PostgreSQL also provides the jsonpath data type described in Section 8.14. 7. The json and jsonb data types accept almost identical sets of values as input.


2 Answers

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
like image 60
janfoeh Avatar answered Sep 22 '22 07:09

janfoeh


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
like image 40
Anton Orel Avatar answered Sep 23 '22 07:09

Anton Orel