pg_indexes

A system view listing the available indexes in the current database

pg_indexes is a system catalogue view listing the available indexes in the current database.

pg_indexes was added in PostgreSQL 6.4.

Definition by PostgreSQL version

pg_indexes (PostgreSQL 15)

            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

Documentation: pg_indexes

pg_indexes (PostgreSQL 14)

            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

Documentation: pg_indexes

pg_indexes (PostgreSQL 13)

            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

Documentation: pg_indexes

pg_indexes (PostgreSQL 12)

            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

Documentation: pg_indexes

pg_indexes (PostgreSQL 11)

            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

Documentation: pg_indexes

pg_indexes (PostgreSQL 10)

            View "pg_catalog.pg_indexes"
   Column   | Type | Collation | Nullable | Default 
------------+------+-----------+----------+---------
 schemaname | name |           |          | 
 tablename  | name |           |          | 
 indexname  | name |           |          | 
 tablespace | name |           |          | 
 indexdef   | text |           |          |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.6)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.5)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.4)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.3)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.2)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.1)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 9.0)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text |

Documentation: pg_indexes

pg_indexes (PostgreSQL 8.4)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef
   FROM pg_index x
   JOIN pg_class c ON c.oid = x.indrelid
   JOIN pg_class i ON i.oid = x.indexrelid
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
  WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char";

Documentation: pg_indexes

pg_indexes (PostgreSQL 8.3)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS tablespace, pg_get_indexdef(i.oid) AS indexdef
   FROM pg_index x
   JOIN pg_class c ON c.oid = x.indrelid
   JOIN pg_class i ON i.oid = x.indexrelid
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
  WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char";

Documentation: pg_indexes

pg_indexes (PostgreSQL 8.2)

 View "pg_catalog.pg_indexes"
   Column   | Type | Modifiers 
------------+------+-----------
 schemaname | name | 
 tablename  | name | 
 indexname  | name | 
 tablespace | name | 
 indexdef   | text | 
View definition:
 SELECT n.nspname AS schemaname, c.relname AS tablename, i.relname AS indexname, t.spcname AS "tablespace", pg_get_indexdef(i.oid) AS indexdef
   FROM pg_index x
   JOIN pg_class c ON c.oid = x.indrelid
   JOIN pg_class i ON i.oid = x.indexrelid
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
   LEFT JOIN pg_tablespace t ON t.oid = i.reltablespace
  WHERE c.relkind = 'r'::"char" AND i.relkind = 'i'::"char";

Documentation: pg_indexes

Change history

PostgreSQL 8.0
- column tablespace added (commit 4690cc9c)
PostgreSQL 7.3
- column schemaname added (commit 3b78826b)
PostgreSQL 6.4
- added (commit f93b6974)

Examples

Sample row from pg_indexes:

postgres=# SELECT * FROM pg_indexes WHERE schemaname = 'public'\gx
-[ RECORD 1 ]-----------------------------------------------------------
schemaname | public
tablename  | foo
indexname  | foo_pkey
tablespace | 
indexdef   | CREATE UNIQUE INDEX foo_pkey ON public.foo USING btree (id)

References

PostgreSQL documentation: pg_indexes

Version charts

Useful overviews

Useful links

Contents

pg_indexes

Definition by PostgreSQL version

pg_indexes (PostgreSQL 15)

pg_indexes (PostgreSQL 14)

pg_indexes (PostgreSQL 13)

pg_indexes (PostgreSQL 12)

pg_indexes (PostgreSQL 11)

pg_indexes (PostgreSQL 10)

pg_indexes (PostgreSQL 9.6)

pg_indexes (PostgreSQL 9.5)

pg_indexes (PostgreSQL 9.4)

pg_indexes (PostgreSQL 9.3)

pg_indexes (PostgreSQL 9.2)

pg_indexes (PostgreSQL 9.1)

pg_indexes (PostgreSQL 9.0)

pg_indexes (PostgreSQL 8.4)

pg_indexes (PostgreSQL 8.3)

pg_indexes (PostgreSQL 8.2)

Change history

Examples

References

Categories

See also