IntermediateAdvanced PostgreSQL

HSTORE: key-value pairs

The query

SQL

-- Requires: CREATE EXTENSION hstore;
CREATE TABLE product_meta (
  product_id INT,
  attrs HSTORE
);

SELECT
  product_id,
  attrs->'color' AS color,
  attrs->'weight' AS weight,
  akeys(attrs) AS all_keys,
  avals(attrs) AS all_values
FROM product_meta
WHERE attrs ? 'color'  -- has key 'color'
  AND attrs @> 'size=>large';  -- contains this pair

Tested against PostgreSQL 16

Note

“HSTORE is key-value store. For flexible schema attributes. Often replaced by JSONB in modern apps.