Re: How to attach partition with primary key

Thank you for replying! I've checked `pg_dump -s`, but it didn't help - the definition here was exactly the same (except order, but I didn't notice it since in my actual schema there are thousands of rows).

I've done a bad job describing my issue in the first place: I left out a key definition statement that I thought doesn't play any role in the issue: another unique index on the same columns as PK.

My actual script was:

```sql
CREATE TABLE "Transactions_202405" (LIKE "Transactions" INCLUDING DEFAULTS);
INSERT INTO "Transactions_202405" SELECT * FROM "Transactions_202405_parted";
alter table "Transactions_202405" add primary key (id, "createdAt");
alter table "Transactions_202405" add foreign key (uid) references "User" on update cascade on delete restrict;
create index "Transactions_202405_createdAt_idx" on "Transactions_202405" ("createdAt" desc);
create index "Transactions_202405_type_idx" on "Transactions_202405" (type);
create index "Transactions_202405_uid_idx" on "Transactions_202405" (uid);
create unique index "Transactions_202405_id_createdAt_idx" on "Transactions_202405" (id desc, "createdAt" desc);
alter table "Transactions_202405" add constraint "Transactions_202405_check" check ("createdAt">='2024-05-01' and "createdAt"<'2024-06-01');
alter table "Transactions" attach partition "Transactions_202405" for values from ('2024-05-01') TO ('2024-06-01');
alter table "Transactions_202405" drop constraint "Transactions_202405_check";
```

So as you can see on the 8th line, there is a definition of another unique index. I left it out since I wanted to provide the minimal code for the problem. This index is defined on all partitions and the main partitioned table.

The solution I found is the following: to move `alter table ... add primary key ...` statement after the unique index definition. After that it starts to work like a charm. I tried all combinations of row order, but only when I move this line after the unique index - it starts working.

Interesting thing is that if I define PK before the unique index and then drop it and redefine after the unique index, then the code still doesn't work. This behaviour smells like a bug on the PostgreSQL side...

I've found a minimal reproducible example:

```sql
create table "Transactions"
(
    id          bigserial                                                       not null,
    uid         uuid                                                            not null,
    type        varchar(255)                                                    not null,
    amount      numeric(26, 10)                                                 not null,
    "createdAt" timestamp(3) default CURRENT_TIMESTAMP                          not null
) partition by RANGE ("createdAt");
create unique index "Transactions_id_createdAt_idx" on "Transactions" (id desc, "createdAt" desc);
alter table "Transactions" add primary key (id, "createdAt");

create table "Transactions_202403" (LIKE "Transactions" INCLUDING DEFAULTS);
alter table "Transactions_202403" add primary key (id, "createdAt");
create unique index "Transactions_202403_id_createdAt_idx" on "Transactions_202403" (id desc, "createdAt" desc);
alter table "Transactions" attach partition "Transactions_202403" for values from ('2024-03-01') to ('2024-04-01');
```

If I change the order of PK and unique index statements either in the first block or second - the script breaks. Seems like PostgreSQL requires you to define constraints and indexes in exactly the same order as the partitioned table. Sounds buggy, but kinda logical.

Thank you and sorry for the incorrect question definition.