pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: finding UNIQUES in information_schema
4+ messages / 2 participants
[nested] [flat]
* finding UNIQUES in information_schema
@ 2009-04-16 19:33 Gerardo Herzig <gherzig@fmed.uba.ar>
2009-04-16 19:12 ` Re: finding UNIQUES in information_schema Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Gerardo Herzig @ 2009-04-16 19:33 UTC (permalink / raw)
To: pgsql-sql
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.
See:
Table "public.almatnov"
Column | Type | Modifiers
-------------+------------------------+--------------------------------
formu | integer |
alucod | integer | default 0
codcarr | character varying(3) | default ''::character varying
anifm | character varying(2) | default 20
comentarios | text | default ''::text
Indexes:
"almatnov_alucod_codcarr_idx" UNIQUE, btree (alucod, codcarr)
"almatnov_codcarr_alucod" UNIQUE, btree (alucod, codcarr)
And now:
ematerias=# SELECT * from information_schema.constraint_table_usage
where table_name='almatnov';
table_catalog | table_schema | table_name | constraint_catalog |
constraint_schema | constraint_name
---------------+--------------+------------+--------------------+-------------------+-----------------
(0 rows)
ematerias=# SELECT * from information_schema.key_column_usage where
table_name='almatnov';
constraint_catalog | constraint_schema | constraint_name |
table_catalog | table_schema | table_name | column_name |
ordinal_position | position_in_unique_constraint
--------------------+-------------------+-----------------+---------------+--------------+------------+-------------+------------------+-------------------------------
(0 rows)
1) Im a doing anything wrong?
2) It is safe to extract metainfo from pg_catalog?
Thanks!
Gerardo
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: finding UNIQUES in information_schema
2009-04-16 19:33 finding UNIQUES in information_schema Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2009-04-16 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-16 20:44 ` Re: finding UNIQUES in information_schema Gerardo Herzig <gherzig@fmed.uba.ar>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2009-04-16 19:12 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: finding UNIQUES in information_schema
2009-04-16 19:33 finding UNIQUES in information_schema Gerardo Herzig <gherzig@fmed.uba.ar>
2009-04-16 19:12 ` Re: finding UNIQUES in information_schema Tom Lane <tgl@sss.pgh.pa.us>
@ 2009-04-16 20:44 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2009-04-16 20:09 ` Re: finding UNIQUES in information_schema Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Gerardo Herzig @ 2009-04-16 20:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: finding UNIQUES in information_schema
2009-04-16 19:33 finding UNIQUES in information_schema Gerardo Herzig <gherzig@fmed.uba.ar>
2009-04-16 19:12 ` Re: finding UNIQUES in information_schema Tom Lane <tgl@sss.pgh.pa.us>
2009-04-16 20:44 ` Re: finding UNIQUES in information_schema Gerardo Herzig <gherzig@fmed.uba.ar>
@ 2009-04-16 20:09 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2009-04-16 20:09 UTC (permalink / raw)
To: Gerardo Herzig <gherzig@fmed.uba.ar>; +Cc: pgsql-sql
Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> Tom Lane wrote:
>> 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.
> Well, i just dont get it.
No, apparently not. The term "constraint" in this context has a very
specific meaning; it's talking about the CONSTRAINT syntax in CREATE
TABLE or ALTER TABLE. This is per SQL specification. Indexes are an
implementation detail that the standard doesn't concern itself with;
therefore anything you do with CREATE INDEX is outside the standard and
is not reflected in the standard-defined information_schema.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2009-04-16 20:44 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-04-16 19:33 finding UNIQUES in information_schema Gerardo Herzig <gherzig@fmed.uba.ar>
2009-04-16 19:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2009-04-16 20:44 ` Gerardo Herzig <gherzig@fmed.uba.ar>
2009-04-16 20:09 ` Tom Lane <tgl@sss.pgh.pa.us>
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