pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedshow index from [table]
4+ messages / 4 participants
[nested] [flat]
* show index from [table]
@ 2007-06-08 15:25 Stefan Zweig <stefanzweig1881@web.de>
0 siblings, 2 replies; 4+ messages in thread
From: Stefan Zweig @ 2007-06-08 15:25 UTC (permalink / raw)
To: pgsql-sql
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. unfortunately i 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 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:
-- 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: show index from [table]
@ 2007-06-08 15:47 Andreas Kretschmer <akretschmer@spamfence.net>
parent: Stefan Zweig <stefanzweig1881@web.de>
1 sibling, 1 reply; 4+ messages in thread
From: Andreas Kretschmer @ 2007-06-08 15:47 UTC (permalink / raw)
To: pgsql-sql
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°
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: show index from [table]
@ 2007-06-08 19:13 Ireneusz Pluta <ipluta@wp.pl>
parent: Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 4+ messages in thread
From: Ireneusz Pluta @ 2007-06-08 19:13 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
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';
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: show index from [table]
@ 2007-06-11 06:28 Kristo Kaiv <kristo.kaiv@skype.net>
parent: Stefan Zweig <stefanzweig1881@web.de>
1 sibling, 0 replies; 4+ messages in thread
From: Kristo Kaiv @ 2007-06-11 06:28 UTC (permalink / raw)
To: Stefan Zweig <stefanzweig1881@web.de>; +Cc: 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-06-11 06:28 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-08 15:25 show index from [table] Stefan Zweig <stefanzweig1881@web.de>
2007-06-08 15:47 ` Andreas Kretschmer <akretschmer@spamfence.net>
2007-06-08 19:13 ` Ireneusz Pluta <ipluta@wp.pl>
2007-06-11 06:28 ` Kristo Kaiv <kristo.kaiv@skype.net>
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