The easiest way to explain the question is in the usage of an audit trigger.
However, on insert it saves all values, including null values. I want to filter out the null values in the hstore(NEW.*)
. What is the simplest / fastest way to do this?
Source: https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql#L134
audit_row.row_data = hstore(NEW.*) - excluded_cols;
This obviously doesn't work but explains, hopefully, what the answer will look like.
audit_row.row_data = hstore(hstore(NEW.*) FILTER (WHERE value IS NOT NULL)) - excluded_cols;
Preference will go to answer that doesn't use an user created function, e.g. use only Postgres functions / operators.
This is for >= PostgreSQL 11.
I am looking for a solution with hstore
, not json
or jsonb
. Alternative ways with json
or jsonb
may be included, but hstore
is preferred.
jsonb_strip_nulls()
. What's wrong with using jsonb instead of hstore? The functions around jsonb are much more flexible and powerful anyways – a_horse_with_no_name Oct 13 at 15:30hstore
is already in use. Switching tojsonb
at this time isn't feasible. So we could convert usinghstore_to_jsonb()
and thenjsonb_strip_nulls()
. Is there a way to convert fromjsonb
tohstore
? If that is possible, I would accept that answer. – thames Oct 13 at 23:34select hstore(jsonb_each(jsonb_strip_nulls(some_hstore_data::jsonb))) from my_table;
do you foresee any issues? – thames Oct 13 at 23:48jsonb
and back is if there is a way to filter on aset
e.g. filtereach(hstore)
, but not sure how to filter on aset
. – thames Oct 13 at 23:51select hstore(jsonb_each(jsonb_strip_nulls(some_hstore_data::jsonb))) from my_table;
doesn't work as it doesn't convert right. Maybe it could be modified to correctly convert. – thames Oct 13 at 23:59