Обсуждение: Add custom properties to a column's definition (pg_attribute)
Can anyone suggest a way to associate a set of custom properties (key/value pairs) with a column's definition (ie pg_attribute)?
(without having to replicate/maintain a table of columns separately from the system catalog)
For example:
* I have a table with 50 columns
* I'd like to 'flag' 30 of those columns as group - 'A' and the other 20 as group - 'B'
So I'd like a way to add custom metdata (k/v pairs) to a column's definition (not the actual rows of data).
On 09/01/2014 20:08, Brooke Beacham wrote: > Can anyone suggest a way to associate a set of custom properties > (key/value pairs) with a column's definition (ie pg_attribute)? > > (without having to replicate/maintain a table of columns separately from > the system catalog) > > For example: > * I have a table with 50 columns > * I'd like to 'flag' 30 of those columns as group - 'A' and the other 20 > as group - 'B' > > > So I'd like a way to add custom metdata (k/v pairs) to a column's > definition (not the actual rows of data). You could use the comment facility - COMMENT ON COLUMN tablename.columnname IS IS '....'; - to store some JSON or the like, though you'll have to parse it client-side. Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
On 09/01/2014 20:16, Raymond O'Donnell wrote: > On 09/01/2014 20:08, Brooke Beacham wrote: >> Can anyone suggest a way to associate a set of custom properties >> (key/value pairs) with a column's definition (ie pg_attribute)? >> >> (without having to replicate/maintain a table of columns separately from >> the system catalog) >> >> For example: >> * I have a table with 50 columns >> * I'd like to 'flag' 30 of those columns as group - 'A' and the other 20 >> as group - 'B' >> >> >> So I'd like a way to add custom metdata (k/v pairs) to a column's >> definition (not the actual rows of data). > > You could use the comment facility - > > COMMENT ON COLUMN tablename.columnname IS IS '....'; Whoops, sorry, an extra "is" got in there.... should have been: COMMENT ON COLUMN tablename.columnname IS '....'; Ray. -- Raymond O'Donnell :: Galway :: Ireland rod@iol.ie
Brooke Beacham wrote > (without having to replicate/maintain a table of columns separately from > the system catalog) Just create the friggin' table and wrap whatever logic you want in a view (or functions) so that you at least get usable results/defaults for any columns you haven't added. Any solution you pick has the risk of becoming out-of-sync so tossing out the best solution does more harm than good. In terms of "group" columns you might just consider creating two tables that have a one-to-one relationship to each other (or three, the base table and then group A and group B tables). A lot depends on the why and structure of your model but there is no standard facility for custom column meta-data and though the comment facility can be used for this I much prefer to leave that for human consumption and have something more structured and easily customize-able for meta-data. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Add-custom-properties-to-a-column-s-definition-pg-attribute-tp5786158p5786174.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.