rails 4.2 : nil is inserted when trying to storing array data with PostgreSQL

Question

I have a problem with storing array data into one field with PostgreSQL.

here is my environment

Rails version             4.2.0
Ruby version              2.2.0-p0 (x86_64-darwin14)
RubyGems version          2.4.5
Rack version              1.5

and my migration is

class CreateStartups < ActiveRecord::Migration
  def change
    create_table :startups do |t|
      t.string :name, null: false
      t.string :email
      t.boolean :is_email_verified, null: false, default: false
      t.decimal :location_lat, precision: 10, scale: 6
      t.decimal :location_lng, precision: 10, scale: 6
      t.text :completeness, array: true, default: []
      t.datetime :deleted_at

      t.timestamps null: false
    end
  end
end

and model is

class Startup < ActiveRecord::Base
  serialize :completeness, Array
  validates :name, presence: true
  validates :slug, presence: true, allow_blank: false, length: { maximum: 200 }
  validates_uniqueness_of :slug
  translates :name, :name_ruby, :company_name, :concept, :about_title, :about_detail, :address
end

and what I tried to do was in rails console

    [6] pry(main)> Startup.create!(name: 'test', slug: 'test3', completeness: [100, ["test", "test2"]])
   (0.1ms)  SAVEPOINT active_record_1
  Startup Exists (0.3ms)  SELECT  1 AS one FROM "startups" WHERE "startups"."slug" = 'test3' LIMIT 1
  SQL (0.3ms)  INSERT INTO "startups" ("completeness", "slug", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["completeness", nil], ["slug", "test3"], ["created_at", "2015-02-19 10:17:23.355815"], ["updated_at", "2015-02-19 10:17:23.355815"]]
  SQL (0.2ms)  INSERT INTO "startup_translations" ("locale", "startup_id", "name", "created_at", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id"  [["locale", "ja"], ["startup_id", 4], ["name", "test"], ["created_at", "2015-02-19 10:17:23.364581"], ["updated_at", "2015-02-19 10:17:23.364581"]]
   (0.1ms)  RELEASE SAVEPOINT active_record_1
=> #<Startup:0x007fd840cc1648
 id: 4,
 author_id: nil,
 slug: "test3",
 email: nil,
 location_lat: nil,
 location_lng: nil,
 completeness: [],
 deleted_at: nil,
 created_at: Thu, 19 Feb 2015 10:17:23 UTC +00:00,
 updated_at: Thu, 19 Feb 2015 10:17:23 UTC +00:00>

And what i see is completeness: [] is inserted because generated SQL was

INSERT INTO "startups" ("completeness", "slug", "created_at", "updated_at") VALUES ($1, $2, $3, $4) RETURNING "id"  [["completeness", nil], ["slug", "test3"], ["created_at", "2015-02-19 10:17:23.355815"], ["updated_at", "2015-02-19 10:17:23.355815"]]

and completeness is nil even though I passed [100, ["test", "test2"]

could anyone help me out on this??

best,

Can you complete the array [100, ["test", "test2"]] and try.. — Arup Rakshit
– Arup Rakshit, Commented Feb 19, 2015 at 10:32
What is the type of column completeness in your schema.rb? I see you used the text type in your migration, perhaps this should be changed to type hstore. — zwippie
– zwippie, Commented Feb 19, 2015 at 10:33
@zwippie Yes, it is. I'm also currently working on similar kind of issue.. Need to dig into it more. — Arup Rakshit
– Arup Rakshit, Commented Feb 19, 2015 at 10:43
You can change the field type to a text column only, not an array. And serialize your array and save it to the column. — Arup Rakshit
– Arup Rakshit, Commented Feb 19, 2015 at 11:26

intale · Accepted Answer · 2015-02-19 11:03:47Z

1

@Yoshi, you are trying to store malformed array. Acceptable variants:

[[100], ["test", "test2"]]
[100, "test", "test2"]

Not acceptable variants:

[100, ["test", "test2"]]
[100, "test", ["test2"]]

Another words - array can only be or single-dimensional, or multidimensional. See more at http://www.postgresql.org/docs/9.1/static/arrays.html

answered Feb 19, 2015 at 11:03

intale

8316 silver badges8 bronze badges

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

4 Comments

Yoshi Over a year ago

thank you for your answer. however, i still have the same problem with single-dimension array insertion. @intale

intale Over a year ago

@Yoshi, can you provide a version of your pg gem and a version your postgreSQL?

intale Over a year ago

Hmm... Wait. Remove this line: serialize :completeness, Array and it should work right after. Or, otherwise, remove :array => true, :default => [] from the column's options in the migration(and run migration again).

Yoshi Over a year ago

@intale Yes, your last suggestion worked right after. thx a lot ^^

Collectives™ on Stack Overflow

rails 4.2 : nil is inserted when trying to storing array data with PostgreSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related