postgresql 修改表结构,添加索引

张映 发表于 2013-05-31

分类目录: pgsql

标签:, , , , ,

玩了一下pgsql的修改表格的命令,感觉和mysql基本上差不多,唯有一些不同的是,alter 只能添加主键和外键,普通索引,唯一索引不能添加,还不能删除。要想删除,就要删除表,重建表。这个有点坑爹,我用的版本是8.1.13,非常低的版本了。不知道高版本有没有解决这个问题。

playboy=> alter table test add sex integer;    //添加一个表字段
ALTER TABLE
playboy=> \d test
                                   Table "public.test"
    Column    |         Type          |                     Modifiers
--------------+-----------------------+---------------------------------------------------
 id           | integer               | not null default nextval('seq_test_id'::regclass)
 name         | character varying(32) |
 date_created | date                  |
 sex          | integer               |
Indexes:
    "playboy_id_pk" PRIMARY KEY, btree (id)

playboy=> alter table test alter sex type varchar(1);  //修改表字段类型
ALTER TABLE
playboy=> \d test
                                   Table "public.test"
    Column    |         Type          |                     Modifiers
--------------+-----------------------+---------------------------------------------------
 id           | integer               | not null default nextval('seq_test_id'::regclass)
 name         | character varying(32) |
 date_created | date                  |
 sex          | character varying(1)  |
Indexes:
    "playboy_id_pk" PRIMARY KEY, btree (id)

playboy=> create unique index unique_name on test(name);  //创建唯一索引
CREATE INDEX
playboy=> \d test
                                   Table "public.test"
    Column    |         Type          |                     Modifiers
--------------+-----------------------+---------------------------------------------------
 id           | integer               | not null default nextval('seq_test_id'::regclass)
 name         | character varying(32) |
 date_created | date                  |
 sex          | character varying(1)  |
Indexes:
    "playboy_id_pk" PRIMARY KEY, btree (id)
    "unique_name" UNIQUE, btree (name)

playboy=> alter table test rename sex to age;     //表字段改名
ALTER TABLE
playboy=> \d test
                                   Table "public.test"
    Column    |         Type          |                     Modifiers
--------------+-----------------------+---------------------------------------------------
 id           | integer               | not null default nextval('seq_test_id'::regclass)
 name         | character varying(32) |
 date_created | date                  |
 age          | character varying(1)  |
Indexes:
    "playboy_id_pk" PRIMARY KEY, btree (id)
    "unique_name" UNIQUE, btree (name)

playboy=> alter table test drop age;    //删除表字段
ALTER TABLE


转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/pgsql/1528.html

留下评论

留下评论
  • (必需)
  • (必需) (will not be published)
  • (必需)   4X6=?