Re: Check for existence of index

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

5 апреля 2005 г. в 19:20:03

David Rysdam  writes:
> Why can't I have the same index name be on 
> different tables?

You can ... if they are in different schemas.  Indexes and tables share
the same namespace, ie, they must be unique within a schema.

As for your original question, you probably want something like

SELECT ... FROM pg_class c, pg_namespace n WHERE c.relnamespace = n.oid
  AND relname = 'indexname' AND nspname = 'schemaname' AND relkind = 'i';

If you actually want to verify that this index is on a specific table,
you'll need a more complicated join involving pg_index and a second
scan of pg_class.  See
http://www.postgresql.org/docs/8.0/static/catalogs.html

			regards, tom lane

Check for existence of index

От:

David Rysdam <drysdam@ll.mit.edu>

Дата:

5 апреля 2005 г. в 17:41:30

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 = 
"" and table_name = ""

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?

Re: Check for existence of index

От:

David Rysdam <drysdam@ll.mit.edu>

Дата:

5 апреля 2005 г. в 18:19:33

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 = 
> "" and table_name = ""
>
> 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@postgresql.org so that your
>      message can get through to the mailing list cleanly
>
>

Re: Check for existence of index

От:

David Rysdam <drysdam@ll.mit.edu>

Дата:

5 апреля 2005 г. в 19:30:28

Tom Lane wrote:

>David Rysdam  writes:
>  
>
>>Why can't I have the same index name be on 
>>different tables?
>>    
>>
>
>You can ... if they are in different schemas.  Indexes and tables share
>the same namespace, ie, they must be unique within a schema.
>
>As for your original question, you probably want something like
>
>SELECT ... FROM pg_class c, pg_namespace n WHERE c.relnamespace = n.oid
>  AND relname = 'indexname' AND nspname = 'schemaname' AND relkind = 'i';
>
>If you actually want to verify that this index is on a specific table,
>you'll need a more complicated join involving pg_index and a second
>scan of pg_class.  See
>http://www.postgresql.org/docs/8.0/static/catalogs.html
>
>			regards, tom lane
>  
>
Well, since I can't have more than one index of a given name in a schema 
anyway, I'll have to name them "$tablename_$indexname" or something, 
which means I won't have to verify they are on a particular table. 

Anyway, this query looks good.  I was getting lost in all the 
terminology ("namespace" vs "schema") data distributed all over (some 
stuff in pg_index, some in pg_class, etc).