Check for existence of index

From: David Rysdam <drysdam(at)ll(dot)mit(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Check for existence of index
Date: 2005-04-05 16:41:03
Message-ID: 4252BF9F.3030009@ll.mit.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a script that automatically creates my database objects. In
order to automatically create indexes, it needs to first make sure they
don't exist.

For things like tables, this is easy:

select * from information_schema.tables where table_schema =
"<myschema>" and table_name = "<tablename>"

But for indexes it is hard for some reason. There's a catalog table
"pg_index", but it doesn't have index, schema or table names. I
eventually found them in pg_class but the table and schema names aren't
there.

After some searching around, I came across this very strange (to me,
anyway) "::regclass" thing that let me do this:

select * from pg_catalog.pg_index where indexrelid =
'schema.index'::regclass

I'm not really clear what's that doing, but in any case it still isn't
what I want. That query returns information when the index exists but
errors out when the index doesn't exist. Is there a way I can get a
non-erroring query on either condition that will tell me if an index
exists on a given table in a given schema?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Wes 2005-04-05 16:47:01 Re: Vacuum time degrading
Previous Message Hannes Dorbath 2005-04-05 16:40:40 Re: Postmaster running out of discspace; Data corruption?