gaussdb DDL测试

创建表

create table zcbus_test1(employee_id int not null ,employee_name varchar(32) not null default ‘王小二’,home_addr varchar(64) default ‘ac:12*bc-02&1#4%5’,phone_number varchar(11) not null,age decimal(5,2) not null default 18,zhiwei varchar(32) default ‘普通员工’,create_date date,update_time timestamp with time zone default current_timestamp, primary key(employee_id,phone_number),unique (employee_id,employee_name));
create index ind_zcbus_test1 on zcbus_test1(home_addr);
create unique index uni_ind_zcbus_test1 on zcbus_test1 (age,zhiwei);
comment on table zcbus_test1 is ‘测试表1’;
comment on column zcbus_test1.age is ‘年龄’;

添加列

alter table zcbus_test1 add column fstab_0001 integer;
alter table zcbus_test1 add column fstab_0002 varchar(32) not null;
alter table zcbus_test1 add column fstab_0003 character varying(100) not null default ‘addr=192.168.1.2’;
alter table zcbus_test1 add column fstab_0004 decimal(38,20) not null default 10.89;
alter table zcbus_test1 add column fstab_0005 numeric(38,35) default 3.4;
alter table zcbus_test1 add column fstab_0006 varchar(64) null default ‘3/4的一半’;
alter table zcbus_test1 add column fstab_0007 date not null default ‘2025-01-02’;

alter table zcbus_test1 add column fstab_0007 date not null default current_timestamp; —- 不支持解析默认值为current_timestamp(可能是不支持函数为默认值)

修改列属性

alter table zcbus_test1 alter column fstab_0001 type bigint;
alter table zcbus_test1 alter column fstab_0002 type character varying(200);
alter table zcbus_test1 alter column fstab_0003 type varchar(210);
alter table zcbus_test1 alter column fstab_0004 type double precision;

添加、删除默认值

alter table zcbus_test1 alter column fstab_0001 set default 13;
alter table zcbus_test1 alter column fstab_0002 set default ‘addr=192.ac:12*bc-02&1#4%5’;

alter table zcbus_test1 alter column fstab_0001 drop default;

添加、删除约束、索引

alter table zcbus_test1 add constraint uk_zcbus_test1_0001 unique(fstab_0001);
alter table zcbus_test1 drop constraint uk_zcbus_test1_0001;

alter table zcbus_test1 alter column fstab_0005 set not null;
alter table zcbus_test1 alter column fstab_0005 drop not null;

create index ind_zcbus_test1_01 on zcbus_test1 (fstab_0001,fstab_0002,fstab_0003);
drop index ind_zcbus_test1_01; —-不支持解析

create unique index uni_ind_zcbus_test1_02 on zcbus_test1 (fstab_0001,fstab_0002,fstab_0003);
drop index uni_ind_zcbus_test1_02; —–不支持解析

添加、删除注释

comment on column zcbus_test1.fstab_0001 is ‘全国-kh/21’; —- 不支持解析
comment on table zcbus_test1 is ‘测试表test1’; —- 不支持解析

列名大小写

alter table zcbus_test1 add column “FSTAB_0008” varchar(64) not null default ‘1::bpchar’;
alter table zcbus_test1 drop column “FSTAB_0008”;

关键字

alter table zcbus_test1 add column “column” varchar(64) not null;
alter table zcbus_test1 alter column “column” type varchar(32);
alter table zcbus_test1 alter column “column” set default 13.3;

清空表

truncate table zcbus_test1; —不支持解析

重命名列

alter table zcbus_test1 add column “fstab 0009” timestamp not null
alter table zcbus_test1 alter column “fstab 0009” set default pg_systimestamp();
alter table zcbus_test1 rename “fstab 0009” to fstab_0010;

删除主键约束

alter table zcbus_test1 drop constraint zcbus_test1_pkey;

添加主键约束

alter table zcbus_test1 add primary key(employee_id, employee_name);

删除列

alter table zcbus_test1 drop column fstab_0001;
alter table zcbus_test1 drop column fstab_0002;
alter table zcbus_test1 drop column fstab_0003;
alter table zcbus_test1 drop column fstab_0004;
alter table zcbus_test1 drop column fstab_0005;
alter table zcbus_test1 drop column fstab_0006;
alter table zcbus_test1 drop column fstab_0007;
alter table zcbus_test1 drop column “column”;
alter table zcbus_test1 drop column fstab_0010;