knex/knex · error · Error

postgres cannot create constraint with predicate

Error message

postgres cannot create constraint with predicate

What it means

postgres unique() (pg-tablecompiler.js:203) refuses to attach a WHERE predicate to a UNIQUE CONSTRAINT. Postgres supports partial unique indexes (WHERE clause) but not partial constraints; the two features are mutually exclusive. When useConstraint is true and predicate is set, knex throws rather than emit invalid DDL.

Source

Thrown at lib/dialects/postgres/schema/pg-tablecompiler.js:204

      );
    }
  }

  unique(columns, indexName) {
    let deferrable;
    let useConstraint = true;
    let predicate;
    if (isObject(indexName)) {
      ({ indexName, deferrable, useConstraint, predicate } = indexName);
      if (useConstraint === undefined) {
        useConstraint = !!deferrable || !predicate;
      }
    }
    if (!useConstraint && deferrable && deferrable !== 'not deferrable') {
      throw new Error('postgres cannot create deferrable index');
    }
    if (useConstraint && predicate) {
      throw new Error('postgres cannot create constraint with predicate');
    }
    deferrable = deferrable ? ` deferrable initially ${deferrable}` : '';
    indexName = indexName
      ? this.formatter.wrap(indexName)
      : this._indexCommand('unique', this.tableNameRaw, columns);

    if (useConstraint) {
      this.pushQuery(
        `alter table ${this.tableName()} add constraint ${indexName}` +
          ' unique (' +
          this.formatter.columnize(columns) +
          ')' +
          deferrable
      );
    } else {
      const predicateQuery = predicate
        ? ' ' + this.client.queryCompiler(predicate).where()
        : '';

View on GitHub (pinned to e25d54bcb7)

Solutions

1. Use an index instead of a constraint for partial uniqueness: table.unique(['col'], { useConstraint: false, predicate: knex.raw('active = true') }).
2. Drop the predicate if you must use a constraint.
3. Model the soft-delete uniqueness via an expression index or a generated column.

Example fix

// before
table.unique('email', { useConstraint: true, predicate: knex.raw('deleted_at IS NULL') });

// after (partial unique must be an index)
table.unique('email', { useConstraint: false, predicate: knex.raw('deleted_at IS NULL') });

function validateUniqueOptions({ useConstraint = true, predicate } = {}) {
  if (useConstraint && predicate) {
    throw new Error('Partial unique must be an index, not a constraint');
  }
  return { useConstraint, predicate };
}
table.unique('email', validateUniqueOptions(opts));

function isPartialUniqueConstraint(opts) {
  return opts && (opts.useConstraint === undefined ? true : opts.useConstraint) && !!opts.predicate;
}

When it happens

Trigger: Calling table.unique(['col'], { useConstraint: true, predicate: knex.raw('active = true') }) or any path where useConstraint resolves true (explicit, or implied by deferrable/no-predicate logic) alongside a non-empty predicate.

Common situations: Trying to make a soft-delete-friendly unique constraint by adding a WHERE; copy-pasting options between constraint and index forms; assuming constraints accept the same predicate option as indexes.

Related errors

• postgres cannot create deferrable index
• .dropUniqueIfExists() is not supported by redshift
• mssql cannot create constraint with predicate
• .dropForeignIfExists is not supported for mssql.
• .dropPrimaryIfExists is not supported for mssql.

AI-assisted analysis of knex/knex@e25d54bcb7 (2026-08-03). Data as JSON: /data/errors/c56cb400f083eb0d.json. Report an issue: GitHub.