Created
May 9, 2016 14:09
-
-
Save chocobn69/57dcfaf383f63116bd85553a53a3ffbb to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- list index not in tablespace | |
SELECT i.relname as indname, | |
i.relowner as indowner, | |
idx.indrelid::regclass, | |
am.amname as indam, | |
idx.indkey, | |
pg_tablespace.spcname, | |
ARRAY( | |
SELECT pg_get_indexdef(idx.indexrelid, k + 1, true) | |
FROM generate_subscripts(idx.indkey, 1) as k | |
ORDER BY k | |
) as indkey_names, | |
idx.indexprs IS NOT NULL as indexprs, | |
idx.indpred IS NOT NULL as indpred | |
FROM pg_index as idx | |
JOIN pg_class as i | |
ON i.oid = idx.indexrelid | |
JOIN pg_am as am | |
ON i.relam = am.oid | |
JOIN pg_namespace as ns | |
ON ns.oid = i.relnamespace | |
AND ns.nspname = ANY(current_schemas(false)) | |
left join pg_tablespace on pg_tablespace.oid=i.reltablespace | |
where pg_tablespace.oid is null |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment