CREATE INDEX
An SQL command for creating an index
CREATE INDEX is a DDL command for creating an index.
CREATE INDEX has always been present in PostgreSQL.
Change history
- PostgreSQL 15
UNIQUE [ NULLS [ NOT ] DISTINCT ]syntax added (commit 94aa7cc5)
- PostgreSQL 13
- support for opclass parameters added (commit 911e7020)
- implicit support for deprecated pre-PostgreSQL 8.0 opclass names removed (commit 84eca14b)
deduplicate_itemsstorage parameter added (commit 0d861bbb)
- PostgreSQL 12
- progress of
CREATE INDEXcan be tracked withpg_stat_progress_create_index(commit ab0dfc96)
- progress of
- PostgreSQL 11
- support for covering indexes via the
INCLUDEclause added (commit 8224de4f)
- support for covering indexes via the
- PostgreSQL 9.5
IF NOT EXISTSsyntax added (commit 08309aaf)
- PostgreSQL 9.1
- option
COLLATEadded (commit 414c5a2e)
- option
- PostgreSQL 8.3
- options
ASC/DESC/NULLS FIRST/NULLS LAST(for btree indexes) added (commit 44317582)
- options
- PostgreSQL 8.2
- PostgreSQL 8.0
- option
TABLESPACEadded (commit 2467394e)
- option
- PostgreSQL 7.2
- support for partial indexes added (initial commit f31dc0ad)
- PostgreSQL 6.1
- proper multicolumn index creation (commit 65019fcf)
Examples
Basic usage example for CREATE INDEX:
postgres=# CREATE INDEX ON foo (bar_id);
CREATE INDEX
postgres=# \d foo
Table "public.foo"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
bar_id | integer | | not null |
Indexes:
"foo_pkey" PRIMARY KEY, btree (id)
"foo_bar_id_idx" btree (bar_id)
Foreign-key constraints:
"foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES bar(id)
References
- PostgreSQL documentation: CREATE INDEX
Useful links
- Using Postgres CREATE INDEX: Understanding operator classes, index types & more - August 2021 blog article by pganalyze