Data typesversion 1.8

hstore

Key-value pairs in one column.

What it is for

hstore stores a flat set of text keys and text values in one column, with operators to read, test and merge keys and GIN index support. For nested data jsonb is usually the better choice; hstore remains simple and compact for flat string maps.

Enable it

sql

create extension if not exists hstore with schema extensions;

Example

sql

select 'color => red, size => M'::hstore -> 'color';    -- red

select * from products where attributes ? 'size';
update products set attributes = attributes || 'size => L'::hstore where id = 1;

Questions

How do I enable hstore?

Run create extension if not exists hstore with schema extensions; in the SQL editor, or switch it on from the Extensions page in the console. You do not need superuser access.

Which version of hstore is installed?

1.8, on Postgres 17.6, as read from the running engine on 2026-10-08. Check yours with: select extversion from pg_extension where extname = 'hstore';

Upstream project: www.postgresql.org/docs/17/hstore.html

More data types extensions

uuid-ossp UUID generators (v1 to v5).
pg_uuidv7 Time-ordered UUID v7 values.
citext Case-insensitive text, for emails and user names.
ltree Tree paths such as categories and org charts.
intarray Functions and indexes for integer arrays.
cube Multi-dimensional points and boxes.

Try hstore on Scribase All extensions