7

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?

Improve this question
1
  • 1
    Define "doesn't work". Commented Mar 23, 2016 at 12:17

1 Answer 1

Reset to default
12

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.
Improve this answer
2
  • Can't you use OR changed_fields->'updated_by' IS NULL instead? This would work both for a defined key that's null, and for an undefined key; whereas your EXIST method would only work for an undefined key, but give you the wrong answer if you have: '"updated_by" => NULL' in your hstore. IE, try: select exist('"bloo" => NULL', 'bloo'); and you will get t even though it's NULL and you probably want f Commented Feb 25, 2022 at 17:45
  • @nzifnab Six years later I am not totally sure how the original question used 'defined', but to me it sounds exist() gave the needed answer. If your use case differs (e.g. you want to distinguish between the cases you mention), I think you can use your proposed version. Commented Mar 2, 2022 at 15:23

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.