Postgres SQL Table Partitioning by Range Timestamp not Unique key Collision

Question

I have an issue when trying to modify and existing PostgreSQL (version 13.3) table to support partitioning it gets stuck when inserting the new data from the old table because the inserted timestamp in some cases may not be unique, so it fails on execution.

The partition forces me to create the primary to be the range (timestamp) value. You can see the new table definition below:

CREATE TABLE "UserFavorites_master" (
    "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "UserId" int4 NOT NULL,
    "CardId" int4 NOT NULL,
    "CreationDate" timestamp NOT NULL,
    CONSTRAINT "PK_UserFavorites_CreationDate" PRIMARY KEY ("CreationDate")
) partition by range ("CreationDate");

The original table didn't have a constraint on timestamp to either be unique or a primary key nor would we particularly want that but that seems to be a requirement of partitioning. Looking for alternatives or good ideas to solve the issue.

You can see the full code below:

alter table "UserFavorites" rename to "UserFavorites_old";

CREATE TABLE "UserFavorites_master" (
    "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "UserId" int4 NOT NULL,
    "CardId" int4 NOT NULL,
    "CreationDate" timestamp NOT NULL,
    CONSTRAINT "PK_UserFavorites_CreationDate" PRIMARY KEY ("CreationDate")
) partition by range ("CreationDate");

-- Frome Reference: https://stackoverflow.com/a/53600145/1190540
create or replace function createPartitionIfNotExists(forDate timestamp) returns void
as $body$
declare yearStart date := date_trunc('year', forDate);
    declare yearEndExclusive date := yearStart + interval '1 year';
    declare tableName text := 'UserFavorites_Partition_' || to_char(forDate, 'YYYY');
begin
    if to_regclass(tableName) is null then
        execute format('create table %I partition of "UserFavorites_master" for values from (%L) to (%L)', tableName, yearStart, yearEndExclusive);
        -- Unfortunatelly Postgres forces us to define index for each table individually:
        --execute format('create unique index on %I (%I)', tableName, 'UserId'::text);
    end if;
end;
$body$ language plpgsql;

do
$$
declare rec record;
begin
loop
    for rec in 2015..2030 loop
        -- ... and create a partition for them
        perform createPartitionIfNotExists(to_date(rec::varchar,'yyyy'));
    end loop;
end
$$;

create or replace view "UserFavorites" as select * from "UserFavorites_master";
insert into "UserFavorites" ("Id", "UserId", "CardId", "CreationDate") select * from "UserFavorites_old";

It fails on the Last line with the following error:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "UserFavorites_Partition_2020_pkey"
  Detail: Key ("CreationDate")=(2020-11-02 09:38:54.997) already exists.
  ERROR: duplicate key value violates unique constraint "UserFavorites_Partition_2020_pkey"
  Detail: Key ("CreationDate")=(2020-11-02 09:38:54.997) already exists.
  ERROR: duplicate key value violates unique constraint "UserFavorites_Partition_2020_pkey"
  Detail: Key ("CreationDate")=(2020-11-02 09:38:54.997) already exists.

"seems to be a requirement of partitioning" It certainly is not. What makes you think that? If you want to have a primary key, it must **include** the partitioning column. If it consists entirely of the partitioning column, that would make partitioning rather silly. — jjanes, Sep 01 '21 at 14:12

score 3 · Accepted Answer · answered Sep 01 '21 at 12:07

No, partitioning doesn't force you to create a primary key. Just omit that line, and your example should work.

However, you definitely always should have a primary key on your tables. Otherwise, you can end up with identical rows, which is a major headache in a relational database. You might have to clean up your data.

score 1 · Answer 2 · answered Sep 12 '21 at 11:55

@Laurenz Albe is correct, it seems I also have the ability to specify multiple keys though it may affect performance as referenced here Multiple Keys Performance, even indexing the creation date of the partition seemed to make the performance worse.

You can see a reference to multiple keys below, you mileage may vary.

CREATE TABLE "UserFavorites_master" (
    "Id" int4 NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    "UserId" int4 NOT NULL,
    "CardId" int4 NOT NULL,
    "CreationDate" timestamp NOT NULL,
    CONSTRAINT "PK_UserFavorites" PRIMARY KEY ("Id", "CreationDate")
) partition by range ("CreationDate");

Postgres SQL Table Partitioning by Range Timestamp not Unique key Collision

2 Answers2