CREATE INDEX

Builds CREATE INDEX statements using SqlCreateIndex.

CREATE INDEX is a DDL statement and produces no parameterized arguments.

Provided as a convenience for DDL operations around temporary tables where the indexed columns may vary based on program logic.

Basic Index

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

CREATE INDEX title_idx ON films (title)

Unique Index

qb = (SQL.create_index('title_idx', 'films')
      .unique()
      .columns('title'))

CREATE UNIQUE INDEX title_idx ON films (title)

INCLUDE

PostgreSQL only.

Non-key columns for index-only scans:

qb = (SQL.create_index('title_idx', 'films')
      .unique()
      .columns('title')
      .include('director', 'rating'))

CREATE UNIQUE INDEX title_idx ON films (title) INCLUDE (director, rating)

USING (Index Method)

qb = (SQL.create_index('pointloc', 'points')
      .using('gist')
      .columns('box(location,location)'))

CREATE INDEX pointloc ON points USING gist (box(location,location))

Partial Index (WHERE)

qb = (SQL.create_index('idx_active', 'users')
      .columns('email')
      .where('active = true'))

CREATE INDEX idx_active ON users (email) WHERE active = true

CONCURRENTLY

PostgreSQL only.

qb = (SQL.create_index('sales_quantity_index', 'sales_table')
      .concurrently()
      .columns('quantity'))

CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity)

Expression Index

qb = (SQL.create_index('films_lower_idx', 'films')
      .columns('(lower(title))'))

CREATE INDEX films_lower_idx ON films ((lower(title)))

Storage Parameters

PostgreSQL only.

qb = (SQL.create_index('title_idx', 'films')
      .unique()
      .columns('title')
      .with_params('fillfactor = 70'))

CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70)

TABLESPACE

PostgreSQL only.

qb = (SQL.create_index('code_idx', 'films')
      .columns('code')
      .tablespace('indexspace'))

CREATE INDEX code_idx ON films (code) TABLESPACE indexspace

NULLS NOT DISTINCT

PostgreSQL only.

qb = (SQL.create_index('idx', 'users')
      .unique()
      .nulls_not_distinct()
      .columns('email'))

CREATE UNIQUE INDEX idx ON users (email) NULLS NOT DISTINCT

Column Options

Collation, sort order, and nulls ordering are specified in the column string:

qb = (SQL.create_index('idx', 'films')
      .columns('title COLLATE "de_DE"', 'date DESC NULLS FIRST'))

CREATE INDEX idx ON films (title COLLATE "de_DE", date DESC NULLS FIRST)