Insert problems after postgresql migration?

I migrated my DB from MySQL to PostgreSQL, the way was:

MySQL dump
Laravel migrations run in PostgreSQL
In every table manually run "INSERT INTO", edited only booleans to instead of 1 => true...
The migrated data is correct 1:1

I think there is some issue with id's and autoincrement.

If I need to add new elements I get error message:

SQLSTATE[23505]: Unique violation: 7 ERROR: duplicate key value violates unique constraint "contacts_pkey"
DETAIL: Key (id)=(2) already exists. (SQL: insert into "contacts" ("main_id", "uuid", "default", "disabled", "title", "name", "phone", "email", "phone_mobile", "updated_at", "created_at") values (1, 933ae5cf-f459-4096-9fa0-29e220485c3f, 0, 0, prof, Fadsfdasf, , [email protected], , 2018-04-07 12:35:12, 2018-04-07 12:35:12) returning "id")

If I reset db migrations without data and make some insert everything works fine...

How can this problem be solved?

bobbybouwmann

8 years ago

Best Answer

Level 88

Well you need to reset the autoincrement of your tables, because the current autoincrement is still 1.

You can do something like this in your case

ALTER SEQUENCE contacts_main_id_seq RESTART WITH 1453

Source: https://stackoverflow.com/questions/5342440/reset-auto-increment-counter-in-postgres

However it's better to use a tool that migrates everything! Something like this? http://www.sqlines.com/mysql-to-postgresql-tool

1 like

Please or to participate in this conversation.