postgresql 对已经存在的表进行分区

对老表改名

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

　　

参考复制表: https://www.cnblogs.com/pythonClub/p/17472584.html , 如果有结构等其他

使用其名字建立新表

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>);　

 

 

按结构复制

CREATE TABLE testdb 
(like testdb_old)
partition by range(vdate)

　　

 

从老表移数据

INSERT INTO ins_txn_attachment_live SELECT * FROM ins_txn_attachment_live_old

　　

如果列不一样,则

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