Re: Check for existence of index

Tom Lane wrote:

>David Rysdam <drysdam(at)ll(dot)mit(dot)edu> 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).