is it possible to add table metadata in postgresql?

Question

Forgive my ignorance, but I'm wondering if there is a way to specify metadata for a table in PostgreSQL that I don't want it to be as a field in that table. For instance, if I want to add a Description field for that table, creation Time, etc...

I know I can do this using extra tables, but I'd prefer having not to do this, to be honest. I've digged in the official PostgreSQL docs, but there's nothing there besides looking in information_schema.tables, where I guess I'm not allowed to modify anything.

Any clues? Otherwise, I guess I'll have to create a few more tables to handle this.

Thanks!

Answer 1

There's the comment field:

COMMENT ON TABLE my_table IS 'Yup, it's a table';

In current versions the comment field is limited to a single text string. There's been discussion of allowing composite types or records, but AFAIK no agreement on any workable design.

You can shove JSON into the comments if you want. It's a bit dirty, since it'll show up as the Description column in \d+ output in psql, etc, but it'll work.

craig=> COMMENT ON TABLE test IS 'Some table';
COMMENT
craig=> \d+
                         List of relations
 Schema |         Name         |   Type   | Owner |    Size    | Description 
--------+----------------------+----------+-------+------------+-------------
 public | test                 | table    | craig | 8192 bytes | Some table

You can get the comment from SQL with:

SELECT pg_catalog.obj_description('test'::regclass, 'pg_class');

Comments can also be added on other objects, like columns, data types, functions, etc.

If that doesn't fit your needs you're pretty much stuck with a side table for metadata.

People regularly request table metadata like creation time, etc, but nobody tends to step up with a workable plan for an implementation and the time and enthusiasm to carry it through to the finish. In any case the most common request is "last modified time", which is pretty horrible from a performance point of view and difficult to get right in the face of multi-version concurrency control, transaction isolation rules, etc.