Database Update Order

Question

I am faced with a situation in our database where I need to shift the timestamps of several records per day, however I have a unique restriction that requires the id field and the label field to be unique.

Here is a description of the table.

  Table "public.eedata"
    Column |  Type |  Modifiers                          
 ------------- + -------------------------------- + --- -------------------------------------------------- -------
  eedata_id |  bigint |  not null default nextval ('eedata_eedata_id_seq' :: regclass)
  user_id |  integer | 
  eeupload_id |  bigint | 
  eetimestamp |  timestamp (0) without time zone | 
 Indexes:
     "pk_eedata" PRIMARY KEY, btree (eedata_id)
     "eedata_user_id_key" UNIQUE, btree (user_id, eetimestamp)
     "fki_eeuploadid" btree (eeupload_id)
 Foreign-key constraints:
     "fk_eeupload_id" FOREIGN KEY (eeupload_id) REFERENCES eeupload (eeupload_id) ON UPDATE CASCADE ON DELETE CASCADE
     "fk_user_id" FOREIGN KEY (user_id) REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE

The problem in this case is caused by the eedata_user_id_key restriction. I can successfully subtract the day using

  update eedata set eetimestamp = eetimestamp - interval '1 day' where eeupload_id = xxx; 

because the order in which the update is applied prevents any collisions, however, when I try

  update eedata set eetimestamp = eetimestamp + interval '1 day' where eeupload_id = xxx; 

I get

  ERROR: duplicate key violates unique constraint "eedata_user_id_key" 

What I need to do is to specify the order in which the update is applied (in fact, the order for the update operator) or the ability to suspend the restriction for one update operator.

I am using Postgres 8.1.11 if that matters.

Answer 1

Could you just remove the time limit for your update and then add it back when done?

Answer 2

I recently had a similar problem - I have test data that ends in September 2008, and I need it to be very recent. I moved it for six months, but had several duplicates. I needed to detect duplicates before creating them and make UPDATE from existing rows instead of INSERT new rows.