postgresql 修改表字段_postgresql 修改表结构，添加索引

postgresql 修改表结构，添加索引

//添加一个表字段

testuser=> alter table test add sex integer;

ALTER TABLE

testuser=> \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:

"testuser_id_pk" PRIMARY KEY, btree (id)

//修改表字段类型

testuser=> alter table test alter sex type varchar(1);

ALTER TABLE

testuser=> \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:

"testuser_id_pk" PRIMARY KEY, btree (id)

//创建唯一索引

testuser=> create unique index unique_name on test(name);

CREATE INDEX

testuser=> \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:

"testuser_id_pk" PRIMARY KEY, btree (id)

"unique_name" UNIQUE, btree (name)

//表字段改名

testuser=> alter table test rename sex to age;

ALTER TABLE

testuser=> \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:

"testuser_id_pk" PRIMARY KEY, btree (id)

"unique_name" UNIQUE, btree (name)

//删除表字段

testuser=> alter table test drop age;

ALTER TABLE