Track my own custom metadata on Postgres tables and columns

Question

Is there any way to add my own metadata to tables and columns in Postgres, such as key-value pairs of short text?

I am migrating tables, columns, and data from another database system. That other system has properties defined on tables and columns not matched by Postgres. For example, in the other database system each table can be assigned a color for use in graphical display of the database structure such as CUSTOMER_ table being green and INVOICE_ table being red.

So I would like a way to keep track of those property settings within my Postgres database in such a way that my Java app may query for the custom metadata via JDBC.

SET attribute ?

I noticed:

ALTER TABLE … SET ( attribute_option = value [, ... ] )

…and…

ALTER TABLE … ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )

The documentation for ALTER TABLE does not really explain what these attributes do. Can I invent my own attribute names to be used as keys that map to my own metadata values?

Or is there some other approach to having Postgres remember my own custom metadata?

Comment

I suppose one workaround might be embedding key-value pairs as text with the COMMENT property on each table and column. Seems like kind of a hack, and kind of messy as I do often use COMMENT to document business rules as prose. But I suppose it would work. Any better way to remember per-table and per-column attributes?

Extended Properties of Microsoft SQL Server

Apparently MS SQL Server offers an "Extended Properties" feature for such meta-data. Described here on Stack Overflow and here on Microsoft doc.

Perhaps something like that in Postgres?

Additional tables

Of course I could define my own extra tables to store this meta-data. But I was hoping for something tied to the targeted tables and columns to be more obvious to those maintaining this app down road. No point in re-inventing the wheel if Postgres already provides me with a custom meta-data facility of some sort.

Answer 1

Usually the most portable way of doing this is to have your own metadata table, something like:

create table meta(
  table_name text not null,
  column_name text not null,
  attribute_name text not null,
  attribute_value text not null,
  primary key (table_name, column_name, attribute_name)
);

• This approach works with any database
• Access to metadata is done by standard SQL
• Migration and backup is very easy
• The attribute_value can be anything, you can declare it as byte[], text, json, jsonb, whatever you want...

Answer 2

Answer from comments by a-horse-with-no-name:

The manual does explain what those attributes are:

Currently, the only defined per-attribute options are n_distinct and n_distinct_inherited, which override the number-of-distinct-values estimates made by subsequent ANALYZE operations.

If you could introduce your own attributes the manual would say so. But in short: no, you can not introduce your own column attributes unless you change the Postgres source code. Your only options are to store that information as a comment (e.g. as a JSON) or to create your own table that stores the attributes in a JSON or HSTORE column.