94

I'm following up in regards to a question that I asked earlier in which I sought to seek a conversion from a goofy/poorly written mysql query to postgresql. I believe I succeeded with that. Anyways, I'm using data that was manually moved from a mysql database to a postgres database. I'm using a query that looks like so:

 UPDATE krypdos_coderound cru set is_correct = case when t.kv_values1 = t.kv_values2 then True else False end from (select cr.id, array_agg( case when kv1.code_round_id = cr.id then kv1.option_id else null end ) as kv_values1, array_agg( case when kv2.code_round_id = cr_m.id then kv2.option_id else null end ) as kv_values2 from krypdos_coderound cr join krypdos_value kv1 on kv1.code_round_id = cr.id join krypdos_coderound cr_m on cr_m.object_id=cr.object_id and cr_m.content_type_id =cr.content_type_id join krypdos_value kv2 on kv2.code_round_id = cr_m.id WHERE cr.is_master= False AND cr_m.is_master= True AND cr.object_id=%s AND cr.content_type_id=%s GROUP BY cr.id ) t where t.id = cru.id """ % ( self.object_id, self.content_type.id) )

I have reason to believe that this works well. However, this has lead to a new issue. When trying to submit, I get an error from django that states:

IntegrityError at (some url): duplicate key value violates unique constraint "krypdos_value_pkey"

I've looked at several of the responses posted on here and I haven't quite found the solution to my problem (although the related questions have made for some interesting reading). I see this in my logs, which is interesting because I never explicitly call insert- django must handle it:

 STATEMENT: INSERT INTO "krypdos_value" ("code_round_id", "variable_id", "option_id", "confidence", "freetext") VALUES (1105935, 11, 55, NULL, E'') RETURNING "krypdos_value"."id"

However, trying to run that results in the duplicate key error. The actual error is thrown in the code below.

# Delete current coding CodeRound.objects.filter( object_id=o.id, content_type=object_type, is_master=True ).delete() code_round = CodeRound( object_id=o.id, content_type=object_type, coded_by=request.user, comments=request.POST.get('_comments',None), is_master=True, ) code_round.save() for key in request.POST.keys(): if key[0] != '_' or key != 'csrfmiddlewaretoken': options = request.POST.getlist(key) for option in options: Value( code_round=code_round, variable_id=key, option_id=option, confidence=request.POST.get('_confidence_'+key, None), ).save() #This is where it dies # Resave to set is_correct code_round.save() o.status = '3' o.save()

I've checked the sequences and such and they seem to be in order. At this point I'm not sure what to do- I assume it's something on django's end but I'm not sure. Any feedback would be much appreciated!

Improve this question
3
  • 2
    An aside: By De Morgan's laws, your condition key[0] != '_' or key != 'csrfmiddlewaretoken' is equivalent to not (key[0] == '_' and key == 'csrfmiddlewaretoken'). It should be easy to see that the inner condition is never satisfied, so it's equivalent to not (False), or in other words True. But then why bother with the if? Commented Sep 21, 2013 at 0:06
  • 1
    python manage.py sqlsequencereset <app> | python manage.py dbshell Commented May 23, 2014 at 18:03
  • This previous answer gives greater detail and light on the subject: stackoverflow.com/questions/244243/… Commented Sep 2, 2015 at 16:38

14 Answers 14

Reset to default
194

This happend to me - it turns out you need to resync your primary key fields in Postgres. The key is the SQL statement:

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1);
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

That was it! I thought I had addressed that issue but it turns out I reset the wrong value.
I know of a bunch of people who have had this problem - I'm glad you resolved it !
I was about to ask @HackingLife if you knew more about why this happens... turns out for us, it was because we were syncing all the data from a different database by copying it directly (which is was zaphod mentioned in his answer). When we stopped doing that and started using the main database directly, the primary key sequence hadn't been being increment when a new model was added, producing this error.
I had this same problem after manually importing content via a SQL script and it was giving problems once I went back to my UI in Flask to add a new item. Updating the primary key field sequence did the trick for me. Thanks for the fix!
Wow.. I was using heroku and I had to move the db out to to amazon AWS RDS. I exported and imported to a fresh postgres db at aws and this issue appeared. Did reset id sequence to all the tables as mentioned above and it worked like a charm. Thank you!
|
47

It appears to be a known difference of behaviour between the MySQL and SQLite (they update the next available primary key even when inserting an object with an explicit id) backends, and other backends like Postgres, Oracle, ... (they do not).

There is a ticket describing the same issue. Even though it was closed as invalid, it provides a hint that there is a Django management command to update the next available key.

To display the SQL updating all next ids for the application MyApp:

python manage.py sqlsequencereset MyApp

In order to have the statement executed, you can provide it as the input for the dbshell management command. For bash, you could type:

python manage.py sqlsequencereset MyApp | python manage.py dbshell

The advantage of the management commands is that abstracts away the underlying DB backend, so it will work even if later migrating to a different backend.

Improve this answer

Comments

37

I had an existing table in my "inventory" app and I wanted to add new records in Django admin and I got this error:

Duplicate key value violates unique constraint "inventory_part_pkey" DETAIL: Key (part_id)=(1) already exists.

As mentioned before, I run the code below to get the SQL command to reset the id-s:

python manage.py sqlsequencereset inventory

Piping the python manage.py sqlsequencereset inventory | python manage.py dbshell to the shell was not working

  • So I copied the generated raw SQL command
  • Then opened pgAdmin3 https://www.pgadmin.org for postgreSQL and opened my db
  • Clicked on the 6. icon (Execute arbitrary SQL queries)
  • Copied the statement what was generated

In my case the raw SQL command was:

BEGIN; SELECT setval(pg_get_serial_sequence('"inventory_signup"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "inventory_signup"; SELECT setval(pg_get_serial_sequence('"inventory_supplier"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "inventory_supplier"; COMMIT;

Executed it with F5.

This fixed everything.

Improve this answer

2 Comments

This answer should be the one you actually need - it's the most right way for it!
Saved my day! Thanks. Execute arbitrary SQL queries is nowadays available under the Tools menu > Query Tool.
9

In addition to zapphods answer:

In my case the indexing was indeed incorrect, since I had deleted all migrations, and the database probably 10-15 times when developing as I wasn't in the stage of migrating anything.

I was getting an IntegrityError on finished_product_template_finishedproduct_pkey

Reindex the table and restart runserver:

I was using pgadmin3 and for whichever index was incorrect and throwing duplicate key errors I navigated to the constraints and reindexed.

enter image description here

And then reindexed.

enter image description here

Improve this answer

1 Comment

Reindex didn't work for me, however Analyze Full (with freeze & analyze options also checked) did the trick for me. I didnt have any sequence or trigger on table, but i figure some unfinished insert got stuck somewhere, so Vacuum Full helped.
8

The solution is that you need to resync your primary key fields as reported by "Hacking Life" who wrote an example SQL code but, as suggested by "Ad N" is better to run the Django command sqlsequencereset to get the exact SQL code that you can copy and past or run with another command.

As a further improvement to these answers I would suggest to you and other reader to dont' copy and paste the SQL code but, more safely, to execute the SQL query generated by sqlsequencereset from within your python code in this way (using the default database):

from django.core.management.color import no_style from django.db import connection from myapps.models import MyModel1, MyModel2 sequence_sql = connection.ops.sequence_reset_sql(no_style(), [MyModel1, MyModel2]) with connection.cursor() as cursor: for sql in sequence_sql: cursor.execute(sql)

I tested this code with Python3.6, Django 2.0 and PostgreSQL 10.

Improve this answer

1 Comment

Please don't post identical answers to multiple questions. Post one good answer, then vote/flag to close the other questions as duplicates. If the question is not a duplicate, tailor your answers to the question.
5

If you want to reset the PK on all of your tables, like me, you can use the PostgreSQL recommended way:

SELECT 'SELECT SETVAL(' || quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname)) || ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM ' || quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';' FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C, pg_tables AS PGT WHERE S.relkind = 'S' AND S.oid = D.objid AND D.refobjid = T.oid AND D.refobjid = C.attrelid AND D.refobjsubid = C.attnum AND T.relname = PGT.tablename ORDER BY S.relname;

After running this query, you will need to execute the results of the query. I typically copy and paste into Notepad. Then I find and replace "SELECT with SELECT and ;" with ;. I copy and paste into pgAdmin III and run the query. It resets all of the tables in the database. More "professional" instructions are provided at the link above.

Improve this answer

Comments

3

If you have manually copied the databases, you may be running into the issue described here.

Improve this answer

Comments

3

I encountered this error because I was passing extra arguments to the save method in the wrong way.

For anybody who encounters this, try forcing UPDATE with:

instance_name.save(..., force_update=True)

If you get an error that you cannot pass force_insert and force_update at the same time, you're probably passing some custom arguments the wrong way, like I did.

Improve this answer

3 Comments

This a nice solution within django. From the shell just import your model class (if you aren't using shell_plus) and then do MyModelClass.objects.first().save(force_update=True)
In my case i had to use both force_insert and force_update because i had override the save method and calling the super 2 times. On the first save, set force_insert to True and force_update to False. On the second save set force_insert to False and force_update to True.
So, what was the actual cause of the error? It says "I was passing extra arguments to the save method in the wrong way", but does not give any details. Perhaps the "brute force" workaround of using force_update is not always appropriate.
1

This question was asked about 9 years ago, and lots of people gave their own ways to solve it.

For me, I put unique=True in my email custom model field, but while creating superuser I didn't ask for the email to be mandatory.

Now after creating a superuser my email field is just saved as blank or Null. Now this is how I created and saved new user

obj = mymodel.objects.create_user(username='abc', password='abc') obj.email = '[email protected]' obj.save()

It just threw the error saying duplicate-key-value-violates in the first line because the email was set to empty by default which was the same with the admin user. Django spotted a duplicate !!!

Solution

  • Option1: Make email mandatory while creating any user (for superuser as well)
  • Option2: Remove unique=True and run migrations
  • Option3: If you don't know where are the duplicates, you either drop the column or you can clear the database using python manage.py flush

It is highly recommended to know the reason why the error occurred in your case.

Improve this answer

Comments

0

I was getting the same error as the OP.

I had created some Django models, created a Postgres table based on the models, and added some rows to the Postgres table via Django Admin. Then I fiddled with some of the columns in the models (changing around ForeignKeys, etc.) but had forgotten to migrate the changes.

Running the migration commands solved my problem, which makes sense given the SQL answers above.

To see what changes would be applied, without actually applying them:
python manage.py makemigrations --dry-run --verbosity 3

If you're happy with those changes, then run:
python manage.py makemigrations

Then run:
python manage.py migrate

Improve this answer

Comments

0

I was getting a similar issue and nothing seemed to be working. If you need the data (ie cant exclude it when doing dump) make sure you have turned off (commented) any post_save receivers. I think the data would be imported but it would create the same model again because of these. Worked for me.

Improve this answer

Comments

0

You just have to go to pgAdmin III and there execute your script with the name of the table:

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1);
Improve this answer

1 Comment

How is this any different from the accepted answer?
0

Based on Paolo Melchiorre's answer, I wrote a chunk as a function to be called before any .save()

from django.db import connection def setSqlCursor(db_table): sql = """SELECT pg_catalog.setval(pg_get_serial_sequence('"""+db_table+"""', 'id'), MAX(id)) FROM """+db_table+""";""" with connection.cursor() as cursor: cursor.execute(sql)
Improve this answer

Comments

0

This is the right statement. Mostly, It happens when we insert rows with id field.

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename));
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.