Is there any way to use a trigger which can get the only updated field from a record? I am just testing the audit trail log so it can save the field name alongside its other information when it is updated. I do not want to use IF statement because I may have more than 200 fields in my table.


2 Answers 2


If you are using the latest versions of PostgreSQL, you can use the JSON(B) functions and operators to your advantage. Although this is not (yet) a full solution, check to see if it mimics what you're trying to achieve:

-- The 'new' and 'old' entries will simulate the 'old' and 'new' 
-- values for a row that you can use in a trigger function
new(id, changed_column, integer_changed_column, not_changed,
   array_changed_column, changed_null) AS
    VALUES (12, text 'Value', 1234, text 'unchanged', 
        array [1, 2], cast(null as text))
old(id, changed_column, integer_changed_column, not_changed,
    array_changed_column, changed_null) AS
    VALUES (12, text 'New value', 1235, text 'unchanged', 
        array [1, 3], text 'not-null')

-- And we get a setof records with the changes
        (row_to_json(new)->column_name #>> '{}') AS new_value, 
        (row_to_json(old)->column_name #>> '{}') AS old_value
        new, old, (
            json_object_keys(row_to_json(new)) AS column_name
        ) AS cc
    ) AS s0
    new_value IS DISTINCT FROM old_value
    column_name ;

The result that you'll get shows you all the updated columns (=fields). I have assumed that more than one can be updated at once:

 column_name             | old_value | new_value
 array_changed_column    | [1,2]     | [1,3] 
 changed_column          | Value     | New value 
 changed_null            |           | not-null 
 integer_changed_column  | 1234      | 1235 

NOTE: all values are converted to text, because it is the type that all others can be converted to.


Another way is to exploit JSON/JSONB functions that come in recent versions of PostgreSQL. It has the advantage of working both with anything that can be converted to a JSON object (rows or any other structured data), and you don't even need to know the record type.

See my original StackOverflow post with appropriate examples.

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.