1

I'm using the declarative approach to build a database using sqlalchemy in a flask application. If I initialize the database and run my program everything works fine. I am able to add data to the db with no issues and the primary key (id) is incremented appropriately. But if I copy and paste data from another db and then try to run my code to add an item I get this error:

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "printers_pkey" DETAIL: Key (id)=(1) already exists.

I believe it is because upon adding an item the primary key (id) starts from 1 instead of picking up where the id left off. It is not aware that anything has been added outside of my program. I cannot find anything on how to go about getting my program to know that there is data in the table and to pickup where the id is left off.

davidism
  • 121,510
  • 29
  • 395
  • 339
Firesoul44
  • 25
  • 4
  • 2
    you need to reset the sequence e.g. `ALTER SEQUENCE schema."table_name_TableID_seq" RESTART WITH 64;` – Andrew Allen Mar 03 '20 at 18:56
  • 2
    This is not related to SQLA, but how PostgreSQL sequences work. See https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts – Ilja Everilä Mar 03 '20 at 19:00
  • Thank you so much Andrew! I was able to figure out a way to fix my problem thanks to your answer. And thank you Ilja for clarifying that it wasn't an sqlalchemy probelm, as I was not sure initially. – Firesoul44 Mar 03 '20 at 19:10
  • @Firesoul44 I think Ilja's link is a better answer. Self mark as duplicate? – Andrew Allen Mar 03 '20 at 19:25
  • My apologies, I'm not too familiar with stackoverflow. What do you mean by self mark as duplicate? Also, I'm not explicitly declaring an id. It's being autogenerated. I was able to solve my problem with this code: `def update_seq(Schema, table): records = db.session.query(Schema).all() seq = len(records) + 1 sql = "ALTER SEQUENCE %s_id_seq RESTART WITH %i" % (table, seq) db.session.execute(sql)` The link was very helpful however I was not explicitly declaring the id number. – Firesoul44 Mar 03 '20 at 19:36

0 Answers0