From: | David Rysdam <drysdam(at)ll(dot)mit(dot)edu> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Check for existence of index |
Date: | 2005-04-05 17:19:13 |
Message-ID: | 4252C891.3060601@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
And another thing, can't I do this:
create table s.a (blah);
create table s.b (blah);
create index myindex on s.a(blah);
create index myindex on s.b(blah);
? When I drop them I have to specify the schema name, so presumably it
tracks them that way. Why can't I have the same index name be on
different tables?
David Rysdam wrote:
> 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Joseph Shraibman | 2005-04-05 17:25:01 | Re: contrib/dbsize |
Previous Message | Michael Fuhr | 2005-04-05 17:03:00 | Re: contrib/dbsize |