Postgres insert error in ActiveRecord

Question

For some reason, I cannot get ActiveRecord to correctly format the insert statement when using an array type column. It seems to want to escape the Postgres notation:

{"val1", "val2", "val3"} into \{\"val1\", \"val2\", \"val3\"\}

Resulting in an error:

PG::Error: ERROR: array value must start with "{" or dimension information

Am I running my db commands wrong? rake db:seed and bundle exec rake db:seed cause this error as well as running migrations.

I'm running Rails 3.2.13 and Postgres 9.3.1

Are you using the Rails way to serialize array attributes when saving arrays into db? — Jason Kim
– Jason Kim, Commented Nov 12, 2013 at 22:41
No, it's a list. i.e. Thing.create({title: 'xxx', slugs: ["x", "y" "z"]}) — James
– James, Commented Nov 12, 2013 at 22:44
Use Rails' serialize method.. Read up on "Saving arrays, hashes, and other non-mappable objects in text columns" — Jason Kim
– Jason Kim, Commented Nov 12, 2013 at 22:48
@dmtri.com he wants to use Postgres. James could you please provide more code/context? — RyanB
– RyanB, Commented Nov 12, 2013 at 23:04
@RyanB, using serialize method to store array should work for postgresql as well. — Jason Kim
– Jason Kim, Commented Nov 12, 2013 at 23:09

mu is too short · Accepted Answer · 2013-11-12 23:06:17Z

2

The Rails3 version of ActiveRecord doesn't understand PostgreSQL arrays natively so it is falling back to "I don't know what it is so I'll pretend it is a string" mode. If you install postgres_ext then you'll be able to use arrays properly:

Model.where(:some_array_column => [2, 3, 5, 6, 11]).to_sql
# SELECT "models".* FROM "models" WHERE "models"."some_array_column" = '{2,3,5,6,11}'

and inserting a %w[val1 val2 val3] array should work similarly.

answered Nov 12, 2013 at 23:06

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

1 Comment

poorva Over a year ago

In case someone needs to have array of string in the strong params all you have to do is include postgres_ext and the add in the list of params to permit :some_array_column => []

Collectives™ on Stack Overflow

Postgres insert error in ActiveRecord

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related