>
> 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
neil@postgrescompare.com
https://www.postgrescompare.com