finding UNIQUES in information_schema

From: Gerardo Herzig <gherzig(at)fmed(dot)uba(dot)ar>
To: pgsql-sql(at)postgresql(dot)org
Subject: finding UNIQUES in information_schema
Date: 2009-04-16 19:33:16
Message-ID: 49E787FC.8020909@fmed.uba.ar
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2009-04-16 20:09:56 Re: finding UNIQUES in information_schema
Previous Message Tom Lane 2009-04-16 19:12:19 Re: finding UNIQUES in information_schema