Shareware software
Indexes built on a PostgreSQL table
The node displays a list of indexes built on the columns of the selected PostgreSQL table.
Menu
Copy name - copy the index name to the clipboard
Copy full name - copy the full name of the index to the clipboard
Reindex - generate command code for index rebuilding
Get DDL - generate command code to create an index
Create code [Drop] - generate a command code for deleting an index
Child nodes
Column - the names of the columns on which the index is built
Primary - flag indicating that the index is the primary key of the table
Unique - a pointer to whether the index is unique
Clustered - if true, the table was last clustered at this index
Valid - if true, the index can be used in queries.
Condeferrable - is the constraint deferrable?
Condeferred - is the constraint deferred by default?
Replident - if true, this index is selected as the "replica ID"
select relname, oid, indisprimary, indisunique,
indisclustered, indisvalid, pg_get_indexdef,
pg_get_constraintdef, contype,
condeferrable, condeferred, indisreplident,
reltablespace,
string_agg(pg_get_indexdef_, ',') "col_lict"
from (
SELECT c2.relname,
c2.oid,
i.indisprimary::int,
i.indisunique::int,
i.indisclustered::int,
i.indisvalid::int,
pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) "pg_get_indexdef",
pg_catalog.pg_get_constraintdef(con.oid, false),
contype,
condeferrable::int,
condeferred::int,
i.indisreplident::int,
c2.reltablespace,
pg_catalog.pg_get_indexdef(c2.oid, (information_schema._pg_expandarray(i.indkey)).n, false) "pg_get_indexdef_"
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = $$TABLE_OID AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname) a
group by relname, oid, indisprimary, indisunique,
indisclustered, indisvalid, pg_get_indexdef, pg_get_constraintdef,
contype, condeferrable, condeferred, indisreplident, reltablespace