DDL测试

创建表

create table pg_tab1 (test_1 smallint primary key,test_2 integer not null,test_3 bigint unique,test_4 decimal(38,35),test_5 numeric(38,35),test_6 real check (test_6 > 0),test_7 double precision,test_8 smallserial,test_9 serial,test_10 bigserial,test_11 money,unique (test_4,test_5),constraint check_test_5 check (test_5 > 10));
create table pg_tab2 (test_1 smallint,test_2 character varying(10) constraint uk_t2_test2 unique,test_3 varchar(2000),test_4 character(255),test_5 char(255),test_6 text,primary key(test_1));
create table pg_tab3 (test_1 smallint primary key,test_2 timestamp,test_3 timestamp with time zone,test_4 date,test_5 time,test_6 time with time zone,test_7 interval);
create table PG_TAB4 (TEST_1 smallint primary key,TEST_2 bit(15),TEST_3 bit varying(20),TEST_4 boolean,TEST_6 bigint,foreign key (TEST_6) references pg_tab1 (test_3));
create table pg_tab5 (test_1 smallint primary key,test_2 point,test_3 line,test_4 lseg,test_5 box,test_6 path,test_7 polygon,test_8 circle,test_9 cidr,test_10 inet,test_11 macaddr,test_12 tsvector,test_13 tsquery,test_14 uuid);
create table pg_tab6 (test_1 smallint primary key);
create table PG_TAB7 (test_1 smallint);
create table “create” (“table” smallint primary key);

添加列

alter table pg_tab6 add column test_2 integer;
alter table pg_tab6 add column test_3 character(255);
alter table pg_tab6 add column test_4 bigint null;
alter table pg_tab6 add column test_5 character varying(100) not null;
alter table pg_tab6 add column test_6 decimal(38,20) not null default 10;
alter table pg_tab6 add column test_7 numeric(38,35) default 3.4;

alter table pg_tab6 drop column test_2;
alter table pg_tab6 drop column test_3;
alter table pg_tab6 drop column test_4;
alter table pg_tab6 drop column test_5;
alter table pg_tab6 drop column test_6;
alter table pg_tab6 drop column test_7;

修改列属性

alter table pg_tab6 alter column test_2 type real;
alter table pg_tab6 alter column test_3 type character varying(200);
alter table pg_tab6 alter column test_4 type double precision;
alter table pg_tab6 alter column test_5 type varchar(100);

添加、删除默认值

alter table pg_tab6 alter column test_7 set default 4.8;
alter table pg_tab6 alter column test_7 drop default;
alter table pg_tab6 add default 8.1 for test_7; —antdb不支持

删除、重命名列

alter table pg_tab6 add column test_9 integer;
alter table pg_tab6 rename column test_9 to test_10;
alter table pg_tab6 drop column test_10;

添加、删除约束、索引

alter table pg_tab6 add constraint uk_t6_test_2 unique(test_2);
alter table pg_tab6 drop constraint uk_t6_test_2;

alter table pg_tab6 alter column test_5 set not null;
alter table pg_tab6 alter column test_5 drop not null;

create index index_t6_test2 on pg_tab6(test_2);
drop index index_t6_test2;

create index index_t6_test23 on pg_tab6(test_2,test_3);
drop index index_t6_test23;

create unique index unindex_t6_test2 on pg_tab6(test_2);
drop index unindex_t6_test2;

create unique index unidex_t6_test45 on pg_tab6(test_4,test_5);

大小写

ALTER TABLE PG_TAB7 ADD COLUMN TEST_2 DECIMAL(38,25) DEFAULT 10 NOT NULL;
ALTER TABLE PG_TAB7 ADD COLUMN Test_3 varchar(50);
ALTER TABLE PG_TAB7 ADD COLUMN TEST_4 INTEGER;
ALTER TABLE PG_TAB7 ADD COLUMN TEST_5 CHARACTER VARYING(50);

alter table PG_TAB7 alter column TEST_2 drop default;
ALTER TABLE PG_TAB7 ALTER COLUMN Test_3 set default ‘aaa’;

alter table PG_TAB7 DROP COLUMN TEST_4;

ALTER TABLE PG_TAB7 ADD CONSTRAINT INDEX_T8_TEST2 ON unique(TEST_2); —antdb不支持
alter table pg_tab7 drop constraint INDEX_T8_TEST2;

重命名表

alter table pg_tab7 rename to PG_TAB8;

分区表

一、RANG分区表
1、创建range分区表
CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
primary key (logdate,peaktemp)
) PARTITION BY RANGE (logdate);

2、创建分区(可加tablespace指定表空间)
CREATE TABLE measurement_y2021m11 PARTITION OF measurement
FOR VALUES FROM (‘2021-10-01’) TO (‘2021-12-01’);

CREATE TABLE measurement_y2021m12 PARTITION OF measurement
FOR VALUES FROM (‘2021-12-01’) TO (‘2022-01-01’);

CREATE TABLE measurement_y2022m01 PARTITION OF measurement
FOR VALUES FROM (‘2022-01-01’) TO (‘2022-02-01’);

CREATE TABLE measurement_y2022m02 PARTITION OF measurement
FOR VALUES FROM (‘2022-02-01’) TO (‘2022-03-01’);

CREATE TABLE measurement_y2022m03 PARTITION OF measurement
FOR VALUES FROM (‘2022-03-01’) TO (‘2022-04-01’)
WITH (parallel_workers = 4);

