0

I'm a long term user of SQL considering a switch to CQL & PostgreSQL. This is because I understand PostgreSQL 9.4 supports dynamic metadata and if it is what I think it is, this would make life easier for an app I'm developing.

I need to store key data about each field. This includes: where the field sits within a hierarchy and what front-end form item should be used as default to show the data (e.g. dropdown). These things will occasionally be updated.

Normally I would store these data in a separate table, but in this app, the user can add tables and extend existing ones. It would be amazing if I could store the data within the field definition instead of trying to keep two sets of tables in sync (in fact three, because of audit tables).

Is it possible to do what I want to do with 9.4, and can anyone tell me the right search term (because 'dynamic metadata postgreSQL' is returning 'dynamic SQL' results half the time).

Many thanks

Emma

emma
  • 784
  • 2
  • 9
  • 23
  • I'm really not sure what you mean by metadata in this context. Two features that might help you are the json/jsonb data types and table inheritance. Table inheritance is particularly powerful when you have a bunch of related things that have slightly differing structure. – Jim Nasby Feb 29 '16 at 20:17
  • "user of SQL". Microsoft SQL Server? Because you can't "switch" from "SQL" to PostgreSQL, SQL is the query language PostgreSQL uses. Anyway, I suspect you're looking for the json/hstore support. – Craig Ringer Mar 01 '16 at 04:59
  • You can attach [`COMMENT` strings](http://www.postgresql.org/docs/current/static/sql-comment.html) to various database objects, though if you want to attach multiple attributes to the same column, it's up to you to invent / interpret some data structure within these strings. [`json`](http://www.postgresql.org/docs/current/static/functions-json.html) might help with this. – Nick Barnes Mar 01 '16 at 05:40

0 Answers0