CREATE INDEX

CREATE INDEX

The CREATE INDEX statement constructs an index on the specified column(s) of the specified table.

Examples

Create a unique index films_id_idx on the column id of table films:

Query

CREATE UNIQUE INDEX films_id_idx ON films (id);

Create index s_idx that allows for duplicate values on column revenue of table films:

Query

CREATE INDEX s_idx ON films (revenue);

Create index if it does not yet exist:

Query

CREATE INDEX IF NOT EXISTS s_idx ON films (revenue);

Tip

The CREATE INDEX IF NOT EXISTS statement does not have an “early exit” at the moment, instead, it will attempt to create the index and only check its existence before committing it to storage. Therefore, it may run for a longer time compared to other IF NOT EXISTS statements, which terminate early.

Create compound index gy_idx on genre and year columns:

Query

CREATE INDEX gy_idx ON films (genre, year);

Create an index on an expression — e.g., the sum of columns j and k from table integers:

Query

CREATE INDEX i_index ON integers ((j + k));

Inverted indexes support indexed expressions too — see CREATE INDEX … USING inverted.

Parameters

Name	Description
UNIQUE	Causes the system to check for duplicate values in the table when the index is created (if data already exist) and each time data is added. Attempts to insert or update data that would result in duplicate entries will generate an error.
name	The name of the index to be created.
table	The name of the table to be indexed.
column	The name of the column to be indexed.
expression	An expression over one or more columns of the table (see the example above). Inverted indexes also support indexed expressions.
index type	Specified index type, see Indexes. Optional.
option	Index option in the form of a Boolean true value (e.g., is_cool) or a key-value pair (e.g., my_option = 2). Optional.

Syntax

Inverted Indexes

Adding USING inverted creates an inverted index for full-text, vector and geospatial search. It has its own grammar — column dictionaries, feature flags, INCLUDE columns and index options. See CREATE INDEX … USING inverted for the full syntax reference.

DROP INDEX

DROP INDEX drops an existing index from the database system.

Examples

Remove the index title_idx:

Query

DROP INDEX title_idx;

Parameters

Name	Description
IF EXISTS	Do not throw an error if the index does not exist.
name	The name of an index to remove.

Syntax

Limitations

The CREATE INDEX clause does not support the OR REPLACE modifier.