Cloud

pg_get_indexdef

The pg_get_indexdef() is a system catalog information function that reconstructs the PostgreSQL command used to retrieve the definition of a specified index.

Syntax

The pg_get_indexdef() function has three available syntax versions:

pg_get_indexdef(index_oid)
pg_get_indexdef(index_oid, column_oid)
pg_get_indexdef(index_oid, column_oid, pretty_bool)

Parameters

The parameters required to execute this function:

  • index_oid: Specifies the object identifier (OID) of the index.

  • column_oid: Indicates the column number within the index (starting from 1).

  • pretty_bool: Controls whether to format the output in a human-readable way.

Examples

Create a sample table and an index for it:

CREATE TABLE sample_table(col int);
CREATE INDEX sample_index ON sample_table(col);

Once that is done, get the OID of the index:

SELECT oid FROM pg_class WHERE relname = 'sample_index';
 oid
------
 16387

Retrieve the index definition:

SELECT pg_get_indexdef(16387);

The query returns the reconstructed definition:

                    pg_get_indexdef
-------------------------------------------------------
 CREATE INDEX sample_index ON public.sample_table(col)
(1 row)