二百八十五、华为云PostgreSQL——建分区表并设置主键

一、目的

在PostgreSQL里建表，设置主键，三个字段确认数据的唯一性。设置分区字段，按月分区

二、PostgreSQL版本

三、PostgreSQL 9.2.4 版本缺点

在 PostgreSQL 9.2.4 中，虽然你可以创建分区表，但需要注意的是，该版本中的分区功能并不像后续版本那样成熟和灵活。
特别是，它不支持直接使用复合主键进行分区，而且分区键通常必须是表中的一个实际字段（尽管可以通过表达式索引等方式进行变通，但这种方法在 9.2.4 中可能不受支持或不够直观）。

四、建库建表

4.1 建库

create schema if not exists etc_dwd;

4.2 建表

4.2.1 建表逻辑

以traffic_id、pay_type和pay_year做主键，以pay_year分做分区
traffic_id,vehicle_id,member_detail_id,pay_year,pay_month字段为varchar;
bill_type,pay_type,pay_channel_id,pay_amount字段类型为int;
bill_created_at,pay_at字段类型为timestamp;
pay_day字段类型为date

注意点

1、主键必须包含分区字段

2、PostgreSQL日期类型有限

4.2.2 建表SQL

create table etc_dwd.traffic_payment (traffic_id       int8,vehicle_id       int8,member_detail_id int8,bill_type        int4,pay_type         int4,pay_channel_id   int4,pay_amount       int8,bill_created_at  timestamp,pay_at           timestamp ,pay_year         varchar(4) ,pay_month        varchar(7),pay_day          date,PRIMARY KEY (traffic_id, pay_type,pay_month)
)
-- 设置列存储
WITH (orientation = COLUMN,enable_hstore_opt = ON,compression = middle,colversion = 3.0,enable_delta = FALSE,enable_hstore = TRUE,enable_turbo_store = TRUE
)
TABLESPACE cu_obs_tbs
-- 按 pay_month 进行列表分区
PARTITION BY LIST (pay_month) (PARTITION p_unknown VALUES ((''))
);

4.2.3 增加分区

ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_01 VALUES ('2025-01');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_02 VALUES ('2025-02');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_03 VALUES ('2025-03');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_04 VALUES ('2025-04');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_05 VALUES ('2025-05');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_06 VALUES ('2025-06');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_07 VALUES ('2025-07');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_08 VALUES ('2025-08');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_09 VALUES ('2025-09');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_10 VALUES ('2025-10');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_11 VALUES ('2025-11');
ALTER TABLE etc_test.traffic_payment ADD PARTITION p_2025_12 VALUES ('2025-12');

4.2.4 增加表字段注释

comment on column etc_dwd.traffic_payment.traffic_id is '通行账单id';
comment on column etc_dwd.traffic_payment.vehicle_id is '车辆id';
comment on column etc_dwd.traffic_payment.member_detail_id is '用户实名ID';
comment on column etc_dwd.traffic_payment.bill_type is '账单类型 1 通行费 2 服务费 3 通道费 4 滞纳金 5 返白费';
comment on column etc_dwd.traffic_payment.pay_type is '支付类型 0 自动划扣 1 主动还款 2 人工结清';
comment on column etc_dwd.traffic_payment.pay_channel_id is '支付渠道id';
comment on column etc_dwd.traffic_payment.pay_amount is '支付金额';
comment on column etc_dwd.traffic_payment.bill_created_at is '账单请款时间(创建时间)';
comment on column etc_dwd.traffic_payment.pay_at is '支付时间';
comment on column etc_dwd.traffic_payment.pay_year is '支付所属年份';
comment on column etc_dwd.traffic_payment.pay_month is '支付所属月份';
comment on column etc_dwd.traffic_payment.pay_day is '支付所属日期';

4.2.5 增加表名注释

comment on table etc_dwd.traffic_payment is '通行账单费用拆分表'

4.2.6 创建索引

SET behavior_compat_options = 'create_partition_local_index';

CREATE INDEX idx_local_vehicle_id ON etc_dwd.traffic_payment (vehicle_id);

4.2.7 清除表数据

truncate table etc_dwd.traffic_payment;

4.3 删除表

DROP TABLE etc_test.traffic_payment;

4.4 删除库

DROP schema etc_test;

4.5 华为云分区表样例（9.5版本开始）

CREATE TABLE etc_test.traffic (
-- 这里添加表的其他字段，以 id 为例
id INT,
month VARCHAR(7),
PRIMARY KEY (id) )
PARTITION BY RANGE (month) (
-- 定义分区，这里以 2025 年的每个月为例
PARTITION p_2025_01 VALUES LESS THAN ('2025-02'),
PARTITION p_2025_02 VALUES LESS THAN ('2025-03'),
PARTITION p_2025_03 VALUES LESS THAN ('2025-04'),
PARTITION p_2025_04 VALUES LESS THAN ('2025-05'),
PARTITION p_2025_05 VALUES LESS THAN ('2025-06'),
PARTITION p_2025_06 VALUES LESS THAN ('2025-07'),
PARTITION p_2025_07 VALUES LESS THAN ('2025-08'),
PARTITION p_2025_08 VALUES LESS THAN ('2025-09'),
PARTITION p_2025_09 VALUES LESS THAN ('2025-10'),
PARTITION p_2025_10 VALUES LESS THAN ('2025-11'),
PARTITION p_2025_11 VALUES LESS THAN ('2025-12'),
PARTITION p_2025_12 VALUES LESS THAN ('2026-01')
);

4.6 参考同事样例

CREATE TABLE IF NOT EXISTS bill_clean_detail (
    id bigint NOT NULL,
    vehicle_id bigint,
    member_detail_id bigint,
    receipt_amt bigint DEFAULT 0::bigint,
    car_no character varying (1020) DEFAULT '0'::character varying,
    province_short_name character varying (8),
    site_in character varying (1020) NOT NULL,
    site_in_at timestamp (0) without TIME zone,
    site_in_province character varying (1020),
    site_in_city character varying (1020),
    site_in_county character varying (1020),
    site_in_lng character varying (1020),
    site_in_lat character varying (1020),
    site_out character varying (1020) NOT NULL,
    site_out_at timestamp (0) without TIME zone,
    site_out_province character varying (1020),
    site_out_city character varying (1020),
    site_out_county character varying (1020),
    site_out_lng character varying (1020),
    site_out_lat character varying (1020),
    created_at character varying (40),
    month_date character varying (28),
    YEAR character varying (16)
)
WITH (
    orientation = COLUMN,
    enable_hstore_opt = ON,
    compression = middle,
    colversion = 3.0,
    enable_delta = FALSE,
    enable_hstore = TRUE,
    enable_turbo_store = TRUE
)
TABLESPACE cu_obs_tbs
-- 按 province_short_name 进行列表分区
PARTITION BY LIST (province_short_name) (
    PARTITION p_unknown VALUES ((''))
);
ALTER TABLE footprint_data_detail1 ADD PARTITION p_jingA VALUES (('京A'));

ALTER TABLE footprint_data_detail1 ADD PARTITION p_chuanJ VALUES (('川J'));
ALTER TABLE footprint_data_detail1 ADD PARTITION p_jinA VALUES (('晋A'));

--插入数据

insert into footprint_data_detail1
select * from footprint_data_detail
where created_at<'2024-10-01'
and created_at>='2024-09-01'

总之，PostgreSQL 9.2.4受版本限制，建表，尤其是主键和分区表有点难度