Check for existence of index
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?
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:
Show quoted text
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'::regclassI'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
David Rysdam <drysdam@ll.mit.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
Tom Lane wrote:
David Rysdam <drysdam@ll.mit.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.htmlregards, 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).