Search code examples
rubypostgresqlsequel

How can I retrieve an array stored in postgresql via Sequel as an array?

I have a ruby script, a postgres database and I use the Sequel gem to connect to the database.

I want to store and retrieve records, where some of the column values are arrays (set to text[] as data type). I therefore load the pg_array extension for sequel.

I create a hash {id: 1, key1: Sequel.pg_array(["lorem ipsum", "caesar", "quidquid id est, timeo danaos"])}

I can insert the hash through table.insert hash and it creates the correct looking record.

 id |                          key1                          
----+--------------------------------------------------------
 1  | {"lorem ipsum",caesar,"quidquid id est, timeo danaos"}

Based on this favourable result I deduce that the database, the sequel gem and the pg_array extension are all set up correctly.

However, when I retrieve the record via sequel again with advisers.where(id: 1).all I get this

[{:id => 1, :key1 => "{\"lorem ipsum\",caesar,\"quidquid id est, timeo danaos\"}"}]

The value of :key1 is returned as String. I can't make heads or tails of the documentation. Does anyone have a way to turn this directly back into the array it started out as: ["lorem ipsum", "caesar", "quidquid id est, timeo danaos"]?

Much appreciated from a total newbie

Solution

  • This is usually handled when you load the pg_array extension into your Sequel::Database instance. Maybe you just loaded the pg_array extension file without loading it into your Sequel::Database instance. You might have done:

    Sequel.extension :pg_array

    instead of:

    DB.extension :pg_array

    You need to load the pg_array extension into your Sequel::Database instance for retrieved values to be array-like objects and not strings.