Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename
От | Bob Branch |
---|---|
Тема | Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename |
Дата | |
Msg-id | 4F0E1D33.7050909@nabancard.com обсуждение исходный текст |
Ответы |
Re: Join troubles between pg_index and pg_indexes with
capitalization in pg_indexes.tablename
Re: Join troubles between pg_index and pg_indexes with capitalization in pg_indexes.tablename |
Список | 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@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.
В списке pgsql-novice по дате отправления: