How to I insert an array into a Postgresql table using ActiveRecord?

Question

I have active_record setup to use a Postgresql database. One of the columns is a character varying[] (basically a varchar array).

Anyway, my import routine then reads a tab-delimited text file and inserts records. All is fine until I get to an array. The column that should be converted to an array is comma separated. But the line itself is tab separated.

A sample of the data I'm importing looks like (tab delimited):

Col1      Col2     Col3                  Col4
----------------------------------------------
Apple     Pear     Sweet,Round,Green     Fruit

Col3 is imported like (ruby): col3.split(/,/) which gives me an array in Ruby. But active_record bombs out with:

PG::Error: ERROR: array value must start with "{" or dimension information (ActiveRecord::StatementInvalid)

How can I insert that column correctly?
Also, sometimes, col3 will be NULL.

If you really want native Pg arrays, look at ActiveRecordPostgresArray extension. — dbenhur
– dbenhur, Commented Mar 19, 2012 at 4:31

cbmeeks · Accepted Answer · 2012-04-16 15:22:30Z

2

I was able to insert using the following Ruby code:

alternatenames = '{' + s[3].split(/,/).map {|n| '"' + n + '"'}.join(",") + '}'

answered Apr 16, 2012 at 15:22

cbmeeks

11.4k22 gold badges91 silver badges143 bronze badges

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

2 Comments

cbmeeks Over a year ago

Wow. Got two downvotes for providing the answer to my own question that works just the way I wanted it to. Me. The person who asked. lol

Richard D Over a year ago

You probably got downvoted because the solution you came up with, while it worked, isn't as elegant as others would like. The fact they weren't able to come up with a better solution and share it with you, probably says more about them than the quality of your solution.

TalkativeTree · Accepted Answer · 2013-12-18 08:26:00Z

Check Postgres' docs on this: http://www.postgresql.org/docs/9.2/static/arrays.html

You can instantiate a model using an array like [:My, :symbols] or ["My", "Strings"], however it (in my experience and form what it seems in the docs) will save the elements as strings.

Search.create(tokens: [{hash: 'value'}, {test: "fails"}])
=> TypeError: can't cast Hash to string

Where as:

[15] pry(main)> Search.create(tokens: [:G, :F])
=> #<Search id: 78, tokens: [:G, :F], created_at: "2013-12-18 06:29:36", updated_at: "2013-12-18 06:29:36">
[16] pry(main)> Search.last
=> #<Search id: 78, tokens: ["G", "F"], created_at: "2013-12-18 06:29:36", updated_at: "2013-12-18 06:29:36">

In my tests, I have a SearchEngine, Search, and Term.

class SearchEngine < ActiveRecord::Base
  has_and_belongs_to_many :terms
  has_many :searches, through: :terms
end

class Term < ActiveRecord::Base
  has_and_belongs_to_many :searches
  has_and_belongs_to_many :searche_engines
end

class Search < ActiveRecord::Base
  has_many :rankings
  has_many :results, through: :rankings
  has_and_belongs_to_many :terms
  has_many :search_engines, through :terms
end


# These work:
# these next two are the way postgrespl says to query against the array. You get the 
Search.where(tokens: '{A,B}')
Search.where(tokens: '{C,D}').first_or_create

[3] pry(main)> Search.where(tokens: ['C','D']).first
ActiveRecord::StatementInvalid: PG::InvalidTextRepresentation: ERROR:  array value must start with "{" or dimension information

[4] pry(main)> Search.where(tokens: '{C,D}').first
=> #<Search id: 77, tokens: ["C", "D"], created_at: "2013-12-18 06:27:24", updated_at: "2013-12-18 06:27:24">

term = "accident"
Search.where("? = ANY (tokens)", term).first
=> #<Search id: 8, tokens: ["accident", "prevention", "safety"], created_at: "2013-12-18 07:48:13", updated_at: "2013-12-18 07:48:13">
Search.create(tokens: [:Aortic, :Any, :Other, :Elements])
Search.where("'Aortic' = ANY (tokens)").first
Parent.first.first_relationships.first.second_.where("'smelly' = ANY (tokens)").first
# The next one will create one with an empty array for tokens and push it into Term.searches anyway. Same thing with 'smelly'
Term.first.searches.where("':smelly' = ANY (tokens)").first_or_create do |s|   Term.first.searches << s 
end

# These error
Search.where(tokens: "Aortic").first
Search.where(tokens: [:Aortic, :Any, :Other, :Elements]).first

Also, if you have nested arrays, you could do a where search with this: '{{1,2,3},{4,5,6},{7,8,9}}' to find an row with column value [[1,2,3],[4,5,6],[7,8,9]]

Collectives™ on Stack Overflow

How to I insert an array into a Postgresql table using ActiveRecord?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related