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

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@nabancard.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.

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.

Bob Branch <bbranch@nabancard.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