SqlCreateIndex

class SqlCreateIndex(name, table)

Builds a CREATE INDEX statement. Created via SQL.create_index(name, table). Inherits from SqlStatement.

Parameters:

• name – Index name string.

• table – Table name string (optionally schema-qualified).

In the examples below, qb refers to a SqlCreateIndex instance:

qb = SQL.create_index('title_idx', 'films')

columns(*columns)

Add index columns. Each column string can include expressions, collation, operator class, and sort options.

Parameters:

columns – One or more column/expression strings.

qb.columns('title')
qb.columns('(lower(title))')
qb.columns('title COLLATE "de_DE"')
qb.columns('date DESC NULLS FIRST')

unique()

Create a UNIQUE index.

qb.unique()
# CREATE UNIQUE INDEX ...

concurrently()

Build the index without locking writes.

qb.concurrently()
# CREATE INDEX CONCURRENTLY ...

if_not_exists()

Add IF NOT EXISTS.

qb.if_not_exists()
# CREATE INDEX IF NOT EXISTS ...

only()

Don’t recurse into partitions.

qb.only()
# ... ON ONLY table ...

using(method)

Specify the index method.

Parameters:

method – Method string ('btree', 'hash', 'gist', 'spgist', 'gin', 'brin').

qb.using('gist')
# ... USING gist (...)

include(*columns)

Add non-key columns (INCLUDE clause) for index-only scans.

Parameters:

columns – One or more column name strings.

qb.include('director', 'rating')
# ... INCLUDE (director, rating)

nulls_not_distinct()

For unique indexes, treat NULLs as equal.

qb.nulls_not_distinct()
# ... NULLS NOT DISTINCT

with_params(params)

Set storage parameters.

Parameters:

params – Parameter string (e.g. 'fillfactor = 70').

qb.with_params('fillfactor = 70')
# ... WITH (fillfactor = 70)

tablespace(name)

Specify the tablespace for the index.

Parameters:

name – Tablespace name string.

qb.tablespace('indexspace')
# ... TABLESPACE indexspace

where(predicate)

Create a partial index with a WHERE predicate.

Parameters:

predicate – Predicate string.

qb.where('active = true')
# ... WHERE active = true