pgsql表分区添加修改

excel增加表分区SQL

excel文件：表分区创建.xlsx

添加表分区

#删除旧索引 并重命名
ALTER TABLE xdata_klines_5m DROP CONSTRAINT pk_okx_candles_1m;
ALTER TABLE xdata_klines_5m RENAME TO xdata_klines_5m_00;

create table xdata_klines_5m (
   b_id                 INT2                 not null default 0,
   t_uid                INT8                 not null default 0,
   o                    DECIMAL(20,10)       null default 0.00,
   h                    DECIMAL(20,10)       null default 0.00,
   l                    DECIMAL(20,10)       null default 0.00,
   c                    DECIMAL(20,10)       null default 0.00,
   vol                  DECIMAL(20,2)        null default 0.00,
   vol_b                DECIMAL(20,2)        null default 0.00,
   vol_u                DECIMAL(20,2)        null default 0.00,
   ts                   TIMESTAMP            null,
   constraint PK_OKX_KLINES_5M primary key (b_id, t_uid)
) partition by range (t_uid);

ALTER TABLE xdata_klines_5m  ATTACH PARTITION xdata_klines_5m_00 FOR VALUES FROM (MINVALUE) TO (8418861);

create table xdata_klines_5m_04 partition of xdata_klines_5m FOR VALUES FROM (8500321) TO (8629921);
create table xdata_klines_5m_05 partition of xdata_klines_5m FOR VALUES FROM (8500321) TO (8629921);
create table xdata_klines_5m_06 partition of xdata_klines_5m FOR VALUES FROM (8500321) TO (8629921);

后期增加表分区

ALTER TABLE xdata_klines_5m ATTACH PARTITION xdata_klines_5m_01 FOR VALUES FROM (MINVALUE) TO (8241121);
ALTER TABLE xdata_klines_5m ATTACH PARTITION xdata_klines_5m_02 FOR VALUES FROM (8241121) TO (8500321);
ALTER TABLE xdata_klines_5m ATTACH PARTITION xdata_klines_5m_03 FOR VALUES FROM (8500321) TO (8629921);

删除表分区

ALTER TABLE xdata_klines_5m DETACH PARTITION xdata_klines_5m_02;
ALTER TABLE xdata_klines_5m DETACH PARTITION xdata_klines_5m_03;