Handling schema names starting with numeric during maintenance operation

Post date: Oct 23, 2014 12:9:30 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;

cheers!