Skip site navigation (1) Skip section navigation (2)

Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename

From: "Jean-Yves F(dot) Barbier" <12ukwn(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename
Date: 2012-01-12 00:28:12
Message-ID: 20120112012812.0cf2a7b5@anubis.defcon1 (view raw or flat)
Thread:
Lists: pgsql-novice
On Wed, 11 Jan 2012 18:37:23 -0500
Bob Branch <bbranch(at)nabancard(dot)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.

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2012-01-12 00:51:31
Subject: Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename
Previous:From: Daniel StaalDate: 2012-01-12 00:28:00
Subject: Re: When should log events be captured in a database?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group