CODEWITHSUNDEEP
postgresqlknex.js
qwang07
qwang07

Reputation: 1256

Knex.js - How to create unique index with 'where' clause?

I'm using Knex.js. I'd like to create a unique index for my table with WHERE clause:

db.schema.createTable('newTable', function(t) {
  t.increments()
  t.string('col1').defaultTo(null)
  t.string('col2').notNullable()
  t.integer('col3').notNullable()
  t.unique(['col1', 'col2', 'col3']).whereNotNull('col1')
  t.unique(['col2', 'col3']).whereNull('col1')
})

I try to create two partial indexes for the table. However, whereNotNull is not chainable with unique function. How to create a unique index with 'where' clause?

Upvotes: 3

Views: 7428

Answers (4)

Akash Rudra
Akash Rudra

Reputation: 176

With this merged, now in order create unique index in knex we can do

db.schema.createTable('newTable', function(t) {
  t.increments()
  t.string('col1').defaultTo(null)
  t.string('col2').notNullable()
  t.integer('col3').notNullable()
  t.unique(['col1', 'col2', 'col3'], {
    useConstraint: false,
    predicate: knex.whereNotNull('col1')
  })
  t.unique(['col2', 'col3'], {
    useConstraint: false,
    predicate: knex.whereNull('col1')
  })
})

Upvotes: 0

krishnazden
krishnazden

Reputation: 1177

one can use below syntax to create index with conditions

knex.table('users', function (table) 
{
    table.index(['name', 'last_name'], 'idx_name_last_name', {
      indexType: 'FULLTEXT',
      storageEngineIndexType: 'hash',
      predicate: knex.whereNotNull('email'),
    });
});

or

knex.table('users', function (table) 
{
    table.index(['name', 'last_name'], 'idx_name_last_name', {
      indexType: 'unique',
      storageEngineIndexType: 'hash',
      predicate: knex.whereRaw('email=false'),
    });
});

extra example

Upvotes: 0

tal weissler
tal weissler

Reputation: 225

Try this: 

   knex.schema.raw(
      `CREATE UNIQUE INDEX "unique_partial_newTable_col1_col2_col3" ON "newTable" ("col1", "col2", "col3") WHERE "col1" IS NOT NULL`
    );

   knex.schema.raw(
      `CREATE UNIQUE INDEX "unique_partial_newTable_col2_col3" ON "newTable" ("col2", "col3") WHERE "col1" IS NULL`
    );

Upvotes: 8

Mikael Lepistö
Mikael Lepistö

Reputation: 19718

Partial index is not yet supported. There is PR which is not ready yet though https://github.com/tgriesser/knex/pull/2401.

For now one has to use knex.schema.raw for it.

Upvotes: 6

Related Questions