{"id":10645,"date":"2017-11-19T16:54:13","date_gmt":"2017-11-19T15:54:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/is-it-an-index-a-table-or-what\/"},"modified":"2017-11-19T16:54:13","modified_gmt":"2017-11-19T15:54:13","slug":"is-it-an-index-a-table-or-what","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/is-it-an-index-a-table-or-what\/","title":{"rendered":"Is it an index, a table or what?"},"content":{"rendered":"

A recent tweet from Kevin Closson<\/a> 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:<\/p>\n

<\/p>\n

For getting into the same situation Kevin described we need something like this:<\/p>\n

\npostgres=# create table base4(custid int, custname varchar(50));\nCREATE TABLE\npostgres=# create index base4_idx on base4(custid);\nCREATE INDEX\n<\/pre>\n
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:<\/p>\n
\npostgres=# create index base4_idx on base4(custid);\nERROR:  relation \"base4_idx\" already exists\npostgres=# drop table base4_idx;\nERROR:  \"base4_idx\" is not a table\nHINT:  Use DROP INDEX to remove an index.\npostgres=# \n<\/pre>\n
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<\/a>. When we check our relations there:<\/p>\n
\npostgres=# select relname from pg_class where relname in ('base4','base4_idx');\n  relname  \n-----------\n base4\n base4_idx\n(2 rows)\n<\/pre>\n
… we can see that both, the table and the index, are somehow treated as a relation. The difference is here:<\/p>\n
\npostgres=# ! cat a.sql\nselect a.relname \n     , b.typname\n  from pg_class a\n     , pg_type b \n where a.relname in ('base4','base4_idx')\n   and a.reltype = b.oid;\npostgres=# i a.sql\n relname | typname \n---------+---------\n base4   | base4\n(1 row)\n<\/pre>\n
Indexes do not have an entry in pg_type<\/a>, 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:<\/p>\n
\npostgres=# select typname,typrelid from pg_type where typname = 'base4';\n typname | typrelid \n---------+----------\n base4   |    32901\n(1 row)\n\npostgres=# select relname from pg_class where oid = 32901;\n relname \n---------\n base4\n(1 row)\n<\/pre>\n
When you want to know what type a relation is of the easiest way is to ask like this:<\/p>\n
\npostgres=# select relname,relkind from pg_class where relname in ('base4','base4_idx');\n  relname  | relkind \n-----------+---------\n base4     | r\n base4_idx | i\n(2 rows)\n<\/pre>\n
… where:<\/p>\n