Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename
От | Jean-Yves F. Barbier |
---|---|
Тема | Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename |
Дата | |
Msg-id | 20120112012812.0cf2a7b5@anubis.defcon1 обсуждение исходный текст |
Ответ на | Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename (Bob Branch <bbranch@nabancard.com>) |
Список | pgsql-novice |
On Wed, 11 Jan 2012 18:37:23 -0500 Bob Branch <bbranch@nabancard.com> wrote: > > SELECT i.indexname, i.indexdef FROM pg_indexes i > INNER JOIN pg_index p ON p.indrelid = i.tablename::regclass::oid > WHERE i.schemaname = 'public' > AND i.tablename = 'tablename_goes_here' > AND p.indisprimary = false First, your query is incorrect: on my test DB is returns 156 rows for 12 (non PK) indexes on a table. Second, you must enclose unregular table names into either simple & double quotes and use the same kinda query as '\d+ mytable': SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered, i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), pg_catalog.pg_get_constraintdef(con.oid, true), contype, condeferrable, condeferred, c2.reltablespace FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x')) WHERE c.oid = '"MyUnregularlyNamedTABLE"'::regclass::oid AND c.oid = i.indrelid AND i.indexrelid = c2.oid AND i.indisprimary = 'f' ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname; (also work with regular names). JY -- The greatest disloyalty one can offer to great pioneers is to refuse to move an inch from where they stood.
В списке pgsql-novice по дате отправления: