Re: question on audit columns

On Wed, 4 Sep 2024 18:19:47 +0530
yudhi s <learnerdatabase99@gmail.com> wrote:

Hi,

> In postgres database , we have all the tables with audit columns like
> created_by_user, created_timestamp,updated_by_user, updated_timestamp. So
> we have these fields that were supposed to be populated by the time at
> which the insert/update operation happened on the database but not at the
> application level. So we are planning to populate the created_by_user,
> created_timestamp columns by setting a default value of "current_timestamp"
> and "current_user" for the two columns,  but no such this is available to
> populate while we do the update of the row, so the only option seems to be
> through a trigger.
>

If you can live with the fact that updated_by_user and updated_timestamp get the same values as created_by_user and
created_timestampwhen inserting the record, then you can do : 

vv=> create table audit (created_by_user text default current_user, created_timestamp timestamp default now(),
updated_by_usertext default current_user, updated_timestamp timestamp default now(), data text); 
CREATE TABLE
vv=> insert into audit (data) values ('abc');
INSERT 0 1
vv=> select * from audit;
 created_by_user |     created_timestamp      | updated_by_user |     updated_timestamp      | data
-----------------+----------------------------+-----------------+----------------------------+------
 vincent         | 2024-09-05 19:17:53.446109 | vincent         | 2024-09-05 19:17:53.446109 | abc
(1 row)

--as user postgres
update audit set updated_by_user = DEFAULT, updated_timestamp = DEFAULT, data = 'def';

vv=> select * from audit;
 created_by_user |     created_timestamp      | updated_by_user |     updated_timestamp     | data
-----------------+----------------------------+-----------------+---------------------------+------
 vincent         | 2024-09-05 19:17:53.446109 | postgres        | 2024-09-05 19:24:01.19186 | def
(1 row)



--
                    Bien à vous, Vincent Veyron

https://marica.fr/
Logiciel de suivi des contentieux juridiques, des sinistres d'assurance et des contrats