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?

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?

• javascript
• node.js
• postgresql
• heroku
• knex.js

• Can you provide data from table contracts? select * from contracts and from contracts_id_seq select * from contracts_id_seq This is clearly caused because you have collisions in contacts.id column (trying to insert same value in primary key column twice). It's hard to say what is happehed exactly. And you don't need .select(...). Builder outputs same query without .select. And which version of node/knex you are using? – coockoo Commented Jul 5, 2017 at 6:13
• @coockoo Good catch, I have removed the select now from my code. I am using Node V6.9.4 and KnexJS V0.12.9. My seeds look like this: {id: 1, fName: 'Todd', lName: 'Drinkwater', organisation: 'TDD', email: '[email protected]'}, {id: 2, fName: 'Blair', lName: 'Drinkwater', organisation: 'BDD', email: '[email protected]'} – Todd Drinkwater Commented Jul 7, 2017 at 0:20
• @coockoo I also got this error message on my heroku logs: 2017-07- name: 'error', length: 192, severity: 'ERROR', code: '23505', detail: 'Key (id)=(3) already exists.', hint: undefined, position: undefined, internalPosition: undefined, internalQuery: undefined, where: undefined, schema: 'public', table: 'contracts', column: undefined, dataType: undefined, constraint: 'contracts_pkey', file: 'nbtinsert.c', line: '433', routine: '_bt_check_unique' } I have editied out the timestamp so this could fit. – Todd Drinkwater Commented Jul 7, 2017 at 0:24

1 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> mand 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.