Last Update: 2017-01-19 10:04:33 -0800

The pg_array extension adds support for Sequel to handle PostgreSQL's array types.

This extension integrates with Sequel's native postgres adapter and the jdbc/postgresql adapter, so that when array fields are retrieved, they are parsed and returned as instances of Sequel::Postgres::PGArray. PGArray is a DelegateClass of Array, so it mostly acts like an array, but not completely (is_a?(Array) is false). If you want the actual array, you can call PGArray#to_a. This is done so that Sequel does not treat a PGArray like an Array by default, which would cause issues.

In addition to the parsers, this extension comes with literalizers for PGArray using the standard Sequel literalization callbacks, so they work with on all adapters.

To turn an existing Array into a PGArray:


If you have loaded the core_extensions extension, or you have loaded the core_refinements extension and have activated refinements for the file, you can also use Array#pg_array:


You can also provide a type, though in many cases it isn't necessary:

Sequel.pg_array(array, :varchar) # or :integer, :"double precision", etc.
array.pg_array(:varchar) # or :integer, :"double precision", etc.

So if you want to insert an array into an integer[] database column:

DB[:table].insert(:column=>Sequel.pg_array([1, 2, 3]))

To use this extension, first load it into your Sequel::Database instance:

DB.extension :pg_array

See the schema modification guide for details on using postgres array columns in CREATE/ALTER TABLE statements.

This extension by default includes handlers for array types for all scalar types that the native postgres adapter handles. It also makes it easy to add support for other array types. In general, you just need to make sure that the scalar type is handled and has the appropriate converter installed in Sequel::Postgres::PG_TYPES or the Database instance's conversion_procs usingthe appropriate type OID. For user defined types, you can do this via:

DB.conversion_procs[scalar_type_oid] = lambda{|string| }

Then you can call Sequel::Postgres::PGArray::DatabaseMethods#register_array_type to automatically set up a handler for the array type. So if you want to support the foo[] type (assuming the foo type is already supported):


You can also register array types on a global basis using Sequel::Postgres::PGArray.register. In this case, you'll have to specify the type oids:

Sequel::Postgres::PG_TYPES[1234] = lambda{|string| }
Sequel::Postgres::PGArray.register('foo', :oid=>4321, :scalar_oid=>1234)

Both Sequel::Postgres::PGArray::DatabaseMethods#register_array_type and Sequel::Postgres::PGArray.register support many options to customize the array type handling. See the Sequel::Postgres::PGArray.register method documentation.

While this extension can parse PostgreSQL arrays with explicit bounds, it currently ignores explicit bounds, so such values do not round trip.

If you want an easy way to call PostgreSQL array functions and operators, look into the pg_array_ops extension.

This extension requires the strscan and delegate libraries.

Related module: Sequel::Postgres::PGArray

Required files

  1. delegate
  2. strscan
  3. adapters/utils/pg_types