pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is there an efficient way to check whether a particular index or constraint exists?
2+ messages / 2 participants
[nested] [flat]

* Is there an efficient way to check whether a particular index or constraint exists?
@ 2006-04-28 10:13 jonathan.lister@vaisala.com
  2006-04-28 14:03 ` Re: Is there an efficient way to check whether a particular Ben K. <bkim@coe.tamu.edu>
  0 siblings, 1 reply; 2+ messages in thread

From: jonathan.lister@vaisala.com @ 2006-04-28 10:13 UTC (permalink / raw)
  To: 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?
 

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Is there an efficient way to check whether a particular
  2006-04-28 10:13 Is there an efficient way to check whether a particular index or constraint exists? jonathan.lister@vaisala.com
@ 2006-04-28 14:03 ` Ben K. <bkim@coe.tamu.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: Ben K. @ 2006-04-28 14:03 UTC (permalink / raw)
  To: jonathan.lister@vaisala.com; +Cc: pgsql-admin

> Are there equivalent ways to check for indeces and constraints?

pg_constraint and pg_indexes?

Regards,
Ben



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2006-04-28 14:03 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-28 10:13 Is there an efficient way to check whether a particular index or constraint exists? jonathan.lister@vaisala.com
2006-04-28 14:03 ` Re: Is there an efficient way to check whether a particular Ben K. <bkim@coe.tamu.edu>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox