Is there an efficient way to check whether a particular index or constraint exists?

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?

Responses

Browse pgsql-admin by date

  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?