On Thu, Aug 31, 2017 at 8:29 AM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:
On Wednesday, August 30, 2017, <hamann.w@xxxxxxxxxxx> wrote:
Hi,
is there a way to add a table create (and perhaps schema modify) timestamp to the system?
There is not. You may wish to search the archives for discussions as to why previous requests for this feature have not resulted in patches.David J.
Wolfgang, as David said, a column in pg_class for the creation time of a table does not exist. I long ago requested that feature as it is
in other DB's (Oracle & MS SQL Server), but the main reason that it was not done was that no one was interested in doing it.
As for finding tables with no comments, you can use the following query:
SELECT DISTINCT ON (n.nspname, c.relname)
n.nspname as schema,
c.relname,
a.rolname as owner,
d.description as comment
FROM pg_class c
LEFT JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = 0)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
AND d.description IS NULL
ORDER BY 1, 2;
n.nspname as schema,
c.relname,
a.rolname as owner,
d.description as comment
FROM pg_class c
LEFT JOIN pg_attribute col ON (col.attrelid = c.oid)
LEFT JOIN pg_description d ON (d.objoid = col.attrelid AND d.objsubid = 0)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_authid a ON ( a.OID = c.relowner )
WHERE n.nspname NOT LIKE 'information%'
AND relname NOT LIKE 'pg_%'
AND relname NOT LIKE 'information%'
AND relname NOT LIKE 'sql_%'
AND relkind = 'r'
AND d.description IS NULL
ORDER BY 1, 2;
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
