From: | Neil Anderson <neil(at)postgrescompare(dot)com> |
---|---|
To: | Igor Korot <ikorot01(at)gmail(dot)com> |
Cc: | John R Pierce <pierce(at)hogranch(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: |
Date: | 2017-05-11 20:26:53 |
Message-ID: | CAEKCySvm==c0yHz8sGSGYbv5P7bOBOii9KmDakd7N2J2fab4sA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)postgrescompare(dot)com
https://www.postgrescompare.com
From | Date | Subject | |
---|---|---|---|
Next Message | Gavin Flower | 2017-05-11 20:38:17 | Re: Top posting.... |
Previous Message | Tom Lane | 2017-05-11 20:07:48 | Re: Constraints of view attributes |