pg学习_基本表定义_修改表结构

修改表结构
1、增加字段
highgo=# \d test1
          Table "public.test1"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | character varying | 

highgo=# alter table test1 add addr varchar(20);
ALTER TABLE
highgo=# \d test1
            Table "public.test1"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 id     | integer               | 
 name   | character varying     | 
 addr   | character varying(20) | 
 
2、删除字段
highgo=# alter table test1 drop column addr;
ALTER TABLE
highgo=# \d test1
          Table "public.test1"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 id     | integer           | 
 name   | character varying | 

3、修改字段
最好的方法 
先建一个临时的表，然后删除该表，再创建新表，最后从临时表把记录导入进去。 
highgo=# create table test6 (num int,name varchar(10),sex varchar(1) check(sex in('y','n'))); 
CREATE TABLE
highgo=# insert into test6 values(1,'aaa','y'); 
INSERT 0 1
highgo=#  insert into test6 values(2,'bbb','n'); 
INSERT 0 1
highgo=# create table test7
highgo-# as
highgo-# select * from test6;
SELECT 2
highgo=# drop table test6;
DROP TABLE
highgo=# create table test6(
highgo(# num int,
highgo(# name varchar(16),
highgo(# sex varchar(1) check(sex in('y','n')));
CREATE TABLE
highgo=# insert into test6
highgo-# select * from test7;
INSERT 0 2
highgo=# select * from test6;
 num | name | sex 
-----+------+-----
   1 | aaa  | y
   2 | bbb  | n
(2 rows)