Inserting an array using Sequel gem in PostgreSQL

Question

i created a table with the following schema in code

DB.create_table :Pokemon do
  primary_key :id
  String :first_name
  String :last_name
  String :email
  String :zipcode
  String :company_name
  String :google_profile
  String :skype
  String :phone
  String :about
  String :linkedin_profile_url
  String :company_url
  column :needs , 'Text[]'
  column :offering , 'Text[]'
end

for needs and offering i am inserting a string array with following code

pokes=DB[:Pokemon];
off=['hello1' , 'hello2']
nee= ['need1' , 'need2']
pokes.insert(:first_name => 'abcd' ,:last_name => 'mehandiratta', :offering => off , :needs =>  nee)

when i run it i am getting an error

PG::Error: ERROR:  column "offering" is of type text[] but expression is of type record (Sequel::DatabaseError)
LINE 1: ...fering", "needs") VALUES ('abcd', 'mehandiratta', ('hello1',...
                                                             ^
HINT:  You will need to rewrite or cast the expression.
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:145:in `execute_query'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/database/logging.rb:33:in `log_yield'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:145:in `execute_query'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:132:in `execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:111:in `check_disconnect_errors'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:132:in `execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:413:in `_execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:242:in `execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:425:in `check_database_errors'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:242:in `execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/database/connecting.rb:236:in `synchronize'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/connection_pool/threaded.rb:104:in `hold'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/database/connecting.rb:236:in `synchronize'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:242:in `execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/dataset/actions.rb:801:in `execute'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/postgres.rb:525:in `fetch_rows'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/dataset/actions.rb:860:in `returning_fetch_rows'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/dataset/actions.rb:341:in `insert'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/shared/postgres.rb:1060:in `insert'
    from /var/lib/gems/1.8/gems/sequel-3.43.0/lib/sequel/adapters/shared/postgres.rb:1069:in `insert'
    from hello.rb:10

can any one please tell a suitable way how to insert array in Sequel gem and how to add Text array in a column

ironsand · Accepted Answer · 2023-03-22 04:35:36Z

6

You need to use Sequel's pg_array extension, see https://sequel.jeremyevans.net/rdoc-plugins/files/lib/sequel/extensions/pg_array_rb.html

edited Mar 22, 2023 at 4:35

ironsand

15.4k18 gold badges93 silver badges188 bronze badges

answered Jan 31, 2013 at 16:53

Jeremy Evans

12.2k31 silver badges27 bronze badges

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

1 Comment

Denis de Bernardy Over a year ago

The change log of your sequel_pg gem suggest there's a C parser for pg arrays which was committed two years ago, so shortly after you answered this. Can you confirm that this answer is still correct and that pg_array?

MatzFan · Accepted Answer · 2018-08-09 22:02:51Z

0

In your database config:

DB.extension :pg_array

Then just convert each array to type PGArray:

pokes.insert(:first_name => 'abcd' ,:last_name => 'mehandiratta', :offering => Sequel.pg_array(off) , :needs => Sequel.pg_array(nee))

@Jeremy Evans' answer provides a link to the docs which include a more succinct way to do it if using the core-extensions extension.

answered Aug 9, 2018 at 22:02

MatzFan

9669 silver badges20 bronze badges

Comments

Jasdeep Singh · Accepted Answer · 2013-01-31 16:11:40Z

-1

As the error says, just cast the Array expression to a string, like so:

pokes.insert(:first_name => 'abcd' ,:last_name => 'mehandiratta', :offering => off.to_s , :needs =>  nee.to_s)

answered Jan 31, 2013 at 16:11

Jasdeep Singh

3,3364 gold badges32 silver badges47 bronze badges

Collectives™ on Stack Overflow

Inserting an array using Sequel gem in PostgreSQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related