duplicate key value violates unique constraint

Делаю update времени:
update sometable set ts = ts + $Date_dif

оно же помечено как PRIMARY KEY (и не только оно)

в ts записано время в формате unix_time. Я прибавляю ко всем значениям поля ts некую разницу в формате unix_time и таким образом обновляю время в БД будто данные были записаны недавно.

Первый раз у меня всё вышло. Когда я пробовал обновить время ещё раз — выдавало ошибку:
ERROR: duplicate key value violates unique constraint

Как можно обойти это? (аналогичная проблема с и БД MySQL, выдает что-то вроде: ERROR 1062 (23000) at line 1: Duplicate entry ‘1439933444-stat-localhost’ for key ‘PRIMARY’ )

Как обойти? Есть варианты? Мне надо иметь возможность обновлять данные столько раз сколько потребуется.

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:

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:

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:

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

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!

I’m using psql in my Laravel App. I’m trying to create my user, and I keep getting this error

Unique violation: 7 ERROR: duplicate key value violates unique constraint «users_pkey»

Did I do anything that I’m not suppose to?

What I am having right now used to work when I hook my app with MySQL .

Any hints / suggestions will mean a lot to me.

1 Answer 1

Postgres handles auto incrementing a little differently than MySQL does. In Postgres, when you create the serial field, you are also creating a sequence field that is keeping track of the id to use. This sequence field is going to start out with a value of 1.

When you insert a new record into the table, if you don’t specify the id field, it will use the value of the sequence, and then increment the sequence. However, if you do specify the id field, then the sequence is not used, and it is not updated, either.

I’m assuming that when you moved over to Postgres, you seeded or imported some existing users, along with their existing ids. When you created these user records with their ids, the sequence was not used, and therefore it was never updated.

So, if, for example, you imported 10 users, you have users with ids 1-10, but your sequence is still at 1. When you attempt to create a new user without specifying the id, it pulls the value from the sequence (1), and you get a unique violation because you already have a user with id 1.

To resolve the issue, you need to set your users_id_seq sequence value to the MAX(id) of your existing users. You can read this question/answer for more information on resetting the sequence, but you can also try something like (untested):

FYI, this is not an issue in MySQL because MySQL automatically updates the auto increment sequence to the largest column value when a value is manually inserted into the auto incrementing field.

Источник: computermaker.info