Handling schema names starting with numeric during maintenance operation

posted Apr 28, 2017, 10:29 AM by Sachchida Ojha   [ updated Apr 28, 2017, 10:31 AM ]
The simplest solution of this problem to put your schema name in double quotes. I have seen cases where there are tables with UPPER CASE. Your maintenance script will fail for that table if you do not include in double quotes. Here is the script that I use to pass ton 
VACUUM and ANALYZE command command/ ( non catalog tables).

SELECT '"'||b.nspname ||'"'|| '.' ||'"'||relname||'"' FROM pg_class c,pg_namespace b WHERE c.relnamespace=b.oid and b.nspname NOT IN ('gp_toolkit', 'information_schema', 'pg_catalog') AND c.relkind='r'and c.relstorage!='x' ORDER BY b.nspname;
Comments