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

From: Bob Branch <bbranch(at)nabancard(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename
Date: 2012-01-11 23:37:23
Message-ID: 4F0E1D33.7050909@nabancard.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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.).

This works to get the right records from pg_indexes if I strip out the
join, but with the join in place Postgres does as it typically does when
you try to use a relation with capitalization in it without quoting. It
converts it to lower case, and I get an error like this (working with a
table called Status):

ERROR: relation "status" does not exist

The problem is similar, but different for tables like "Sales Rep" with a
space in the name:

ERROR: invalid name syntax

Is there a way I can modify this query to work with tables that require
special quoting for Postgres not to mangle their names? It'd be simple
if I didn't need to worry about excluding the PK from the results, but
this is part of a script that's dropping and re-creating all the indexes
prior to/after a COPY to increase performance, so dropping the PK index
is...sub-optimal, at best.

--
Bob Branch
Database Administrator
North American Bancard
250 Stevenson Hwy
Troy, MI 48083

bbranch(at)nabancard(dot)com
248-269-6000

CONFIDENTIALITY NOTICE
This e-mail message and any attachments are only for the use of the
intended recipient and may contain information that is privileged,
confidential or
exempt from disclosure under applicable law. If you are not the intended
recipient, any disclosure, distribution or other use of this e-mail message
or attachments is prohibited. If you have received this e-mail message
in error, please delete and notify the sender immediately. Thank you.

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message James Hartley 2012-01-12 00:06:06 When should log events be captured in a database?
Previous Message Ioannis Anagnostopoulos 2012-01-11 12:11:13 Complex structure storage, better in temp table or array?