duplicate key value violates unique constraint - Using KnexJS, with Postgresql

Question

I am currently building an app using the KnexJS framework that helps me to write sqlite3 in development and postgresql in production (for Heroku).

My main issue is that my application works fine when on my machine, but as soon as I upload it to heroku it breaks. In Heroku logs I get the message:

{ error: insert into "contracts" ("contract_desc", "contract_header", "owner_id", "signee_id") values ($1, $2, $3, $4) - duplicate key value violates unique constraint "contracts_pkey"

And it leaves me unable to insert data into my database.

My Knex migrations for the table are setup like this:

exports.up = function(knex, Promise) {
  return knex.schema.createTable('contracts', function (table) {
    table.increments('id').primary()
    table.integer('owner_id')
    table.integer('signee_id')
    table.string('contract_header')
    table.text('contract_desc')
    table.string('signature_url')
    table.string('date_signed')
    table.boolean('isSigned')
      })
   };

exports.down = function(knex, Promise) {
  return knex.schema.dropTable('contracts')
};

And the function I am calling to insert the data looks like this:

function newContract (id, contractDetails) {
  return knex('contracts')
  .select('owner_id', 'signee_id', 'contract_header', 'contract_desc')
  .insert({
    owner_id: id,
    signee_id: contractDetails.signee_id,
    contract_header: contractDetails.contract_header,
    contract_desc:contractDetails.contract_desc
  })
}

Any ideas on what could be causing this?

Answer 1

Okay, I got it.

Try to remove id fields from your seed.

Let me explain why this happens

Postgres autoincrement fields take their values from sequences

You can try \d <table_name> command in psql interface. It will give you something like

...
 id         | bigint                   | not null default nextval('<table_name>_id_seq'::regclass)
...

By doing

insert into table_name (name) values ('Kappa')

You actually omit id field and insert default value, which is nextval('<table_name>_id_seq').

By specifying explicitly your id parameter in query you are not using this function and the next time you use it – you can get a collision of ids. You inserted id = 1 and nextval generated 1 as well.