PostgreSQL 分区表
CREATE TABLE database.ad_hourly_report ( id bigserial NOT NULL, -- 自增ID settled_time timestamp NOT NULL, -- 日期时间 account_id int4 NOT NULL, -- 广告主账号 campaign_id int4 NOT NULL, -- 广告计划 adgroup_id int4 NOT NULL, -- 广告单元 ad_id int4 NOT NULL, -- 广告创意 view_count int4 NOT NULL DEFAULT 0, -- 曝光量 click_count int4 NOT NULL DEFAULT 0, -- 点击量 "cost" int8 NOT NULL DEFAULT 0, -- 花费 source_ad_id int8 NULL, -- 对应平台广告创意 source_adgroup_id int8 NULL, -- 对应平台广告单元 source_campaign_id int8 NULL, -- 对应平台广告计划 source_account_id int8 NULL, -- 对应平台广告主账号 insert_time timestamp DEFAULT CURRENT_TIMESTAMP ) PARTITION BY RANGE(settled_time); COMMENT ON TABLE database.ad_hourly_report IS '小时报告表'; --创建表分区 create table ad_hourly_report_202112 partition of ad_hourly_report for values from (minvalue) to ('2021-12-31'); create table ad_hourly_report_202201 partition of ad_hourly_report for values from ('2022-01-01') to ('2022-01-31'); create table ad_hourly_report_202202 partition of ad_hourly_report for values from ('2022-02-01') to ('2022-02-28'); --查询表分区 SELECT nmsp_parent.nspname AS parent_schema , parent.relname AS parent , nmsp_child.nspname AS child , child.relname AS child_schema FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_class child ON pg_inherits.inhrelid = child.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace JOIN pg_namespace nmsp_child ON nmsp_child.oid = child.relnamespace WHERE parent.relname = 'ad_hourly_report';