PostgreSQL：创建自增序列id，分区表，分区表子表

1）创建自增序列seq

CREATE SEQUENCE if not exists public.test_id_seq
    INCREMENT 1
    START 5
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

2）创建分区表主表

-- 创建表 根据ds分表（ds为yyyyMMdd格式）
create table test
(
  id bigint NOT NULL DEFAULT nextval('test_id_seq'::regclass),
  ip text,
  name text,
  create_time timestamp with time zone NOT NULL,
  ds bigint
) PARTITION BY LIST (ds);
-- 创建注释
COMMENT ON TABLE test IS '分区表测试';
COMMENT ON COLUMN test.ip IS '访问ip';
COMMENT ON COLUMN test.name IS '名称';
COMMENT ON COLUMN test.create_time IS '创建时间';

3）创建分区表子表

-- 创建分区子表
-- create table %s_%s partition of %s for values in (%s)
create table test_20200924 partition of test for values in(20200924);

4）分区表数据插入

insert into test(ip,name,ds) values('1.1.1.1','good',20200924);
insert into test(ip,name,ds) values('1.1.1.1','good2',20200924);
insert into test(ip,name,ds) values('1.1.1.1','good3',20200924);

5）分区表查询

select name,to_char(create_time,'yyyymmddhh24mi') as period from test where ds in (20200921,20200922,20200923,20200924) and create_time <='2020-09-24 15:30:00.000' and create_time >= '2020-09-21 00:00:00.000' group by name,to_char(create_time,'yyyymmddhh24mi') order by name,period