Thread: show index from [table]

hi list,

currently i am switching from mysql to pgsql, so i am a bit new to postgres' syntax.

at the moment i am looking in postgres for something which is similar to SHOW INDEX FROM [table] in mysql.
unfortunatelyi could not find anything satisfying relating to this issue. 
 

i have found out, that there is the -di option with psql.

but actually i would need the information from within a (postgres) sql-query. is there a possibility to get information
aboutthe 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
inpgAdmin:
 

-- Index: g_g114_b_idx

-- DROP INDEX g_g114_b_idx;

CREATE INDEX g_g114_b_idx ON g_g114 USING btree (b);

and parse them manually.

is that possible in any way?

maybe there is something similar to SHOW CREATE TABLE (as in MySQL) in postgresql.


thanks in advance,

stefan
_______________________________________________________________
SMS schreiben mit WEB.DE FreeMail - einfach, schnell und
kostenguenstig. Jetzt gleich testen! http://f.web.de/?mc=021192

Stefan Zweig <stefanzweig1881@web.de> schrieb:

> hi list,
> 
> currently i am switching from mysql to pgsql, so i am a bit new to
> postgres' syntax.

You are welcome.

> 
> at the moment i am looking in postgres for something which is similar
> to SHOW INDEX FROM [table] in mysql. unfortunately i could not find
> anything satisfying relating to this issue. 
> 
> i have found out, that there is the -di option with psql.

Yes, right.

> 
> 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?

Yes, of corse. Please start psql with the -E - option. Now you can see
the underlying sql-statement for commands like \di.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andreas Kretschmer napisał(a):
>
> Yes, right.
>
>   
>> 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?
>>     
>
> Yes, of corse. Please start psql with the -E - option. Now you can see
> the underlying sql-statement for commands like \di.
>
>
>   
... or even better:
select * from pg_indexes where tablename = 'your_table';

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