Integrity error: duplicate key (id) in posgres using sqlalchemy

Question

I have some code, and every now and then (not always) when I try to insert a new record I run into an integrity error:

IntegrityError: (IntegrityError) duplicate key value violates unique constraint "question_pkey" DETAIL: Key (id)=(1) already exists.

I read somewhere on here that it's not a Postgres error, so it may have something to do with my code.

When I do a curl I'm inserting these things: curl 127.0.0.1:5000/test/question -d '{"question_order": 6, "question_admin_type": 2}'

My code looks like:

def post(self, question_id=0):
    post_args = request.get_json(force=True)
    if question_id == 0: # assume new record
        question_obj = Question(**post_args)
        db.session.add(question_obj)
        db.session.flush()
        db.session.commit()

The error message seems to point towards the db.session.flush() but I don't know why. It seems like there's a problem with the line before it. It seems like my program tries to insert starting at the first-most id, and if that fails then it throws this error. On the next insert attempt (using cURL), it will try the next id, do the same thing if there is already an entry using that id and it'll continue to happen until it reaches an unused id. I'm also using the flask-sqlalchemy extension, if that's relevant.

You're trying to reuse ids to create new rows here. Do you wish to modify the existing data or create a new record? If the former, you should try to fetch the object first and modify that. If the latter, you can omit the id from the request entirely and let postgres handle them. — jonafato
@jonafato I don't see where I am reusing ids. If you mean question_id==0, that relates to the endpoint. If the endpoint is: /test/question, I'm assuming it's for insertion of a new record. If the endpoint is: /test/question/1, I'm assuming there is a record update/modify of existing data. I want to create a new record in this case, so I don't pass in id inside of my post args JSON as seen in that cURL example. — user2899444
If you're confident that this error doesn't stem from using similar logic to try and create a new question at an existing id, please post your model and information about your database. — jonafato

jackotonye jackotonye · Accepted Answer · 2016-10-31T18:27:38

Using **post_args to add question_obj json datatype has index which will translate into row ids similar to specifying more than on id = 1 id = 1 for the same row

Integrity error: duplicate key (id) in posgres using sqlalchemy

1 Answers