手机音乐网站程序源码,南京触屏网站开发,定制网站开发成本估算表,产品效果图怎么做出来的一、目的
在PostgreSQL里建表#xff0c;设置主键#xff0c;三个字段确认数据的唯一性。设置分区字段#xff0c;按月分区
二、PostgreSQL版本 三、PostgreSQL 9.2.4 版本缺点
在 PostgreSQL 9.2.4 中#xff0c;虽然你可以创建分区表#xff0c;但需要注意的是#…一、目的
在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_at2024-10-01 and created_at2024-09-01 总之PostgreSQL 9.2.4受版本限制建表尤其是主键和分区表有点难度
