Alter table to add 'partition by range' in postgres

Question

I want to alter table with adding partition by range with archivedate.

CREATE TABLE transactions(
    id              UUID         not null ,
    txn_id          UUID         NOT NULL,
    orderId         UUID         NOT NULL,
    inserttstmp     timestamp    not NULL
    archivedate     timestamp    NULL
)

usually, whenever i'm creating new table with partition with below script. But now want to do same for existing tables.

CREATE TABLE attachment(
    id              UUID            not null,
    txn_id          UUID         NOT NULL,
    attachment_id   UUID         NOT NULL,
    inserttstmp     timestamp    not NULL
    archivedate     timestamp    NULL
)PARTITION BY RANGE (archivedate);

CREATE TABLE ins_txn_attachment_live PARTITION OF ins_txn_attachment DEFAULT;
ALTER  TABLE ins_txn_attachment_live ADD CHECK (archivedate is null);
ALTER  TABLE ins_txn_attachment_live ADD CONSTRAINT PK_INS_TXN_ATTACHMENT_EVENT_Live unique (id);

CREATE UNIQUE NONCLUSTERED INDEX PK_Ins_TXN_Attachment_ID_ArchiveData ON ins_txn_attachment (id,archivedate);

Thanks, Jagadeesh

Please check https://stackoverflow.com/questions/53600144/how-to-migrate-an-existing-postgres-table-to-partitioned-table-as-transparently — dassum, Dec 30 '19 at 15:40

Stavros Koureas · Answer 1 · 2023-06-12T10:16:16.153

From the questing I believe you want to change or add partition column on an existing table, in this case ins_txn_attachment_live. One easy way I see and I personally follow when there is no option to alter a table for a specific property, like partition or change the position of fields inside a table is the following:

First rename your table and including keys or indexes (in addition do not give prefix of PK in PostgreSQL indexes, as PostgreSQL creates out of the box primary key indexes without being visible in PGAdmin):

ALTER TABLE ins_txn_attachment_live RENAME TO ins_txn_attachment_live_old
ALTER INDEX PK_Ins_TXN_Attachment_ID_ArchiveDataRENAME TO PK_Ins_TXN_Attachment_ID_ArchiveData_old

Then create your new table with the desired partition:

CREATE TABLE ins_txn_attachment_live (
    id              UUID         NOT NULL,
    attachment_id   UUID         NOT NULL,
    inserttstmp     timestamp    not NULL
    archivedate     timestamp    NULL
)PARTITION BY RANGE (<whatever column>);

Then copy your data from _old table to the new table (if the source and destination columns are the same):

INSERT INTO ins_txn_attachment_live SELECT * FROM ins_txn_attachment_live_old

Then copy your data from _old table to the new table (in case the source and destination columns are different):

INSERT INTO ins_txn_attachment_live (id, attachment_id, inserttstmp, archivedate, new_column) SELECT id, attachment_id, inserttstmp, archivedate, 'new_default_value' FROM ins_txn_attachment_live_old

Hope it helps

Alter table to add 'partition by range' in postgres

1 Answers1