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, _, . and - are quoted as column identifiers. This includes a-b and [{ name: 'a-b' }]. 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 and JSON expressions 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
include	string or array[string]	columns to add to the include clause
nulls	string	distinct | not distinct (for unique indexes only)

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)

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.