例子
向表中增加一个 varchar 列:
ALTER TABLE distributors ADD COLUMN address varchar(30);
从表中删除一个字段:
ALTER TABLE distributors DROP COLUMN address RESTRICT;
在一个操作中修改两个现有字段的类型:
ALTER TABLE distributors
ALTER COLUMN address TYPE varchar(80),
ALTER COLUMN name TYPE varchar(100);
使用一个 USING 子句,把一个包含 UNIX 时间戳的 integer 字段转化成 timestamp with time zone 字段:
ALTER TABLE foo
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';
同样地,当字段有一个不会自动转换成新类型的缺省值表达式时:
ALTER TABLE foo
ALTER COLUMN foo_timestamp DROP DEFAULT,
ALTER COLUMN foo_timestamp TYPE timestamp with time zone
USING
timestamp with time zone 'epoch' + foo_timestamp * interval '1 second',
ALTER COLUMN foo_timestamp SET DEFAULT now();
对现存字段改名:
ALTER TABLE distributors RENAME COLUMN address TO city;
更改现存表的名字:
ALTER TABLE distributors RENAME TO suppliers;
给一个字段增加一个非空约束:
ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;
从一个字段里删除一个非空约束:
ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;
给一个表增加一个检查约束:
ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);
删除一个表及其所有子表的监查约束:
ALTER TABLE distributors DROP CONSTRAINT zipchk;
向表中增加一个外键约束:
ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;
给表增加一个(多字段)唯一约束:
ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);
给一个表增加一个自动命名的主键约束,要注意的是一个表只能有一个主键:
ALTER TABLE distributors ADD PRIMARY KEY (dist_id);
把表移动到另外一个表空间:
ALTER TABLE distributors SET TABLESPACE fasttablespace;
把表移动到另外一个模式:
ALTER TABLE myschema.distributors SET SCHEMA yourschema;
兼容性
ADD, DROP, SET DEFAULT 形式与 SQL 标准兼容。其它形式是 PostgreSQL 对 SQL 标准的扩展。还有,在一个 ALTER TABLE 命令里声明多个操作也是扩展。
ALTER TABLE DROP COLUMN 可以用于删除表中的唯一的一个字段,留下一个零字段的表。这是对 SQL 的扩展,它不允许零字段表。
altertable test alter colunm_name type colunm_type;
1
2.将表alter_table 中var_date字段类型varchar 转换为timestamp
实验步骤:
1)建表
2)插入数据
3)使用修改表字段语句进行字段类型转换
实验结果如图
出现错误:error:cloumn “var_date” cannot be autmatically to type timestamp without time zone Hint:You might need to specify “using var_date::timestamp without time zone”
查阅资料:
大致意思是:转换类型的时候有隐含类型转换的时候,会自动转换,如果没有,那么就必须使用using显性指定一下转换规则。
那么我们使用如alter table 就成功转换:
altertable alter_table alter var_date typetimestampusing var_date::timestamp without time zone
1
结果如下:
3.失败二alter table
上述显式修改表字段,我以为这样,我的正式表就可以修改成功,结果又出现另一种问题,如图所示:
显式修改字段类型出现错误如下:
查了好多官方文档,也没有这类解释,后来经过高人指点,可能表中这个数据是null类型,转换不了,可以先将表中这个数据转换为timestamp类型,然后再用alter 语句转换数据类型。
实验如下:
1)先查表中字段这个数据,果然是"NULL"
2)修改表中数据为”timestamp“类型
3)显式修改表中该字段类型为”timestamp“类型
如图所示:
4. 把id的varchar变为int
postgres=# alter table tb101 alter idtypeint;
ERROR: column "id" cannot be cast automatically totype integer
HINT: Specify a USING expression to perform the conversion.
在没有隐式的转换下,就需要指定Using来显示的转换。
5. 使用Using进行类型转换
postgres=# altertable tb101 alter id type intusing id::int;
ALTERTABLE
postgres=# \d tb101
Table "public.tb101"
Column| Type | Modifiers
--------+---------+-----------
id |integer|
id::int 也可以使用cast(id as int)