From: | Kristo Kaiv <kristo(dot)kaiv(at)skype(dot)net> |
---|---|
To: | Stefan Zweig <stefanzweig1881(at)web(dot)de> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: show index from [table] |
Date: | 2007-06-11 06:28:13 |
Message-ID: | DE38B02B-CC33-409E-88AD-6EB53F504E88@skype.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
On 08.06.2007, at 18:25, Stefan Zweig wrote:
>
> but actually i would need the information from within a (postgres)
> sql-query. is there a possibility to get information about the
> indices which have been created on a table?
>
> if there is not, it might be sufficient for me to get the create
> index strings, such like you get, when viewing a table in pgAdmin:
you can turn on echoing of psql commands sent to server with:
psql -E dbname
from there you can get the queries needed:
find the oid of table (unique object id)
********* QUERY **********
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(miljon)$'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
find table indices (replace the oid by the value found with your
previous query)
********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
pg_catalog.pg_index i
WHERE c.oid = '16427' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname
here is my sample output:
relname | indisprimary | indisunique | indisclustered |
indisvalid |
pg_get_indexdef | reltablespace
--------------+--------------+-------------+----------------
+------------
+------------------------------------------------------------
+---------------
miljon_pkey | t | t | f |
t | CREATE UNIQUE INDEX miljon_pkey ON miljon USING btree
(id) | 0
idx_blahblah | f | f | f |
t | CREATE INDEX idx_blahblah ON miljon USING btree
(sisu) | 0
(2 rows)
hope this helps
Kristo
From | Date | Subject | |
---|---|---|---|
Next Message | Sabin Coanda | 2007-06-11 14:31:03 | cluster index on primary key |
Previous Message | Shoaib Mir | 2007-06-09 17:21:57 | Re: search path within trigger |