Re: question on audit columns

On Wed, 4 Sep 2024 18:19:47 +0530
yudhi s <learnerdatabase99(at)gmail(dot)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_timestamp when 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_user text 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