Create partitioned table from existing table

Question

Given the following table:

create table widget (
 id serial primary key,
 description text,
 created_at timestamptz not null default current_timestamp
);

I try to create a partitioned copy:

create table widget2 (like widget including all)
 partition by range (created_at);

But this fails with the error

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

So I alter the original table to satisfy the requirements:

alter table widget
 add unique (id),
 drop constraint widget_pkey,
 add primary key (id, created_at);

But even after doing this, a similar error is returned:

SQL Error [0A000]: ERROR: unique constraint on partitioned table must include all partitioning columns
 Detail: UNIQUE constraint on table "widget2" lacks column "created_at" which is part of the partition key.

Isn't the unique constraint implicitly added by the primary key constraint? In any case, I add the unique constraint manually anyway:

alter table widget add unique (id, created_at);

But re-running create table widget2 (like widget including all) partition by range (created_at); after still gives the same error

SQL Error [0A000]: ERROR: unique constraint on partitioned table must include all partitioning columns
 Detail: UNIQUE constraint on table "widget2" lacks column "created_at" which is part of the partition key.

Table widget DDL after the 2 alterations:

CREATE TABLE public.widget (
 id serial4 NOT NULL,
 description text NULL,
 created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT widget_id_created_at_key UNIQUE (id, created_at),
 CONSTRAINT widget_id_key UNIQUE (id),
 CONSTRAINT widget_pkey PRIMARY KEY (id, created_at)
);

The created_at partition key is already included in the primary key constraint and a manually added unique constraint. So why is the partitioning still rejected? Is this a bug or am I missing something?

• jkavalik: You dropped the PRIMARY KEY constraint, but not the UNIQUE index that is backing it up. Everything else you did after that had no chance to work because the CONSTRAINT widget_id_key UNIQUE (id) was still there.
• jkavalik: (Or maybe that unique constraint was created separately?)
• erwinv: @jkavalik I recreated the widget_id_key manually. I did so because I was concerned about the queries that rely on the unique index from the old primary key. Now that I've read the answers, I think I can't (or shouldn't) use create table ... (like ... including all) partition by ... since doing so would require re-creating all unique indexes (to add the partition key to each one) on the old table. I don't want to reindex since this defeats my original purpose for partitioning, which is to keep only the most recent records.
• jkavalik: Yes, you could instead use like to create an empty temporary copy, modify what is needed only and then create the partitioned one. On the other hand the partitioned version does not seem useful if its definition is dangerous when used on non-partitioned version.
• erwinv: @jkavalik I didn't think of that. Modifying only a temporary seems a great idea. I will try it. Thanks!

Answer 1

Every unique index must contain the partitioning key, not just some. Documentation says:

This limitation exists because the individual indexes making up the constraint can only directly enforce uniqueness within their own partitions; therefore, the partition structure itself must guarantee that there are not duplicates in different partitions.

Answer 2

Credits to @jkavalik for the temporary copy idea.

begin;
create table widget_temp (like widget including all);
alter table widget_temp
-- add the following 2 lines for every other unique constraint (aside from the primary key) in the original table:
-- drop constraint widget_temp_{column}_key,
-- add unique ({column}, created_at),
 drop constraint widget_temp_pkey,
 add primary key (id, created_at);
create table widget2 (like widget_temp including all)
 partition by range (created_at);
drop table widget_temp;
commit;