From: | jonathan(dot)lister(at)vaisala(dot)com |
---|---|
To: | <pgsql-admin(at)postgresql(dot)org> |
Subject: | Is there an efficient way to check whether a particular index or constraint exists? |
Date: | 2006-04-28 10:13:13 |
Message-ID: | D3081BDF8E6A5D40A4E82155E83C865E132525@birdx1.corp.vaisala.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Szabolcs BALLA | 2006-04-28 12:03:17 | Re: Autovacuum probably not working? |
Previous Message | Szabolcs BALLA | 2006-04-28 09:07:29 | Autovacuum probably not working? |