Postgresql – Postgres hstore check if a key exists and does not match value

hstorepostgresql

I have a hstore field in an audit table that stores all the fields that have changed as a result of an operation.

On inserts, the key, updated_by in changed_fields is NULL and on system updates it is set to system. I would like to return all rows where either the key is not defined or the key is not system but I am lost as to how to do this.

So far I have tried

select changed_fields -> 'updated_by' 
from audit.logged_actions 
where (changed_fields -> 'updated_by' != 'system' 
       or defined(changed_fields, 'updated_by') = false) 
order by event_id desc

But that doesn't work and I am not quite sure why. Any ideas on what I am doing wrong?

Best Answer

The usage of defined() in the documentation:

does hstore contain non-NULL value for key?

In addition to a NULL value for an existing key, it returns FALSE also when the key does not exist.

To keep it clean, you might want to use the exist() function instead:

SELECT exist('"bla" => 1234', 'bloo');
 exist 
───────
 f

So your query would look like

SELECT changed_fields -> 'updated_by' 
  FROM audit.logged_actions 
 WHERE changed_fields -> 'updated_by' <> 'system' 
       OR NOT exist(changed_fields, 'updated_by')
 ORDER by event_id desc;

Notes:

  • never test logical values using the equality operator (unless you can be really sure the value cannot be NULL). You can directly use the value if it is guaranteed to not be NULL (like in my query), or use the IS operator.
  • In the current form, the query is checking a simple 'OR' - from your wording it is not quite clear what should happen when both conditions are true.
Related Question