A function for generating an index definition

pg_get_indexdef() is a system function which generates the SQL required to recreate the specified index.

pg_get_indexdef() was added in PostgreSQL 6.4.


pg_get_indexdef ( index oid [, column integer, pretty boolean ] ) → text

Note that the returned text is reconstructed from metadata and is not the verbatim text of the original CREATE INDEX command.

Change history


Assuming the following table definition:

postgres=# CREATE TABLE foo (id INT PRIMARY KEY);

postgres=# \d foo
                Table ""
 Column |  Type   | Collation | Nullable | Default 
 id     | integer |           | not null | 
    "foo_pkey" PRIMARY KEY, btree (id)

pg_get_indexdef() can generate the CREATE INDEX command for the implicitly created foo_pkey index as:

postgres=# SELECT pg_get_indexdef('foo_pkey'::regclass);
 CREATE UNIQUE INDEX foo_pkey ON USING btree (id)
(1 row)

A less verbose definition can be created it the pretty parameter is TRUE (this requires providing the column parameter as 0):

postgres=# SELECT pg_get_indexdef('foo_pkey'::regclass, 0, true);
 CREATE UNIQUE INDEX foo_pkey ON foo USING btree (id)
(1 row)

If the column parameter is specified, only the name of the matching column in that position is returned:

postgres=# SELECT pg_get_indexdef('bar_cols'::regclass) ;
 CREATE INDEX bar_cols ON USING btree (col1, col2)
(1 row)

postgres=# SELECT pg_get_indexdef('bar_cols'::regclass, 1, false),
                  pg_get_indexdef('bar_cols'::regclass, 2, true);
 pg_get_indexdef | pg_get_indexdef 
 col1            | col2
(1 row)

In this case, the pretty parameter has no effect.

An empty string is returned if an invalid column parameter was provided:

postgres=# SELECT pg_get_indexdef('bar_cols'::regclass, -1, true);
(1 row)


Indexing, System catalogue, System function

See also

pg_get_constraintdef(), pg_get_functiondef(), pg_get_ruledef(), pg_get_statisticsobjdef(), pg_get_triggerdef(), pg_get_viewdef()