Re: [GENERAL] - Mailing list pgsql-general

From Neil Anderson
Subject Re: [GENERAL]
Date
Msg-id CAEKCySvm==c0yHz8sGSGYbv5P7bOBOii9KmDakd7N2J2fab4sA@mail.gmail.com
Whole thread Raw
In response to Re: [GENERAL]  (Igor Korot <ikorot01@gmail.com>)
List pgsql-general
>
> 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



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: [GENERAL] Constraints of view attributes
Next
From: Gavin Flower
Date:
Subject: Re: [GENERAL] Top posting....