How to get around the error ERROR: duplicate key value violates unique constraint?

Question

linux

I am doing a time update:
update sometable set ts = ts + $Date_dif
there is a ts field in the sometable table:
Column | type | Modifiers | storage | Description
------------------+-------------------- --+-----------+----------+-------------
ts | integer | not null | plain |
it is also marked as PRIMARY KEY (and not only it)
in ts the time is written in unix_time format. I add some difference in the unix_time format to all values ​​of the ts field and thus update the time in the database as if the data was written recently.
The first time I got it right. When I tried to update the time again, it gave an error:
ERROR: duplicate key value violates unique constraint
How can I get around this? (similar problem with MySQL database, gives something like: ERROR 1062 (23000) at line 1: Duplicate entry '1439933444-stat-localhost' for key 'PRIMARY' )
How to get around? There are options? I need to be able to update the data as many times as needed.

Answer 1

Update, just don't create duplicates, it's written in human language in an error.
It is possible (necessary) to make sure that the update is generally possible before updating.

Answer 2

I think that the problem is that when updating records, duplicates appear at some point in time (i.e. there was field 3 and 6, we do +3 and first we get field 3, i.e. there will be 6, which conflicts with the existing one 6).
You either need to change the PRIMARY KEY to DEFERRABLE (so that the check is performed when the transaction ends), or you need to update the data in sorted form, i.e. add to the newest entries in the table first.

Answer 3

In Postgresql 9.5, you can use ON CONFLICT DO NOTHING.

Answer 4

TRUNCATE TABLE youtable CONTINUE IDENTITY CASCADE; clear the data of the tables with the trunk and then do the replication