Knex / Objection `duplicate key value violates unique constraint` when inserting

Question

exports.seed = async function(knex, Promise) {
  await knex.raw('TRUNCATE TABLE posts RESTART IDENTITY CASCADE')
  await knex.raw('TRUNCATE TABLE users RESTART IDENTITY CASCADE')
}

and some seed data....

const faker = require('faker')

const post = [
  {
    id: 1,
    title: faker.lorem.words(),
    body: '12 - 10',
    answers: '[12]',
    user_id: 1
  },
  {
    id: 2,
    title: faker.lorem.words(),
    body: '12 - 10 + 123',
    answers: '[12]',
    user_id: 1
  },
  {
    id: 3,
    title: faker.lorem.words(),
    body: '12 - 10 / 901',
    answers: '[12]',
    user_id: 1
  }
]

exports.seed = (knex, Promise) => {
  // Deletes ALL existing entries
  return knex('post').then(() => {
    // Inserts seed entries
    return knex('post').insert(post)
  })
}

A simple update method

export async function update(req, res) {
  try {
    const user = await currentUser()
    const params = req.body
    await Post.query()
      .findById(req.params.id)
      .where('user_id', user.id)
      .patch({ ...params, user_id: user.id })

    return res.json({ success: true })
  } catch (err) {
    return res.status(500).json({ message: 'Something went wrong' })
  }
}

When I'm doing inserts in my tests, getting a duplicate key value violates unique constraint

Even when the test data created from seeds, has the id's of [1,2,3], the insert complains the primary is already used. Why is this? Shouldn't the database be choosing the next available id?

exports.up = async function(knex) {
  await knex.schema.alterTable('posts', (t) => {
    t.integer('user_id')
      .unsigned()
      .references('users.id')
  })
}

exports.down = async function(knex) {
  await knex.schema.table('posts', (t) => {
    t.dropColumn('user_id')
  })
}

EDIT

Even when inserting with just SQL.. there are errors:

INSERT INTO calcs (title, answers, user_id) VALUES ('tester', '[123]', 1);

Results in a : `duplicate key value violates unique constraint posts_pkey'

The reason I'm not just using a knex del() call is because I am getting errors about deleting a table with foreign key references..

score 7 · Answer 1 · answered Jul 06 '19 at 00:38

7

When you insert the data from seed you are also passing id column value to the database.

That causes your id sequenceof the primary key to not increment when you seed the initial inserts.

Now after seeding when you try to insert more data id sequence still returns ids starting from 1.

You should set your id sequences to correct values during seeding like it is instructed in this answer How to reset sequence in postgres and fill id column with new data?

answered Jul 06 '19 at 00:38

Mikael Lepistö

18,909
3
68
70

Not passing the id column. There is something going on w/ how I setup the foreign / primary key. Going to update my question with new findings – GN. Jul 06 '19 at 00:51
1

You are passing `id` column in seed files. It causes `id_sequence` of primary key to not increment during seeding. So first insert you try to do in app conflicts with row with `id` 1. – Mikael Lepistö Jul 07 '19 at 23:20
@GN this should be marked as the correct answer – halshing Dec 08 '20 at 00:09

score 0 · Answer 2 · answered Jul 06 '19 at 00:52

0

You should fix this part, missing .del():

exports.seed = (knex, Promise) => {
  // Deletes ALL existing entries
  return knex('post').then(() => {
    // Inserts seed entries
    return knex('post').del().insert(post)
  })
}

answered Jul 06 '19 at 00:52

Sombriks

3,370
4
34
54

Knex / Objection `duplicate key value violates unique constraint` when inserting

2 Answers2