二百八十五、华为云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.2.8 删除表数据 delete from etc_dwd.traffic_payment where bill_type=3; 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受版本限制，建表，尤其是主键和分区表有点难度