From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | Igor Korot <ikorot01(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: |
Date: | 2017-05-07 15:09:35 |
Message-ID: | 19856.1494169775@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> On 8 May 2017 at 00:42, Igor Korot <ikorot01(at)gmail(dot)com> wrote:
>> Basically what I'd like to see is the definition of each column and
>> whether this column is part of primary/foreign key or not.
> information_schema.table_constraints is of no use to you then. There
> are no details about which column(s) the constraint applies to.
information_schema.constraint_column_usage might help, though you'll
still need to join that to other views.
> Likely you'll want to look at pg_constraint for contype in('p','f')
> and unnest(conkey) and join that to information_schema.columns. You
> may also need to think about pg_constraint.confkey, depending on if
> you want to know if the column is referencing or referenced in a
> foreign key constraint.
If you don't mind a PG-specific solution, that's the way to go, as
it will surely be more efficient than going through the information_schema
views. Also, there are things that act like constraints but aren't
SQL-standard, such as exclusion constraints; we don't reflect those
in information_schema, so if you want to include those then you
*must* look directly at the catalogs.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Adam Brusselback | 2017-05-07 16:16:16 | Re: Caching and Blobs in PG? Was: Can PG replace redis, amqp, s3 in the future? |
Previous Message | David Rowley | 2017-05-07 12:57:01 | Re: |