0

I am trying to add a new row to my table enjordplatform_keywords but everytime I do that it instead tries to add the row in the middle of the table.
Usually that id that it is trying to insert it in is taken but sometimes (if I have removed a row say row 81 it will add the new values in the empty row 81). Though the table goes up to 474 so why doesn't it add it in 475 instead of in the middle of the table.
This happens if I try to do it through sqlalchemy and if I try to do it directly in the database. It has worked perfectly fine but then this happened for some unknown reason.

What could be the problem?

This is the table in sqlalchemy:

class enjordplatformKeywords(Base):
    __tablename__ = "enjordplatform_keywords"
    id = Column(Integer, primary_key=True, unique=True)
    keyword = Column(String)
    keyword_swedish = Column(String)
    question_id = Column(Integer,ForeignKey("enjordplatform_quiz.id"))
    times_clicked = Column(Integer)
    added_by_company = Column(Integer)
    english_added = Column(Integer)

Here is an example of when I am inserting a new record directly into the postgres database and the error it gives.

My insert

insert into enjordplatform_keywords (keyword, keyword_swedish) VALUES ('Artificial Intelligence', 'Artificial Intelligence');

The error message

ERROR:  duplicate key value violates unique constraint "enjordplatform_keywords_pkey"
DETALJ:  Key (id)=(86) already exists.

The postgres database has 430 rows and previously it would get id 431 when inserting a new record instead of trying to override id=86

user12288003
  • 199
  • 1
  • 4
  • 14
  • 1
    a table has no beginning or end as all tables a re by definition unsorted – nbk Mar 15 '23 at 21:22
  • Ok fine. But I am guessing you understand what I mean. Now it is adding it on id 81 instead of id 473 which it has done everytime up until then. Why if I am doing an insert into to the postgres database it tries to override an id that is already existing instead of inserting it at the highest possible id – user12288003 Mar 15 '23 at 21:29
  • 1
    1) SQL data is unordered so a particular row can end up anywhere. 2) You need to show the code(**as update to question**) where you are saving the object. – Adrian Klaver Mar 15 '23 at 21:34
  • Hi Ok thanks for the clarification. I have updated the question with the response when I am trying to insert a new record into the table.Thanks – user12288003 Mar 15 '23 at 21:40
  • Really? You are mixing methods and not paying attention to your table definition to boot. The error; `Key (id)=(86) already exists.` is telling you the problem, you already have a row with that id in a column(`id = Column(Integer, primary_key=True, unique=True)`) that can't accept duplicates. What you are looking for is an `id` column that autoincrements, in which case you don't supply the value and let the database do that. – Adrian Klaver Mar 15 '23 at 21:44
  • Ok, what do you suggest I use instead? I have that setup for all my tables and it has never been an issue until now, so I am a bit confused why that is. Should I change all my tables to be using an autoincrement id instead? Is that possible to do in a simple way or do I need to copy all the data and then try to load it in again in a new table? Thanks – user12288003 Mar 15 '23 at 21:51
  • 1
    See [here](https://stackoverflow.com/questions/244243/how-to-reset-postgres-primary-key-sequence-when-it-falls-out-of-sync) and [here](https://stackoverflow.com/questions/9314382/why-do-sql-id-sequences-go-out-of-sync-specifically-using-postgres) –  Mar 16 '23 at 06:52

0 Answers0