Repository: knex/knex
Pull request #5601 (2023-06-12 15:14:07)

fix: creating a unique index in postgres using the index method
This is a fix for the issue described here: https://github.com/knex/knex/issues/5217

The `indexType` option was being used for the storage type instead of `storageEngineIndexType` as in the [documentation](https://knexjs.org/guide/schema-builder.html#index).

I've change the method to follow the options as in the documentation. There also appeared to be to option to pass in a string as instead of an options object. I've left this as the `storageEngineIndexType` as it was previously, although this is not mentioned in the documentation.

I've also updated the tests and added a new one for this scenario.


coveralls: 
[![Coverage Status](https://coveralls.io/builds/61123847/badge)](https://coveralls.io/builds/61123847)

coverage: 92.788% (+0.003%) from 92.785% when pulling **4ab0d44304770d7946f9a41e4bc214d3cb8cd691 on awo00:fix-postgres-indexType-unique** into **c8da0dfc13e9cc34fcf0c476ad1b6f379cc5348a on knex:master**.


Patch:
diff --git a/lib/dialects/postgres/schema/pg-tablecompiler.js b/lib/dialects/postgres/schema/pg-tablecompiler.js
index dd029cf1..455a339c 100644
--- a/lib/dialects/postgres/schema/pg-tablecompiler.js
+++ b/lib/dialects/postgres/schema/pg-tablecompiler.js
@@ -235,12 +235,13 @@ class TableCompiler_PG extends TableCompiler {
       : this._indexCommand('index', this.tableNameRaw, columns);
 
     let predicate;
+    let storageEngineIndexType;
     let indexType;
 
     if (isString(options)) {
-      indexType = options;
+      storageEngineIndexType = options;
     } else if (isObject(options)) {
-      ({ indexType, predicate } = options);
+      ({ indexType, storageEngineIndexType, predicate } = options);
     }
 
     const predicateQuery = predicate
@@ -248,8 +249,12 @@ class TableCompiler_PG extends TableCompiler {
       : '';
 
     this.pushQuery(
-      `create index ${indexName} on ${this.tableName()}${
-        (indexType && ` using ${indexType}`) || ''
+      `create${
+        typeof indexType === 'string' && indexType.toLowerCase() === 'unique'
+          ? ' unique'
+          : ''
+      } index ${indexName} on ${this.tableName()}${
+        (storageEngineIndexType && ` using ${storageEngineIndexType}`) || ''
       }` +
         ' (' +
         this.formatter.columnize(columns) +


Test patch:
diff --git a/test/unit/schema-builder/postgres.js b/test/unit/schema-builder/postgres.js
index fb9065d5..313ff118 100644
--- a/test/unit/schema-builder/postgres.js
+++ b/test/unit/schema-builder/postgres.js
@@ -973,7 +973,7 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
-  it('adding index with an index type', function () {
+  it('adding index with a storage engine index type', function () {
     tableSql = client
       .schemaBuilder()
       .table('users', function (table) {
@@ -986,7 +986,7 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
-  it('adding index with an index type fluently', function () {
+  it('adding index with a storage engine index type fluently', function () {
     tableSql = client
       .schemaBuilder()
       .table('users', function (table) {
@@ -1002,11 +1002,11 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
-  it('adding index with an index type and default name fluently', function () {
+  it('adding index with a storage engine index type and default name fluently', function () {
     tableSql = client
       .schemaBuilder()
       .table('users', function (table) {
-        table.string('name').index(null, { indexType: 'gist' });
+        table.string('name').index(null, { storageEngineIndexType: 'gist' });
       })
       .toSQL();
     equal(2, tableSql.length);
@@ -1033,12 +1033,27 @@ describe('PostgreSQL SchemaBuilder', function () {
     );
   });
 
-  it('adding index with an index type and a predicate', function () {
+  it('adding unique index using index method', function () {
     tableSql = client
       .schemaBuilder()
       .table('users', function (table) {
         table.index(['foo', 'bar'], 'baz', {
-          indexType: 'gist',
+          indexType: 'unique',
+        });
+      })
+      .toSQL();
+    equal(1, tableSql.length);
+    expect(tableSql[0].sql).to.equal(
+      'create unique index "baz" on "users" ("foo", "bar")'
+    );
+  });
+
+  it('adding index with a storage engine index type and a predicate', function () {
+    tableSql = client
+      .schemaBuilder()
+      .table('users', function (table) {
+        table.index(['foo', 'bar'], 'baz', {
+          storageEngineIndexType: 'gist',
           predicate: client.queryBuilder().whereRaw('email = "foo@bar"'),
         });
       })