I have a postgresql database, and I am trying the following migration to add a non-nullable reference column to an existing table, using Knex:

exports.up = function (knex) {
  return knex.schema.table('Donation', (table) => {
    table.integer('causeId').notNullable();
    table.foreign('causeId').references('Cause.id');
  });
};

When I run this migration, I get the following error due to existing rows:

error: alter table "Donation" add column "causeId" integer not null - column "causeId" contains null values

I want to seed the column with a default value only for existing rows. For subsequent insertions, I would rather the insert fail if a value is not provided, so .notNullable().defaultTo(...) won't work.

I was thinking of maybe using defaultTo() only for the initial migration and then removing the constraint after, but I'm drawing a blank on how to acplish this.

Any help would be appreciated!

I have a postgresql database, and I am trying the following migration to add a non-nullable reference column to an existing table, using Knex:

exports.up = function (knex) {
  return knex.schema.table('Donation', (table) => {
    table.integer('causeId').notNullable();
    table.foreign('causeId').references('Cause.id');
  });
};

When I run this migration, I get the following error due to existing rows:

error: alter table "Donation" add column "causeId" integer not null - column "causeId" contains null values

I want to seed the column with a default value only for existing rows. For subsequent insertions, I would rather the insert fail if a value is not provided, so .notNullable().defaultTo(...) won't work.

I was thinking of maybe using defaultTo() only for the initial migration and then removing the constraint after, but I'm drawing a blank on how to acplish this.

Any help would be appreciated!

• javascript
• database
• postgresql
• database-migration
• knex.js

1 Answer 1

My solution ended up being to use defaultTo(...) on creation of this column and then alter it afterwards using alterTable(...).

Not sure of a better way, but this works.

exports.up = function (knex) {
  function relateCauseToDonation() {
    return knex.schema.table('Donation', (table) => {
      table.integer('causeId').notNullable().defaultTo(1);
      table.foreign('causeId').references('Cause.id');
    });
  }

  function removeDefaultToFromCauseId() {
    return knex.schema.alterTable('Donation', (table) => {
      table.integer('causeId').notNullable().alter();
    });
  }

  return relateCauseToDonation().then(removeDefaultToFromCauseId);
};