How to filter hstore where values are not null in a trigger?

Question

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.

Answer 1

To do it with built-in tools of Postgres and the hstore module exclusively, without involving jsonb, as requested, replace:

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

with:

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

While unnesting the hstore vlaue anyway, you could eliminate key names in excluded_cols in the WHERE clause instead:

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 the route via jsonb and jsonb_strip_nulls() (as has been discussed in comments) performs in comparison. Can you report back with a performance comparison?

Answer 2

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.