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.

  • Well, with jsonb you can use jsonb_strip_nulls(). What's wrong with using jsonb instead of hstore? The functions around jsonb are much more flexible and powerful anyways
    – user1822
    Oct 13, 2019 at 15:30
  • @a_horse_with_no_name hstore is already in use. Switching to jsonb at this time isn't feasible. So we could convert using hstore_to_jsonb() and then jsonb_strip_nulls(). Is there a way to convert from jsonb to hstore? If that is possible, I would accept that answer.
    – thames
    Oct 13, 2019 at 23:34
  • @a_horse_with_no_name it looks like we could do something like select hstore(jsonb_each(jsonb_strip_nulls(some_hstore_data::jsonb))) from my_table; do you foresee any issues?
    – thames
    Oct 13, 2019 at 23:48
  • The only possible other way to do it without going to jsonb and back is if there is a way to filter on a set e.g. filter each(hstore), but not sure how to filter on a set.
    – thames
    Oct 13, 2019 at 23:51
  • Note: select 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, 2019 at 23:59

2 Answers 2


With only built-in tools of Postgres and the additional module hstore, without involving jsonb (as requested):

audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*) - excluded_cols)
      WHERE  value IS NOT NULL

One of the overloaded variants of the hstore() functions takes a two-dimensional array.

Alternatively, you could eliminate key names listed in excluded_cols in the WHERE clause (while processing unnested keys anyway):

audit_row.row_data = hstore(
   ARRAY (
      SELECT ARRAY[key, value]
      FROM   each(hstore(NEW.*))
      WHERE  value IS NOT NULL
      AND    key <> ANY (excluded_cols)

Might be a bit faster, not sure. Probably not much difference.

Also not sure how jsonbandjsonb_strip_nulls()` (suggested in comments) perform in comparison. Can you report back with a performance comparison?

  • Your suggestion is faster than converting to jsonb and back to hstore using SELECT. I haven't tested INSERT / TRIGGER, but would assume it's comparable. SELECT hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(row_data) WHERE value IS NOT NULL ) ) FROM audit.logged_actions ORDER BY event_id LIMIT 10000; Is Planning time: 0.172 ms Execution time: 679.332 ms and SELECT hstore( ARRAY ( SELECT ARRAY[key, value] FROM each(row_data) WHERE value IS NOT NULL ) ) FROM audit.logged_actions ORDER BY event_id LIMIT 10000; is Planning time: 0.111 ms Execution time: 581.399 ms
    – thames
    Oct 15, 2019 at 2:39
  • @thames: I see the same query twice. c/p hickup? Oct 15, 2019 at 10:45
  • yeah c/p hickup. The query was SELECT hstore( array_agg(key), array_agg(value)) FROM jsonb_each_text(jsonb_strip_nulls(row_data::jsonb) ) ) FROM audit.logged_actions ORDER BY event_id LIMIT 1000; with Planning time: 0.172 ms Execution time: 679.332 ms
    – thames
    Oct 16, 2019 at 0:33
  • Thanks for the feedback! Oct 16, 2019 at 0:43

Can you try this:

  • First, declare a new variable type record, example: empty_row
  • Then, do as bellow:
EXECUTE format('select * from populate_record(null::%s.%s, '''')', TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO empty_row;

audit_row.row_data = hstore(NEW.*) - empty_row - excluded_cols;

You can then reuse empty_row in different places in your function.

  • Results in an error. Doesn't seem to like the param as a type. Any suggestions? [42601] ERROR: syntax error at or near "$1" Where: PL/pgSQL function if_modified_func() line 92 at EXECUTE
    – thames
    Oct 15, 2019 at 2:08
  • I update the SQL to insert table name. Could you check it
    – binhgreat
    Oct 15, 2019 at 5:08
  • [42704] ERROR: type "logged_actions" does not exist. You can also check it. The entire audit.logged_actions code/functions trigger is github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql after you run that script then call SELECT audit.audit_table('myschema.my_table'); on the table you want to audit.
    – thames
    Oct 16, 2019 at 0:48
  • Sorry, I forgot that you use difference schema. I update to use TG_TABLE_SCHEMA to reflect it
    – binhgreat
    Oct 16, 2019 at 11:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.