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

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bob Branch <bbranch(at)nabancard(dot)com>
Cc: 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:51:31
Message-ID: 697.1326329491@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Bob Branch <bbranch(at)nabancard(dot)com> writes:
> I've got a script in which I'm attempting to list all indexes that 
> aren't the PK for a given table.  The query I'm using for this is:

> 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

> This works for tables with what I'm sure we'd all consider to be 
> "proper" naming (all lower-case, underscores between words), but our 
> database unfortunately has vast numbers of tables that use camel case 
> and spaces in the table names (eg- "Status", "Sales Rep", etc.).

Not to mention that it's utterly unsafe if you have equal table names in
different schemas.  You should really not be using pg_indexes in this
query, as it is a view meant for human consumption, not something
helpful for basing further catalog joins on.  Try looking directly at
pg_class and pg_index.  In particular, if all you want is non-PK
indexes, you could just do something like

	select indexrelid::regclass from pg_index where not indisprimary;

If you need an explicit join to pg_class (perhaps because you have more
filter conditions than just "is it primary"), you should be joining
pg_class.oid to indexrelid or indrelid, rather than making something up
with table names.  The names are not suitable as join keys.

			regards, tom lane

In response to

pgsql-novice by date

Next:From: 2M Data SystemsDate: 2012-01-12 01:15:00
Subject: Compiling libpq with Borland bcc32
Previous:From: Jean-Yves F. BarbierDate: 2012-01-12 00:28:12
Subject: Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename

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