3、创建分区指定子分区
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM (‘2006-02-01’) TO (‘2006-03-01’)
PARTITION BY RANGE (peaktemp);

4、在分区键上创建索引
CREATE INDEX ON measurement (logdate);

5、删除分区
DROP TABLE measurement_y2022m11;
ALTER TABLE measurement DETACH PARTITION measurement_y2022m02; –把分区从分区表中移除，但是保留它作为一个独立的表

6、加入分区
ALTER TABLE measurement ATTACH PARTITION measurement_y2022m02 FOR VALUES FROM (‘2022-02-01’) TO (‘2022-03-01’);

二、LIST分区表（基本与上同）

CREATE TABLE cities (
city_id bigint not null,
name text not null,
population bigint
) PARTITION BY LIST (name);

CREATE TABLE cities_1 PARTITION OF cities FOR VALUES IN (‘A’);
CREATE TABLE cities_2 PARTITION OF cities FOR VALUES IN (‘B’);
CREATE TABLE cities_3 PARTITION OF cities FOR VALUES IN (‘C’);
CREATE TABLE cities_4 PARTITION OF cities FOR VALUES IN (‘D’);

—–分区表

1.通过指定 PARTITION BY 子句将measurement表创建为分区表，其中包括分区方法（在本例中为 RANGE）和用分区键。

CREATE TABLE measurement (
city_id int not null,
logdate date not null,
peaktemp int,
unitsales int
) PARTITION BY RANGE (logdate);
2.创建分区。每个分区的定义必须指定与父分区方法和分区键相对应的边界。请注意，指定边界以使新分区的值与一个或多个现有分区中的值重叠会导致错误。

这样创建的分区在各个方面都是普通的 PostgreSQL 表（或者，可能是外部表）。可以分别为每个分区指定表空间和存储参数。

对于我们的示例，每个分区应保存一个月的数据，以匹配一次删除一个月数据的要求。所以命令可能看起来像：

create table measurement_2022_1 partition of measurement
for values from (‘2022-01-01’) to (‘2022-02-01’);

create table measurement_2022_2 partition of measurement
for values from (‘2022-02-01’) to (‘2022-03-01’);

create table measurement_2022_3 partition of measurement
for values from (‘2022-03-01’) to (‘2022-04-01’);
如果您希望实现子分区，请在用于创建单个分区的命令中再次指定 PARTITION BY 子句，例如：

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
FOR VALUES FROM (‘2006-02-01’) TO (‘2006-03-01’)
PARTITION BY RANGE (peaktemp);

在创建measurement_y2006m02的分区后，任何插入到measurement_y2006m02的数据（或直接插入到measurement_y2006m02的数据，只要满足其分区约束就可以）将进一步重定向到基于peaktemp列的分区之一.指定的分区键可能与父分区键重叠，但在指定子分区的边界时应小心，使其接受的数据集构成分区自身边界允许的数据集；系统不会尝试检查是否确实如此。

将数据插入到未映射到现有分区之一的父表中会导致错误；必须手动添加适当的分区。

不必手动创建描述分区的分区边界条件的表约束。此类约束将自动创建。

3.在分区表上的键列以及您可能需要的任何其他索引上创建索引。（键索引不是绝对必要的，但在大多数情况下它是有帮助的。）这会自动在每个分区上创建一个匹配的索引，并且您以后创建或附加的任何分区也将具有这样的索引。在分区表上声明的索引或唯一约束是“虚拟的”，就像分区表一样：实际数据位于各个分区表的子索引中。

CREATE INDEX ON measurement (logdate);
4.确保在 postgresql.conf 中未禁用 enable_partition_pruning 配置参数。如果是，查询将不会按需要进行优化。

测试：

insert into measurement(city_id,logdate,peaktemp,unitsales) values(1,’2022-01-01’,1,1);
insert into measurement(city_id,logdate,peaktemp,unitsales) values(2,’2022-02-01’,2,2);
insert into measurement(city_id,logdate,peaktemp,unitsales) values(3,’2022-03-01’,3,3);

创建存储过程
create or replace procedure shuang_test()
as $body$
declare
pg1 integer;
begin
for pg1 in 1 .. 100 loop
insert into poc_test1 values (pg1,’aaaaaa’,CURRENT_TIMESTAMP);
end loop;
end;
$body$
language plpgsql;

调用存储过程
call shuang_test()

create table pgcol_test (id int primary key,first_name varchar(20),last_name varchar(20),full_name varchar(40) generated always as (first_name || ‘ ‘ || last_name) stored,math int,english int,chinese int,total_num decimal(10,2) generated always as (math + english + chinese) stored,avg_num decimal(10,2) generated always as ((math + english + chinese) / 3) stored,create_date date,update_date date,duration int generated always as (update_date - create_date) stored);

create or replace procedure proc_pgcol_test( in cnt integer)
as $body$
Declare
pg1 integer;
startint integer;
begin
select COALESCE(max(id),0) into startint from public.pgcol_test;
RAISE NOTICE ‘load total lines at ：%,real lines : % ‘,startint,cnt;
for pg1 in 1..cnt loop
insert into public.pgcol_test (id,first_name,last_name,math,english,chinese,create_date,update_date) values (pg1+startint,random_string(10),random_string(10),round(random()100),round(random()100),round(random()*100),current_date-pg1,current_date);
end loop;
end;
$body$
language plpgsql;