From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: finding UNIQUES in information_schema
Date: Thu, 16 Apr 2009 17:44:19 -0300
Message-ID: <49E798A3.2020805@fmed.uba.ar> (raw)
In-Reply-To: <28152.1239909139@sss.pgh.pa.us>
References: <49E787FC.8020909@fmed.uba.ar>
<28152.1239909139@sss.pgh.pa.us>
Tom Lane wrote:
> Gerardo Herzig <gherzig@fmed.uba.ar> writes:
>> Hi all. Im failing while trying to obtain some metainfo from
>> information_schema. Acording to the docs, constraint_column_usage and
>> key_column_usage views contains some information about constraints and
>> indexes.
>
> No, they contain information about constraints, period. How did you
> create those indexes? If you made them via SQL-standard PRIMARY KEY or
> UNIQUE constraint syntax, they should show in the information_schema.
> Otherwise not.
>
> regards, tom lane
>
Well, i just dont get it. Official docs from 8.2 says:
"""
The view key_column_usage identifies all columns in the current
database that are restricted by some *unique*, *primary key*, or foreign
key constraint. Check constraints are not included in this view. Only
those columns are shown that the current user has access to, by way of
being the owner or having some privilege.
"""
Well, damn, lets create some UNIQUE
edatos=# CREATE UNIQUE INDEX aluestud_alu_cod_anifm on aluestud (alucod,
codcarr1, anifm);
CREATE INDEX
edatos=#\d aluestud
[...]
Indexes:
"estud_idx1" PRIMARY KEY, btree (alucod, codcarr1)
"aluestud_alu_cod_anifm" UNIQUE, btree (alucod, codcarr1, anifm)
And...
edatos=# select table_name, constraint_name, column_name from
information_schema.key_column_usage where table_name='aluestud';
table_name | constraint_name | column_name
------------+-----------------+-------------
aluestud | estud_idx1 | alucod
aluestud | estud_idx1 | codcarr1
(2 rows)
Damn, im that idiot? Or should i get the UNIQUE i have just defined also?
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-sql@postgresql.org
Cc: gherzig@fmed.uba.ar, tgl@sss.pgh.pa.us
Subject: Re: finding UNIQUES in information_schema
In-Reply-To: <49E798A3.2020805@fmed.uba.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox