pg_get_indexdef()

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.

Usage

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

Examples

Assuming the following table definition:

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

postgres=# \d foo
                Table "public.foo"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
Indexes:
    "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);
                       pg_get_indexdef                       
-------------------------------------------------------------
 CREATE UNIQUE INDEX foo_pkey ON public.foo 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);
                   pg_get_indexdef                    
------------------------------------------------------
 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) ;
                       pg_get_indexdef                        
--------------------------------------------------------------
 CREATE INDEX bar_cols ON public.bar 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);
 pg_get_indexdef 
-----------------
 
(1 row)

Categories

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()