How to force drop index relation in postgresql?

Question

In PostgreSQL 9.2 / PostGIS 2.0.2 I had an index on a spatial column, created with

CREATE INDEX tiger_data_sld_the_geom_gist ON tiger_data.sld USING gist(the_geom);

Subsequently dropped the index with

DROP INDEX tiger_data_sld_the_geom_gist;

But now, when I try to recreate, I get this error:

#  CREATE INDEX tiger_data_sld_the_geom_gist ON tiger_data.sld USING gist(the_geom);
ERROR:  relation "tiger_data_sld_the_geom_gist" already exists

Dropping again doesn't work. It says that the index doesn't exist:

# DROP INDEX tiger_data_sld_the_geom_gist;
ERROR:  index "tiger_data_sld_the_geom_gist" does not exist

I haven't found the relation "tiger_data_sld_the_geom_gist" in any list of database objects, have tried DROP TABLE, and searched around for solutions.

What is this mystery relation "tiger_data_sld_the_geom_gist", and how do I remove it so that I can create the index?

Edit:

Also have tried restarting the server, and dumping / dropping / reloading the table (dropped with CASCADE).

Answer 1

Unless you are setting the search_path GUC to (or at least including) the tiger_data schema, you need to add the schema to the index name to issue the DROP INDEX (I'd use it in any case for safety):

DROP INDEX tiger_data.tiger_data_sld_the_geom_gist;

That's because the index always go to the same schema of the table it belongs to. If the above doesn't solve your problem, you can check if this relation name exists and on each schema it is in with the following:

SELECT r.relname, r.relkind, n.nspname
FROM pg_class r INNER JOIN pg_namespace n ON r.relnamespace = n.oid
WHERE r.relname = 'tiger_data_sld_the_geom_gist';

It will return the kind (i for indexes, r for tables, S for sequences and v for views) of any relation that has the name tiger_data_sld_the_geom_gist and name of the schema it belongs to.