> > Like I said, what I expect to see from the query is: > > id | integer | | 5| 2 | 0 | P | > name | varchar | 50| 2 | | | | <NULL> > > So I need the information about the field and whether the field is a > primary/foreign key or not. >
I had a go at it using the catalog tables from v9.5 and an example table 'films', maybe you can extend this further to get what you need from the pg_attribute, pg_class, pg_type and pg_constraint tables? SELECT columns.attname as name, data_types.typname as type, columns.attlen as length, columns.attnotnull as not_null, constraints.contype FROM pg_attribute columns INNER JOIN pg_class tables ON columns.attrelid = tables.oid INNER JOIN pg_type data_types ON columns.atttypid = data_types.oid LEFT JOIN pg_constraint constraints ON constraints.conrelid = columns.attrelid AND columns.attnum = ANY (constraints.conkey) WHERE tables.relname = 'films' AND columns.attnum > 0; Thanks, Neil -- Neil Anderson n...@postgrescompare.com https://www.postgrescompare.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
