Re: Adding date column to system catalog "pg_database"

On 7 June 2018 at 13:05, pavan95 <pavan(dot)postgresdba(at)gmail(dot)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