Postgres 14

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

CREATE TABLE employee (
  id INT PRIMARY KEY NOT NULL,
  name VARCHAR(20),
  salary INT NOT NULL,
  bonus INT NOT NULL
);

CREATE UNIQUE INDEX idx_employee_1 ON employee (name);

CREATE INDEX idx_employee_2 ON employee ((salary + bonus) desc, name);

CREATE TABLE



CREATE INDEX



CREATE INDEX


select *
from pg_indexes
where tablename = 'employee'


schemaname
tablename
indexname
tablespace
indexdef


public
employee
employee_pkey
null
CREATE UNIQUE INDEX employee_pkey ON public.employee USING btree (id)

public
employee
idx_employee_1
null
CREATE UNIQUE INDEX idx_employee_1 ON public.employee USING btree (name)

public
employee
idx_employee_2
null
CREATE INDEX idx_employee_2 ON public.employee USING btree (((salary + bonus)) DESC, name)



SELECT 3


select *
from pg_index
where indrelid = 'employee'::regclass


indexrelid
indrelid
indnatts
indnkeyatts
indisunique
indisprimary
indisexclusion
indimmediate
indisclustered
indisvalid
indcheckxmin
indisready
indislive
indisreplident
indkey
indcollation
indclass
indoption
indexprs
indpred


8663737
8663734
1
1
t
t
f
t
f
t
f
t
t
f
1
0
1978
0
null
null

8663739
8663734
1
1
t
f
f
t
f
t
f
t
t
f
2
100
3126
0
null
null

8663740
8663734
2
2
f
f
f
t
f
t
f
t
t
f
0 2
0 100
1978 3126
3 0
({OPEXPR :opno 551 :opfuncid 177 :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 221} {VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 4 :location 230}) :location 228})
null



SELECT 3

schemaname	tablename	indexname	tablespace	indexdef
public	employee	employee_pkey	null	CREATE UNIQUE INDEX employee_pkey ON public.employee USING btree (id)
public	employee	idx_employee_1	null	CREATE UNIQUE INDEX idx_employee_1 ON public.employee USING btree (name)
public	employee	idx_employee_2	null	CREATE INDEX idx_employee_2 ON public.employee USING btree (((salary + bonus)) DESC, name)

indexrelid	indrelid	indnatts	indnkeyatts	indisunique	indisprimary	indisexclusion	indimmediate	indisclustered	indisvalid	indcheckxmin	indisready	indislive	indisreplident	indkey	indcollation	indclass	indoption	indexprs	indpred
8663737	8663734	1	1	t	t	f	t	f	t	f	t	t	f	1	0	1978	0	null	null
8663739	8663734	1	1	t	f	f	t	f	t	f	t	t	f	2	100	3126	0	null	null
8663740	8663734	2	2	f	f	f	t	f	t	f	t	t	f	0 2	0 100	1978 3126	3 0	({OPEXPR :opno 551 :opfuncid 177 :opresulttype 23 :opretset false :opcollid 0 :inputcollid 0 :args ({VAR :varno 1 :varattno 3 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 3 :location 221} {VAR :varno 1 :varattno 4 :vartype 23 :vartypmod -1 :varcollid 0 :varlevelsup 0 :varnosyn 1 :varattnosyn 4 :location 230}) :location 228})	null