pg_hstore_ops.rb

The pg_hstore_ops extension adds support to Sequel’s DSL to make it easier to call PostgreSQL hstore functions and operators.

To load the extension:

Sequel.extension :pg_hstore_ops

The most common usage is taking an object that represents an SQL expression (such as a :symbol), and calling Sequel.hstore_op with it:

h = Sequel.hstore_op(:hstore_column)

If you have also loaded the pg_hstore extension, you can use Sequel.hstore as well:

h = Sequel.hstore(:hstore_column)

Also, on most Sequel expression objects, you can call the hstore method:

h = Sequel[:hstore_column].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 Symbol#hstore:

h = :hstore_column.hstore

This creates a Sequel::Postgres::HStoreOp object that can be used for easier querying:

h - 'a'    # hstore_column - CAST('a' AS text)
h['a']     # hstore_column -> 'a'

h.concat(:other_hstore_column)       # ||
h.has_key?('a')                      # ?
h.contain_all(:array_column)         # ?&
h.contain_any(:array_column)         # ?|
h.contains(:other_hstore_column)     # @>
h.contained_by(:other_hstore_column) # <@

h.defined        # defined(hstore_column)
h.delete('a')    # delete(hstore_column, 'a')
h.each           # each(hstore_column)
h.keys           # akeys(hstore_column)
h.populate(:a)   # populate_record(a, hstore_column)
h.record_set(:a) # (a #= hstore_column)
h.skeys          # skeys(hstore_column)
h.slice(:a)      # slice(hstore_column, a)
h.svals          # svals(hstore_column)
h.to_array       # hstore_to_array(hstore_column)
h.to_matrix      # hstore_to_matrix(hstore_column)
h.values         # avals(hstore_column)

Here are a couple examples for updating an existing hstore column:

# Add a key, or update an existing key with a new value
DB[:tab].update(h: Sequel.hstore_op(:h).concat('c'=>3))

# Delete a key
DB[:tab].update(h: Sequel.hstore_op(:h).delete('k1'))

On PostgreSQL 14+, The hstore [] method will use subscripts instead of being the same as get, if the value being wrapped is an identifer:

Sequel.hstore_op(:hstore_column)['a']    # hstore_column['a']
Sequel.hstore_op(Sequel[:h][:s])['a']      # h.s['a']

This support allows you to use hstore subscripts in UPDATE statements to update only part of a column:

h = Sequel.hstore_op(:h)
DB[:t].update(h['key1'] => 'val1', h['key2'] => 'val2')
#  UPDATE "t" SET "h"['key1'] = 'val1', "h"['key2'] = 'val2'

See the PostgreSQL hstore function and operator documentation for more details on what these functions and operators do.

If you are also using the pg_hstore extension, you should load it before loading this extension. Doing so will allow you to use HStore#op to get an HStoreOp, allowing you to perform hstore operations on hstore literals.

Some of these methods will accept ruby arrays and convert them automatically to PostgreSQL arrays if you have the pg_array extension loaded. Some of these methods will accept ruby hashes and convert them automatically to PostgreSQL hstores if the pg_hstore extension is loaded. Methods representing expressions that return PostgreSQL arrays will have the returned expression automatically wrapped in a Postgres::ArrayOp if the pg_array_ops extension is loaded.

Related module: Sequel::Postgres::HStoreOp