Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TX (A INT, B INT, C INT, D INT, E VARCHAR(32), F INT, G DATE, H BOOLEAN);

CREATE INDEX X1 ON TX (A DESC, B ASC, C DESC);

CREATE INDEX X2 ON TX (A, C) INCLUDE (D, E, F);

CREATE INDEX X3 ON TX (B) WHERE G > '2020-01-01';

CREATE TABLE



CREATE INDEX



CREATE INDEX



CREATE INDEX


SELECT a.attname,
  a.attnum<=(SELECT i.indnkeyatts 
             FROM pg_catalog.pg_index i 
             WHERE i.indexrelid='X2'::regclass::oid) AS is_key
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'X2'::regclass::oid 
AND a.attnum > 0 
AND NOT a.attisdropped
ORDER BY a.attnum;


attname
is_key


a
t

c
t

d
f

e
f

f
f



SELECT 5


SELECT pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)
FROM pg_catalog.pg_index i
WHERE i.indexrelid = 'X3'::regclass::oid;


pg_get_expr


g > '2020-01-01'::date



SELECT 1


SELECT --i.indexrelid, 
       s.nspname AS "TABLE_SCHEMA", tc.relname AS "TABLE_NAME",
       ic.relname AS "INDEX_NAME", m.amname as "INDEX_TYPE",
       (SELECT STRING_AGG(a.attname ||  
                        CASE 
                           WHEN indoption[a.attnum-1] = 3 
                              THEN ' DESC' 
                           ELSE ' ASC' 
                        END ||
                        CASE 
                           WHEN i.indcollation[attnum-1] = 0 
                              THEN '' 
                           ELSE ' COLLATE ' ||
                                 (SELECT collname  
                                FROM   pg_collation  
                                WHERE  oid = i.indcollation[attnum-1])
                        END,  ', ') 
        FROM   pg_attribute AS a
        WHERE  a.attrelid = i.indexrelid) AS "KEY_COLUMN_LIST",
       i.indisunique AS "UNIQUE",
       i.indisprimary AS "PRIMARY_KEY",
       i.indisclustered AS "CLUSTERED",
       i.indisvalid AS "VALID",
       CASE k.contype 
          WHEN 'c' THEN 'CHECK'
          WHEN 'f' THEN 'FOREIGN KEY'
          WHEN 'p' THEN 'PRIMARY KEY'
          WHEN 'u' THEN 'UNIQUE'
       END AS "CONSTRAINT_TYPE",
       (SELECT STRING_AGG(a2.attname,  ', ') 
        FROM   pg_attribute AS a2
        WHERE  a2.attrelid = i.indexrelid
          AND  a2.attnum > i.indnkeyatts) AS "INCLUDE_COLUM_LIST",
        pg_catalog.pg_get_expr(i.indpred, i.indrelid, true) AS "FILTER_DEFINITION",
       pg_get_indexdef(i.indexrelid) AS "INDEX_DEFINITION"
FROM   pg_index AS i           -- index
       JOIN pg_class AS ic       -- classe index
          ON i.indexrelid = ic.oid
       JOIN pg_class AS tc       -- classe table
          ON i.indrelid = tc.oid          
       JOIN pg_namespace AS s  -- schema
          ON tc.relnamespace = s.oid
       JOIN pg_am AS m         -- type d'index
          ON ic.relam = m.oid
       LEFT OUTER JOIN pg_constraint AS k   -- contraintes associées
          ON i.indexrelid = k.conindid AND i.indkey[0:indnkeyatts-1] = k.conkey
WHERE  m.amname = 'btree'
AND  ic.relname LIKE 'x%' --> to match with my sample
ORDER BY 1, 2, 3, 4


TABLE_SCHEMA
TABLE_NAME
INDEX_NAME
INDEX_TYPE
KEY_COLUMN_LIST
UNIQUE
PRIMARY_KEY
CLUSTERED
VALID
CONSTRAINT_TYPE
INCLUDE_COLUM_LIST
FILTER_DEFINITION
INDEX_DEFINITION


public
tx
x1
btree
a DESC, b ASC, c DESC
f
f
f
t
null
null
null
CREATE INDEX x1 ON public.tx USING btree (a DESC, b, c DESC)

public
tx
x2
btree
a ASC, c ASC
f
f
f
t
null
d, e, f
null
CREATE INDEX x2 ON public.tx USING btree (a, c) INCLUDE (d, e, f)

public
tx
x3
btree
b ASC
f
f
f
t
null
null
g > '2020-01-01'::date
CREATE INDEX x3 ON public.tx USING btree (b) WHERE (g > '2020-01-01'::date)



SELECT 3

TABLE_SCHEMA	TABLE_NAME	INDEX_NAME	INDEX_TYPE	KEY_COLUMN_LIST	UNIQUE	PRIMARY_KEY	CLUSTERED	VALID	CONSTRAINT_TYPE	INCLUDE_COLUM_LIST	FILTER_DEFINITION	INDEX_DEFINITION
public	tx	x1	btree	a DESC, b ASC, c DESC	f	f	f	t	null	null	null	CREATE INDEX x1 ON public.tx USING btree (a DESC, b, c DESC)
public	tx	x2	btree	a ASC, c ASC	f	f	f	t	null	d, e, f	null	CREATE INDEX x2 ON public.tx USING btree (a, c) INCLUDE (d, e, f)
public	tx	x3	btree	b ASC	f	f	f	t	null	null	g > '2020-01-01'::date	CREATE INDEX x3 ON public.tx USING btree (b) WHERE (g > '2020-01-01'::date)