Date: Thu, 5 Sep 2024 19:32:51 +0200
From: Vincent Veyron <vv.lists@wanadoo.fr>
To: yudhi s <learnerdatabase99@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: question on audit columns
Message-Id: <20240905193251.8b872491a1789e7d5710704f@wanadoo.fr>
In-Reply-To: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
References: <CAEzWdqd=83DQo4SK0sjPhBNAHGCvdcJ3X0OruFLgkL008AM8ug@mail.gmail.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/20240905193251.8b872491a1789e7d5710704f%40wanadoo.fr>
Precedence: bulk

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_timestam=
p"
> 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.
>=20

If you can live with the fact that updated_by_user and updated_timestamp ge=
t the same values as created_by_user and created_timestamp when inserting t=
he record, then you can do :

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

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

vv=3D> select * from audit;
 created_by_user |     created_timestamp      | updated_by_user |     updat=
ed_timestamp     | data=20
-----------------+----------------------------+-----------------+----------=
-----------------+------
 vincent         | 2024-09-05 19:17:53.446109 | postgres        | 2024-09-0=
5 19:24:01.19186 | def
(1 row)


--=20
					Bien =E0 vous, Vincent Veyron=20

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