Last Updated: February 25, 2016
· boriscy

Update hstore column in PostgreSQL

I wanted to update extras hstore column , I could't find something on the web so I created one

UPDATE accounts SET extras = CONCAT(extras::text, ',"inventory"=>"',
CASE WHEN COALESCE(extras->'inventory', 'false') = 'false' THEN 'true'
ELSE 'false' end, '"')::hstore
WHERE type IN ('Income', 'Expense');

Just concatenate a text convert to hstore and use COALESCE in case there are some null values.