PostgreSQL分区表创建、分区查询等语法⽰例
查询分区表
SELECT
nmsp_parent.nspname AS parent_schema,
少数民族的节日nmsp_child.nspname AS child_schema,六一儿童节快乐的说说
凤凰传奇的新歌child.oid AS child_oid
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 = lnamespace
JOIN pg_namespace nmsp_child ON nmsp_child.oid = lnamespace
WHERE
ORDER BY
child_oid DESC
创建分区表及索引⽰例
CREATE TABLE "public"."vpn_build_inbound_log" (
"id" int8 DEFAULT nextval('vpn_build_inbound_log_id'::regclass) NOT NULL,
"it_code" varchar(64) COLLATE "default" DEFAULT''::character varying NOT NULL,
"inside_port" int4 DEFAULT0NOT NULL,
"manager" varchar(64) COLLATE "default" DEFAULT''::character varying NOT NULL,
"employee_id" varchar(16) COLLATE "default" DEFAULT''::character varying NOT NULL,
"vpn_ad_group" varchar(32) COLLATE "default" DEFAULT''::character varying NOT NULL,
"vpn_acl_name" varchar(32) COLLATE "default" DEFAULT''::character varying NOT NULL,
"clock" int8 DEFAULT0NOT NULL,
经典游戏网名"application" varchar(255) COLLATE "default" DEFAULT''::character varying,
"protocol" int4,
社保转移"inside_ip" inet,
CONSTRAINT "vpn_build_inbound_log_pkey" PRIMARY KEY ("id", "clock")
) INHERITS ("public"."vpn_build_inbound_log") WITH (OIDS=FALSE);
CREATE TABLE IF NOT EXISTS "public"."vpn_build_inbound_log_default" PARTITION OF "public"."vpn_build_inbound_log" DEFAULT; //创建default分区, 存储容错CREATE INDEX IF NOT EXISTS "vpn_build_inbound_log_clock_idx" ON "public"."vpn_build_inbound_log" USING btree (clock); //创建索引
CREATE INDEX IF NOT EXISTS "vpn_build_inbound_log_it_code_idx" ON "public"."vpn_build_inbound_log" USING btree (it_code); //创建索引
CREATE UNIQUE INDEX "vpn_build_inbound_log_pkey" ON "public"."vpn_build_inbound_log" USING btree (id, clock); //创建联合索引
三年级作文秋天的景根据时间段创建新分区
CREATE TABLE IF NOT EXISTS {partition_name} PARTITION OF {tb_name} FOR VALUES FROM ({start_timestamp}) TO ({end_timestamp});
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论