Postgresql 常用表分区SQL

场景一 按照时间范围分区

单列形式

-- 创建主表
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

-- 创建分区表， 2022年1月1日0点 - 2022年1月31日23点59分59秒
CREATE TABLE IF NOT EXISTS measurement_2022_02 PARTITION OF measurement
    FOR VALUES FROM ('2022-01-01') TO ('2022-02-01');
    
-- 创建分区表， 2022年1月1日0点 - 2022年1月31日23点59分59秒  
CREATE TABLE IF NOT EXISTS measurement_2022_03 PARTITION OF measurement
    FOR VALUES FROM ('2022-02-01') TO ('2022-03-01');
    
-- 创建默认分区
CREATE TABLE IF NOT EXISTS measurement_def PARTITION OF measurement DEFAULT;

多列形式

-- 创建主表
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (EXTRACT(YEAR FROM logdate), EXTRACT(MONTH FROM logdate));

-- 创建分区表， 2022年1月1日0点 - 2022年1月31日23点59分59秒
CREATE TABLE IF NOT EXISTS measurement_2022_02 PARTITION OF measurement
    FOR VALUES FROM (2022, 01) TO (2022, 02);
    
-- 创建分区表， 2022年1月1日0点 - 2022年1月31日23点59分59秒  
CREATE TABLE IF NOT EXISTS measurement_2022_03 PARTITION OF measurement
    FOR VALUES FROM (2022, 02) TO (2022, 03);
    
-- 创建默认分区
CREATE TABLE IF NOT EXISTS measurement_def PARTITION OF measurement DEFAULT;

创建外部分区

CREATE TABLE measurement_2022_04
  (LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS)
  TABLESPACE fasttablespace;

装载分区

ALTER TABLE measurement ATTACH PARTITION measurement_2022_04
    FOR VALUES FROM ('2022-04-01') TO ('2022-05-01' );

移除分区

ALTER TABLE measurement DETACH PARTITION measurement_2022_02;

删除分区

DROP TABLE measurement DETACH PARTITION measurement_2022_02;

设置唯一约束

# 创建默认名称的约束（measurement_key）
ALTER TABLE measurement ADD UNIQUE (city_id, logdate);
# 创建指定名称的约束
ALTER TABLE measurement ADD CONSTRAINT measurement_uni_key UNIQUE (city_id, logdate);

添加索引

CREATE INDEX measurement_usls_idx ON measurement (unitsales);

参照

http://www.postgres.cn/docs/13/ddl-partitioning.html
http://www.postgres.cn/docs/13/sql-createtable.html