postgresql常见表级ddl操作

1.创建表

create table t1(id int,name varchar,s1 char,s2 serial,s3 bytea);

2.表上增加/删除字段

alter table t1 add column last_update_time timestamp without time zone;

alter table t1 drop column last_update_time;

增加非空字段,并且设置默认值

alter table tx add column ostime timestamp not null default 'now()';

还可以一条命令多次alter操作(不仅仅是增加删除字段),这样只需要扫描一次表,大大提升效率

alter table t1 drop column if exists last_update_time ,add column s5 bigint;

alter table t1 add column s6 int,drop column if exists s4;

对于删除字段,如果字段上有其它对象(例如其它表的外键)会导致alter table被拒绝,这时需要添加cascade选项

alter table t1 drop column s1 cascade;

3.修改字段类型

对于相同类型类的字段,可以直接进行修改,例如将int改成bigint或者decimal等,但是不能直接修改某些其它类型,例如date

alter table t2 alter column id type decimal(10,2); 

如果某些不同类型的字段,则需要进行强制转换,例如将varchar转换为int

alter table t2 alter column id type int  using id::int;

alter table t2 alter column id type date  using id::date;

using子句可以包含更复杂的表达式,用来将数据进行转换.例如可以截取字段等.下面的将字段进行转换:

alter table ty alter column name type varchar(20) using substr(name::varchar(5),1,2);

4.修改字段非空/默认值

设置默认值

alter table t2 alter column id set default 'now()';

删除默认值

alter table tx alter column ostime drop default;

设置非空

alter table tx alter column ostime set not null;

删除非空

alter table tx alter column ostime drop not null;

5.修改表名

alter table ty rename to tk;

6.将表移动到其它schema中

alter table tx set schema test;

7.将表移动到其它表空间中

grant all on tablespace new_tablespace to brent;--首先需要有表空间的权限

alter table tx set tablespace new_tablespace;

移动表后,索引不会自动移动,因此还需要手工移动索引

alter index idx_tx set tablespace new_tablespace;