Skip to content

"db-migrate up" gets "error: duplicate key value violates unique constraint "migrations_pkey"" #548

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Closed
1 of 3 tasks
hawkeye64 opened this issue Feb 16, 2018 · 2 comments
Closed
1 of 3 tasks

Comments

@hawkeye64
Copy link

hawkeye64 commented Feb 16, 2018

I'm submitting a...

  • Bug report
  • Feature request
  • Question

Current behavior

Here's the situation. We want to start using a migrator for our database. We already have customers with an existing database. I've written all the migrations that create a new database and updates it. My idea is that for existing customers, during the update to the next version, it will detect if there is a "migrations" table. If not, it will create it and fill it with existing migrations so they don't get run.

I created a new migration and when I run it in this scenario, I get the error above in the title.

For a new install where db-migrate was used to create the database, this problem does not exist.

Expected behavior

I would have expected the migration to be inserted into the migration table without error. It appears that when it's doing the insert it is also inserting the primary key (1 in this case) instead of using the DEFAULT key word (postgreSQL) to automatically increment the insert.

Minimal reproduction of the problem with instructions

What is the motivation / use case for changing the behavior?

Environment


db-migrate version: 0.10.5
plugins with versions: X.Y.Z
db-migrate driver with versions: db-migrate-pg 0.3.1

Additional information:
- Node version: 6.12.3
- Platform:  Linux

Others:

@hawkeye64
Copy link
Author

I discovered when creating the migrations table with the sequence, I need to set the "START WITH" to the correct count of expected migrations (rows) in the table:

CREATE SEQUENCE ivtinternal.migrations_id_seq
    START WITH 39 <-- was 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

This solved my problem.

@olso
Copy link

olso commented Jan 16, 2020

Try to run it multiple times

You will notice detail: 'Key (id)=(10) already exists.', increment to correct height

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants