0

I have created a table A having a id (serial) field. I have created a seeding sql file, where I specify explicitly the id values, because I have also to populate related tables having foreign key that refers to this id field.

All the seeding sql script run fine. But when it comes to perform a new insert in my table A (without specifying the id, serial, because I want it to be auto incremented) I get back a duplicate key error. Looks like that after the first seeding script. Postgres doesn't auto increment the index.

In fact, if I had inserted, let's say 3 records in the seeding phase, I'll get back three times the error from the subsequent inserts with id NULL, and then it starts inserting everything as expected. I.e. even if it report duplicate key error, it's incrementing the auto increment value each query sent.

How do you usually manage this? Thanks

koalaok
  • 5,075
  • 11
  • 47
  • 91
  • 2
    Possible duplicate of [How to reset postgres' primary key sequence when it falls out of sync?](http://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync) – pozs Feb 01 '16 at 11:19

1 Answers1

0

You are facing this problem because "current value of your table sequence id is different". you can change it in your database.

If you are using pgadmin then go to your schema > sequences > yourtablename_id_seq > change current value.

Monty
  • 1,110
  • 7
  • 15
  • Isnt it possible to set it via query , just after the seeding process? In Mysql it can be set the auto increment. There's no equivalent in postgres? After running my seeding script it would be nice if the DB is ready for the application, not that I need to remember to go there and increase each autoincrement on every table I populated... – koalaok Feb 01 '16 at 11:04
  • check your max id of your table, using max() function and set your tablename_id_seq > current value plus 1 with your max id, then you will not face this duplicate id issue – Monty Feb 01 '16 at 11:06