1

Is it possible to add a kind of Extended Properties in tables and/or columns in PostgreSQL as we can do in SQL Server?

I've been looking for this in Google, but I can't find anything about it.

I want to describe columns (data dictionary) and add parameters that I can later match by reflection with my properties in Java.

Maximus Decimus
  • 4,901
  • 22
  • 67
  • 95
  • Not familiar with MSSQL, but it sounds like you could build a reasonable approximation by putting [JSON data](http://www.postgresql.org/docs/current/static/datatype-json.html) in [comment strings](http://www.postgresql.org/docs/current/static/sql-comment.html) – Nick Barnes Jan 30 '16 at 04:21

1 Answers1

3

Postgres (and many other DBMS) does this through a DDL statement comment on.

To attach a comment to a table, view, column, foreign key (pretty much everything) use comment on, e.g:

comment on table orders is 'Our orders';
comment on column orders.amount is 'Total amount of this order';

More details in the manual: http://www.postgresql.org/docs/current/static/sql-comment.html

The JDBC driver will return this information in the remarks column in the result of e.g. getTables() or getColumns()

To access the values through SQL, use the functions provided by Postgres:
http://www.postgresql.org/docs/current/static/functions-info.html#FUNCTIONS-INFO-COMMENT-TABLE

  • Thank you. But what about adding my own properties or annotations into a column? Comments would be useful for a data dictionary (half of my question). As far as I know, the columns will be created in lowercase but I want something like Pascal Case (e.g. EmployeeName). I can use the double quotes to force the name of my column. But then queries should always include double quoted for tables and columns. I'm using PostgreSQL 9.4 and pgAdminIII. I don't want this format "employee_social_security_number" or even "employeesocialsecuritynumber". – Maximus Decimus Jan 30 '16 at 17:02
  • I want to create my java properties easily and I thought that reading these kind of annotations from DB would be faster as I would keep my SQL queries simple – Maximus Decimus Jan 30 '16 at 17:03
  • 1
    @MaximusDecimus: you should get used to `snake_case`. It's really the only (and in my opinion the best) solution (and is used with essentially every other DBMS like Oracle, DB2, Firebird, Vertica, ...). Btw: you can essentially put anything in the comments, as far as I know there is no length limit –  Jan 30 '16 at 17:36
  • Thank you so much for answer. Really appreciated. I'm learning new products other than MSSQL. You made a strong point with Oracle, DB2 ans others. – Maximus Decimus Jan 30 '16 at 17:43