Making “Primary Key” for a Partitioned Table in Postgresql

Recently I found out that PostgreSQL behavior for the partitioned table is mildly infuriating. I need to create a partitioned table since we calculated that the size of the table will be huge in the future. We need the table to solely have a single primary key, let’s call it “id”, and it will be partitioned by the timestamp field. Below is the statement example of the table I tried to create.

create table public.user_history (
    id bigserial not null primary key,
    event_time timestamp default now() not null,
    event_name varchar not null
) partition by range (event_time);

This resulted in an error:

SQL Error [0A000]: ERROR: unique constraint on partitioned table must include all partitioning columns.
Detail: PRIMARY KEY constraint on table “user_history” lacks column “event_time” which is part of the partition key.

PostgreSQL error message

This is pretty obvious on their documentation page.

To create a unique or primary key constraint on a partitioned table, the partition keys must not include any expressions or function calls and the constraint’s columns must include all of the partition key columns.

PostgreSQL documentation on partition limitation.¹https://www.postgresql.org/docs/15/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE

Upon trying to find the solution, many suggested that I add the column partition as composite primary keys. In my case, this is unacceptable since making both “id” and “event_time” as a composite primary key means that it lost the uniqueness of the “id” column itself. It’s because the table will accept the same “id” with a different “event_time”.

create table public.user_history (
    id bigserial not null,
    event_time timestamp default now() not null,
    event_name varchar not null
) partition by range (event_time);
create index user_history_id_idx on only public.user_history using btree (id);
create index user_history_event_time_idx on only public.user_history using btree (event_time);

Then we create a PL/SQL function which will be triggered each time a new row is inserted.

create or replace function user_history_update_on_duplicate_id()
returns trigger as $$
    begin
        if exists (select 1 from user_history where id = new.id) then
            update user_history
            set event_timestamp = new.event_timestamp,
                event_name = new.event_name
            where id = new.id;
            return null;
        end if;
        return new;
    end;
$$ language plpgsql;

Or the function can simply ignore the duplicate as if the “do nothing” statement.

create or replace function user_history_update_on_duplicate_id()
returns trigger as $$
    begin
        if exists (select 1 from user_history where id = new.id) then
            return null;
        end if;
        return new;
    end;
$$ language plpgsql;

Finally, we create a trigger on the table in the event of data insertion.

create trigger update_if_duplicate_id
before insert on user_history
for each row execute function user_history_update_on_duplicate_id();