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_items
storage parameter added (commit 0d861bbb)
- PostgreSQL 12
- progress of
CREATE INDEX
can be tracked withpg_stat_progress_create_index
(commit ab0dfc96)
- progress of
- PostgreSQL 11
- support for covering indexes via the
INCLUDE
clause added (commit 8224de4f)
- support for covering indexes via the
- PostgreSQL 9.5
IF NOT EXISTS
syntax added (commit 08309aaf)
- PostgreSQL 9.1
- option
COLLATE
added (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
TABLESPACE
added (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