A recent tweet from Kevin Closson outlined that in PostgreSQL it might be confusing if something is an index or table. Why is it like that? Lets have a look and start be re-building the example from Kevin:
For getting into the same situation Kevin described we need something like this:
postgres=# create table base4(custid int, custname varchar(50)); CREATE TABLE postgres=# create index base4_idx on base4(custid); CREATE INDEX
Assuming that we forgot that we created such an index and come back later and try to create it again we have exactly the same behavior:
postgres=# create index base4_idx on base4(custid); ERROR: relation "base4_idx" already exists postgres=# drop table base4_idx; ERROR: "base4_idx" is not a table HINT: Use DROP INDEX to remove an index. postgres=#
They keyword here is “relation”. In PostgreSQL a “relation” does not necessarily mean a table. What you need to know is that PostgreSQL stores everything that looks like a table/relation (e.g. has columns) in the pg_class catalog table. When we check our relations there:
postgres=# select relname from pg_class where relname in ('base4','base4_idx'); relname ----------- base4 base4_idx (2 rows)
… we can see that both, the table and the index, are somehow treated as a relation. The difference is here:
postgres=# ! cat a.sql select a.relname , b.typname from pg_class a , pg_type b where a.relname in ('base4','base4_idx') and a.reltype = b.oid; postgres=# i a.sql relname | typname ---------+--------- base4 | base4 (1 row)
Indexes do not have an entry in pg_type, tables have. What is even more interesting is, that the “base4” table is a type itself. This means for every table you create a composite type is created as well that describes the structure of the table. You can even link back to pg_class:
postgres=# select typname,typrelid from pg_type where typname = 'base4'; typname | typrelid ---------+---------- base4 | 32901 (1 row) postgres=# select relname from pg_class where oid = 32901; relname --------- base4 (1 row)
When you want to know what type a relation is of the easiest way is to ask like this:
postgres=# select relname,relkind from pg_class where relname in ('base4','base4_idx'); relname | relkind -----------+--------- base4 | r base4_idx | i (2 rows)
… where:
- r = ordinary table
- i = index
- S = sequence
- t = TOAST table
- m = materialized view
- c = composite type
- f = foreign table
- p = partitioned table
Of course there are also catalog tables for tables and indexes, so you can also double check there. Knowing all this the message is pretty clear:
postgres=# create index base4_idx on base4(custid); ERROR: relation "base4_idx" already exists postgres=# drop relation base4_idx; ERROR: syntax error at or near "relation" LINE 1: drop relation base4_idx; ^ postgres=# drop table base4_idx; ERROR: "base4_idx" is not a table HINT: Use DROP INDEX to remove an index. postgres=#
PostgreSQL finally is telling you that “base4_idx” is an index and not a table which is fine. Of course you could think that PostgreSQL should to that on its own but it is also true: When you want to drop something, you should be sure on what you really want to drop.