CREATE INDEX

​

Syntax

create_index ::= CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ IF NOT EXISTS ] index_name
                 ON table_name [ USING method ]
                 ( index_element [, ...] )
                 [ WITH ( storage_parameter [= value] [, ...] ) ]
                 [ WHERE condition ]

index_name ::= name

table_name ::= [schema.]name

method ::= btree | hash | gist | spgist | gin | brin

storage_parameter ::= name

index_element ::= column_name [ direction ] [ operator_class ]
                | ( expression ) [ direction ] [ operator_class ]

direction ::= ASC | DESC

operator_class ::= name

• Index types:
  • Regular indexes
  • UNIQUE indexes for enforcing uniqueness
  • Primary key indexes (handled with constraints)
• Index methods: btree (default), hash, gist, spgist, gin, brin
• Concurrent creation: CONCURRENTLY option for creating indexes without blocking writes
• Columns and expressions:
  • Simple column indexes
  • Multi-column indexes
  • Expression/functional indexes (e.g., LOWER(column), JSON operators)
• Sort direction: ASC (default) or DESC for each column
• Operator classes: Custom operator classes for specialized indexing
• Partial indexes: WHERE clause for indexing subset of rows
• Storage parameters: WITH clause for index storage parameters (reloptions), e.g. WITH (fillfactor=90, deduplicate_items=true)
• Schema qualification: Indexes can be created in specific schemas

​

Canonical Format

CREATE [UNIQUE] INDEX [CONCURRENTLY] IF NOT EXISTS index_name 
ON [schema.]table_name [USING method] (
    column_or_expression[ direction][,
    ...]
)[ WITH (storage_parameter=value[, ...])][ WHERE condition];

• Always includes IF NOT EXISTS to prevent errors on re-running
• Includes UNIQUE when the index enforces uniqueness
• Includes CONCURRENTLY when specified (cannot run in a transaction)
• Only includes USING method when not btree (the default)
• Only includes direction when not ASC (the default)
• JSON expressions are wrapped in double parentheses: ((data->>'key'))
• Storage parameters are included in a WITH (...) clause, placed before the WHERE clause
• Partial index WHERE clause is included when present
• For DROP operations: DROP INDEX IF EXISTS [schema.]index_name;

• Regular index creation can run in a transaction
• CREATE INDEX CONCURRENTLY cannot run in a transaction and will be executed separately