Last Update: 2022-10-30 19:13:54 -0700

The pg_hstore extension adds support for the PostgreSQL hstore type to Sequel. hstore is an extension that ships with PostgreSQL, and the hstore type stores an arbitrary key-value table, where the keys are strings and the values are strings or NULL.

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

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

To turn an existing Hash into an HStore, use Sequel.hstore:


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 Hash#hstore:


Since the hstore type only supports strings, non string keys and values are converted to strings

Sequel.hstore(foo: 1).to_hash # {'foo'=>'1'}
v = Sequel.hstore({})
v[:foo] = 1
v # {'foo'=>'1'}

However, to make life easier, lookups by key are converted to strings (even when accessing the underlying hash directly):

Sequel.hstore('foo'=>'bar')[:foo] # 'bar'
Sequel.hstore('foo'=>'bar').to_hash[:foo] # 'bar'

HStore instances mostly just delegate to the underlying hash instance, so Hash methods that modify the receiver or returned modified copies of the receiver may not do string conversion. The following methods will handle string conversion, and more can be added later if desired:

  • []

  • []=

  • assoc

  • delete

  • fetch

  • has_key?

  • has_value?

  • include?

  • key

  • key?

  • member?

  • merge

  • merge!

  • rassoc

  • replace

  • store

  • update

  • value?

If you want to insert a hash into an hstore database column:

DB[:table].insert(column: Sequel.hstore('foo'=>'bar'))

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

DB.extension :pg_hstore

This extension integrates with the pg_array extension. If you plan to use arrays of hstore types, load the pg_array extension before the pg_hstore extension:

DB.extension :pg_array, :pg_hstore

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

This extension requires the delegate and strscan libraries.

Related module: Sequel::Postgres::HStore

Required files

  1. delegate
  2. strscan