An application includes code that optionally does some admin tasks. Rather than just try to create objects, it's presumably better to test if they exist before attempting to create them.
So far I can check
if a function exists with:
ps = dbConn.prepareStatement("select count(routine_name) from information_schema.routines"
+ " where routine_catalog = ?
+ " and routine_name = ?");
if a table exists with:
ps = dbConn.prepareStatement("select count(*) from pg_tables where tablename = ?"); // and schemaname = ? and tableowner = ?");
if a column in a table exists with:
ps = dbConn.prepareStatement("select count(a.attname)"
+ " from pg_user u, pg_type t, pg_attribute a, pg_type n "
+ " where u.usesysid = t.typowner "
+ " and t.typrelid = a.attrelid and t.typtype = 'c' and not (t.typname ~* 'pg_') "
+ " and n.typelem = a.atttypid "
+ " and substr(n.typname, 1, 1) = '_' "
+ " and a.attnum > 0 "
+ " and t.typname = ?"
+ " and a.attname = ?");
Are there equivalent ways to check for indeces and constraints?