| 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: | Whole Thread | Raw Message | 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?
| 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? |