​
Overview

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

Here are the two available syntax versions of the pg_get_indexdef() function: 
pg_get_indexdef(index_oid, column_oid)

​
Parameters

The following parameters are 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

​
Example

In this example we’ll start from creating 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, we can get the OID of the index in a following way 
SELECT oid FROM pg_class WHERE relname = 'sample_index';

 oid
------
 16387
As the last step we’re going to retrieve the index definition 
SELECT pg_get_indexdef(16387);
Here is the reconstructed definition: 
                    pg_get_indexdef
-------------------------------------------------------
 CREATE INDEX sample_index ON public.sample_table(col)
(1 row)