CREATE INDEX ​

CREATE INDEX defines an index over one or more columns or expressions. A UNIQUE index also rejects duplicate non-NULL keys. A partial index includes only rows satisfying its WHERE condition.

Existing rows are validated when an index is created. An invalid unique index definition therefore fails if its current rows already contain duplicate keys.

Syntax ​

CREATE [UNIQUE] INDEX [IF NOT EXISTS] name
ON table (expression [COLLATE collation] [ASC | DESC], ...)
[WHERE condition]

Examples ​

A case-insensitive unique email ​

Indexing lower(email) makes uniqueness apply to the normalized expression. The partial predicate excludes missing addresses:

CREATE TABLE contacts(id INTEGER PRIMARY KEY, email TEXT);
CREATE UNIQUE INDEX contact_email
ON contacts(lower(email))
WHERE email IS NOT NULL;
INSERT INTO contacts VALUES (1, 'Ada@example.org'), (2, NULL);

SELECT id
FROM contacts
WHERE lower(email) = 'ada@example.org'
FORMAT csv;

1

Inserting another non-NULL email whose lowercase form is ada@example.org violates the unique index.

A compound index ​

CREATE TABLE orders(id INTEGER PRIMARY KEY, account_id UInt32, day Date);
CREATE INDEX orders_by_account_day ON orders(account_id, day DESC);

The index can support queries constrained by account_id and ordered or filtered by day. Use EXPLAIN QUERY PLAN to inspect the selected access path for a concrete query.

Remove an index with DROP INDEX. Each index must also be maintained when its table changes, so choose indexes for the queries the application actually runs.