CODEWITHSUNDEEP
postgresqlsql-deleteunique-constraint
Sachin
Sachin

Reputation: 67

Duplicate Key violation unique constraint PostgreSQL History Tables

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?

Upvotes: 0

Views: 45

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247615

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.

Upvotes: 1

Related Questions