public inbox for [email protected]  

help / color / mirror / Atom feed

From: Vincent Veyron <[email protected]>
To: yudhi s <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: question on audit columns
Date: Thu, 5 Sep 2024 19:32:51 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
References: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>

On Wed, 4 Sep 2024 18:19:47 +0530
yudhi s <[email protected]> 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

view thread (5+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: question on audit columns
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox