I have an existing table with following schema

create table event_logs
    event_id     integer   not null
        constraint event_logs_event_id_fkey
            references event,
    user_id         integer   not null,
    transaction_type_id   integer   not null
        constraint event_logs_transaction_type_id_fkey
            references transaction_type,
    transaction_timestamp timestamp not null,
    created_at      timestamp with time zone,
    transaction_data      jsonb

create index event_created_at_index
    on event_logs (created_at);

create index event_transaction_type_id_index
    on event_logs (transaction_type_id);

Basically above table is to store any user transaction for a given online event like joining , chatting, leaving etc. When this table was created we forgot to add unique constraint across these columns - event_id, user_id, transaction_type_id, transaction_timestamp. Now the database has ~13,293,556 rows with a lot of duplicates. I am unable to add Unique constraint because of duplicates and when I try to remove duplicate rows,the query timesout. Can you please suggest how I can add unique constraint to avoid any duplicate transaction logs in future.

I am using PostgreSQL 11.12 database

  • 1
    Hi, and welcome to dba.se! I tried this-see here. You can have NOT VALID CHECK constraints, but not for UNIQUE ones. I think you're going to have to write a trigger to do this from now on! Or, you could bite the bullet and do some sort of mass DELETE - I'd backup first though - either that or put them into some sort of holding table - 13M records shouldn't be too painful? (*) NOT VALID means it will only be enforced into the future! Aug 12 '21 at 12:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.