agora inbox for pgsql-general@postgresql.org  

help / color / mirror / Atom feed

From: Tom Lane <tgl@sss.pgh.pa.us>
To: Xavier Tarifa <xavier.tarifa@adparts.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: information_schema.constraint_column_usage view missing info
Date: Mon, 24 Aug 2026 10:23:01 -0400
Message-ID: <326993.1787581381@sss.pgh.pa.us> (raw)
In-Reply-To: <CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com>
References: <CAD40nCDxvQ5gLOO3cPHHC__+RVHbFhJZViMsEUtn_KviLntmyg@mail.gmail.com>

Xavier Tarifa <xavier.tarifa@adparts.com> writes:
> how do you go about finding information about constraint columns? I
> was trying to use the view information_schema.constraint_column_usage
> but I've found out that to uniquely identify a constraint it would
> need to show the constraint table, but in case of foreign keys it only
> shows the table of the referenced column.

Yeah.  The back story here is that the SQL standard says that
constraint names are to be unique per-schema.  So they defined
information_schema.constraint_column_usage on the assumption
that constraint_schema + constraint_name is a unique identifier.
Postgres of course considers constraint names to be local to a
table, so that different tables in the same schema can use the
same constraint name.  So you can either design your app to
obey the spec's restriction, or use something other than
constraint_column_usage to get info about your constraints.

Neither end of this is likely to change.  We're not going to
extend constraint_column_usage with extra columns: the entire
point of information_schema is to present an exactly
standards-compliant view of the catalogs.  And we're not going
to require constraint names to be unique per-schema either;
even if we wanted to do that, it'd doubtless break too many
existing applications.

			regards, tom lane

view thread (4+ messages)

Message-ID: <326993.1787581381@sss.pgh.pa.us>
Permalink:  ../326993.1787581381@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/326993.1787581381@sss.pgh.pa.us

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-general@postgresql.org
  Cc: tgl@sss.pgh.pa.us, xavier.tarifa@adparts.com, pgsql-general@lists.postgresql.org
  Subject: Re: information_schema.constraint_column_usage view missing info
  In-Reply-To: <326993.1787581381@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox