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 ?
ALTER TABLE … SET ( attribute_option = value [, ... ] )
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?
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
Perhaps something like that in Postgres?
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.