Index Operations

Operation: createIndex

pgm.createIndex( tablename, columns, options )

IMPORTANT

Create a new index - postgres docs Alias: addIndex

Arguments

Name	Type	Description
tablename	Name	name of the table to alter
columns	string or array[string]	columns to add to the index with optional operator class and sort
options	object	Check below for available options

NOTE

Bare names containing only ASCII letters, digits, non-ASCII characters, _, . and - are quoted as column identifiers; $ is also allowed after the first character. This includes Straße, 日本語, größe-id and [{ name: 'größe-id' }]. Case is preserved unless decamelize is enabled. For compatibility, names containing " are also quoted as identifiers when their other characters are only ASCII letters, digits, _ or .; the quotes are treated as part of the column name. For example, a"b names the column "a""b", while größe"id follows the SQL expression path. To reference the latter column, use explicitly quoted SQL such as ("größe""id"). Other unquoted inputs are treated as SQL expressions, with parentheses added when needed: a+b, a*b, a/b and a - b remain expressions. To make subtraction explicit, use a - b or (a-b); an explicit index name is recommended for string expressions, but is not required. Function calls, JSON expressions and dollar-quoted SQL strings retain their existing behavior.

Options

Option	Type	Description
name	string	name for the index (one will be inferred from table/columns if undefined)
unique	boolean	set to true if this is a unique index
where	string	raw sql for where clause of index
concurrently	boolean	create this index concurrently
ifNotExists	boolean	default false
method	string	btree | hash | gist | spgist | gin | brin
include	string or array[string]	columns to add to the include clause
nulls	string	distinct | not distinct (for unique indexes only)
storageParameters	object	index storage parameters for the WITH clause; values can be strings, numbers, or booleans

Storage parameter names and string values are escaped and used as supplied, without decamelization. An empty storageParameters object omits the WITH clause. PostgreSQL validates which parameters and values the selected index method supports; see Index Storage Parameters.

The exported IndexStorageParameters type can be used for reusable parameter maps.

When using concurrently: true, call pgm.noTransaction() in the migration. Regular index creation supports transactions, including when storage parameters are specified.

Examples

single column

pgm.createIndex('table', 'column');
//expected output: CREATE INDEX ON "table" ("column")

hyphenated column

pgm.createIndex('measurements', 'a-b', {
  name: 'unique_measurement',
  unique: true,
});
//expected output: CREATE UNIQUE INDEX "unique_measurement" ON "measurements" ("a-b")

subtraction expression

pgm.createIndex('measurements', '(a-b)', { name: 'measurement_difference' });
//expected output: CREATE INDEX "measurement_difference" ON "measurements" ((a-b))

multiple columns

pgm.createIndex('table', ['col1', 'col2']);
//expected output: CREATE INDEX ON "table" ("col1", "col2")

multiple columns with options

pgm.createIndex('table', [
  { name: 'col1', sort: 'ASC' },
  { name: 'col2', sort: 'DESC' },
]);
//expected output: CREATE INDEX ON "table" ("col1" ASC, "col2" DESC)

operator class

pgm.createIndex('table', [
  { name: 'col1', opclass: { schema: 'schema', name: 'opclass' }, sort: 'ASC' },
]);
//expected output: CREATE INDEX ON "table" ("col1" "schema"."opclass" ASC)

storage parameters

pgm.createIndex('films', 'title', {
  name: 'title_idx',
  storageParameters: { fillfactor: 70, deduplicate_items: false },
});
//expected output: CREATE INDEX "title_idx" ON "films" ("title") WITH ("fillfactor" = 70, "deduplicate_items" = false);

GiST storage parameters

pgm.createIndex('locations', 'position', {
  name: 'position_idx',
  method: 'gist',
  storageParameters: { buffering: 'off' },
});
//expected output: CREATE INDEX "position_idx" ON "locations" USING gist ("position") WITH ("buffering" = $pga$off$pga$);

BRIN storage parameters

pgm.createIndex('events', 'created_at', {
  method: 'brin',
  storageParameters: { pages_per_range: 32, autosummarize: true },
});
//expected output: CREATE INDEX "events_created_at_index" ON "events" USING brin ("created_at") WITH ("pages_per_range" = 32, "autosummarize" = true);

Reverse Operation: dropIndex

pgm.dropIndex( tablename, columns, options )

IMPORTANT

Drop an index - postgres docs

Arguments

Name	Type	Description
tablename	Name	name of the table to alter
columns	string or array[string]	column names, used only to infer an index name
options	object	Check below for available options

Options

Option	Type	Description
name	string	name of the index to drop
concurrently	boolean	drop this index concurrently
ifExists	boolean	default false
cascade	boolean	default false

Operation: renameIndex

pgm.renameIndex( name, newName )

IMPORTANT

Rename an index - postgres docs

Reverse Operation: same operation in opposite direction

Arguments

Name	Type	Description
name	Name	name of the index to rename
newName	Name	new name for the index

Examples

ts

pgm.renameIndex('index_name', 'new_index_name');
//expected output: ALTER INDEX index_name RENAME TO new_index_name;

Renaming preserves the source schema, including during automatic reversal. See Renaming and schemas for destination-schema validation and supported PgLiteral identifiers.

Renaming an index does not move it to another schema. PostgreSQL does not provide ALTER INDEX ... SET SCHEMA; indexes follow their table when it changes schema.