Re: Adding date column to system catalog "pg_database"

On 7 June 2018 at 13:05, pavan95 <pavan.postgresdba@gmail.com> wrote:
> Any ideas in accomplishment of this task are of great help!

You could create a (structured, json?) comment on the database with this
kind of information, if you don't use the comment field on the database (yet).


feike=# SELECT json_build_object('created', now(), 'author',
CURRENT_USER)::text AS comment;
-[ RECORD 1 ]-----------------------------------------------------------------
comment | {"created" : "2018-06-09T08:33:30.994298+00:00", "author" : "feike"}

feike=# COMMENT ON DATABASE demo IS '{"created" :
"2018-06-09T08:33:30.994298+00:00", "author" : "feike"}';
COMMENT


Later on, you can read/parse that comment using pg_shdescription:


feike=#
SELECT description,
       (description::jsonb->>'created')::timestamptz
  FROM pg_shdescription
  JOIN pg_database pd ON (objoid=pd.oid and classoid =
'pg_catalog.pg_database'::regclass)
 WHERE datname = 'demo';
-[ RECORD 1 ]---------------------------------------------------------------------
description | {"created" : "2018-06-09T08:33:30.994298+00:00",
"author" : "feike"}
timestamptz | 2018-06-09 08:33:30.994298+00


regards,

Feike