Duplicate Key violation unique constraint PostgreSQL History Tables

Question

I have table_1 and table_1_history in PostgreSQL. I have setup a trigger for insert, update, delete on table_1. So any changes in table_1 will reflect to table_1_history and will create a new row. This is working fine.

Begin

    If (TG_OP = 'DELETE') THEN
        INSERT INTO table_1_history SELECT OLD.*;
    ELSEIF (TG_OP = 'UPDATE') THEN
        INSERT INTO table_1_history SELECT NEW.*;
    ELSEIF (TG_OP = 'INSERT') THEN
        INSERT INTO table_1_history SELECT NEW.*;
    END IF;
    RETURN NULL;
END;

PK for table_1 = id, effective_date PK for table_1_history = id, effective_date, update_timestamp

I have a new requirement to delete records from table_1 which are older than 30 days. While doing so I am getting error the "record_1 already exists in history which violates unique constraint". To make it work I need to delete record_1 from table_1_history or change update timestamp of table_1_history.

Is there any other way to handle this scenario?

1) This record_1 already exists in history ... does not match the table_1_history table name. Copy and paste error? 2) How is update_timestamp created? — Adrian Klaver
– Adrian Klaver, Commented Jan 15 at 21:45
Error - Key(id,effective_date,update_timestamp) = (id1, 11/18/2024 12:00:00 AM, 11/18/2024 9:17:32 PM) already exists. duplicate key value unique constraint "pid_sec_id_hist" update_timestamp in table_1 and table_1_history are same for the record as record_1 is written from table_1 to table_1_history — Sachin
– Sachin, Commented Jan 15 at 21:53
You don't see the issue ... update_timestamp in table_1 and table_1_history are same for the record? If the record is updated(assuming that is when update_timestamp is set) then the DELETE will have the same update_timestamp as existing record in the history table. You need to add a field in the history table that makes each row unique, quick option would be auto increment field using GENERATE IDENTITY. — Adrian Klaver
– Adrian Klaver, Commented Jan 15 at 22:05

Laurenz Albe · Accepted Answer · 2025-01-16 06:04:19Z

I think that the mistake is that your trigger insets the deleted row into the history table. That row is already in the history table — it got inserted when the row was last inserted or updated. Upon deletion, you are trying to insert the same old row again, which leads to a constraint violation.

I think you should design your historization better. What information do you want to get from the history table? Perhaps you want to record if the row was inserted, updated or deleted? Perhaps you want to record the time of deletion? Adding the current timestamp and the type of operation to the primary key should solve the problem.

Collectives™ on Stack Overflow

Duplicate Key violation unique constraint PostgreSQL History Tables

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related