PostgreSQL

[語法]PostgreSQL ALTER TABLE

[複製鏈接]
genie genie 2015-6-8 15:39:50 4221 阅读
琪琪小站免責聲明
本論壇仍是開放式討論平台,本論壇對於所有討論的真實性、完整性及立場等,不負任何法律責任。
而一切留言之言論只代表留言者個人意見,並非本網站之立場,用戶不應信賴內容,並應自行判斷內容之真實性。
於有關情形下,用戶應尋求專業意見 (如涉及醫療、法律或投資等問題)。
由於本論壇受到「即時上載留言」運作方式所規限,故不能完全監察所有留言,若讀者發現有留言出現問題,請聯絡我們。
kikishop有權刪除任何留言及拒絕任何人士上載留言,同時亦有不刪除留言的權利。
切勿撰寫粗言穢語、誹謗、渲染色情暴力或人身攻擊的言論,敬請自律。
本網站保留一切法律權利。

To add a column of type varchar to a table:

ALTER TABLE distributors ADD COLUMN address varchar( 30);

To drop a column from a table:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

To change the types of two existing columns in one operation:

ALTER TABLE distributors ALTER COLUMN address TYPE varchar( 80 ), ALTER COLUMN name TYPEvarchar( 100);

To change an integer column containing UNIX timestamps to timestamp with time zone via a USINGclause:

ALTER TABLE foo ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone USINGtimestamp with time zone ' epoch ' + foo_timestamp * interval ' 1 second ';

The same, when the column has a default exssion that won't automatically cast to the new data type:

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();

To rename an existing column:

ALTER TABLE distributors RENAME COLUMN address TO city;

To rename an existing table:

ALTER TABLE distributors RENAME TO suppliers;

To add a not-null constraint to a column:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

To remove a not-null constraint from a column:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

To add a check constraint to a table and all its children:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

To remove a check constraint from a table and all its children:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

To remove a check constraint from a table only:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(The check constraint remains in place for any child tables.)

To add a foreign key constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) MATCH FULL;

To add a (multicolumn) unique constraint to a table:

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

To add an automatically named primary key constraint to a table, noting that a table can only ever have one primary key:

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

To move a table to a different tablespace:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

To move a table to a different schema:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

To recreate a primary key constraint, without blocking updates while the index is rebuilt:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); ALTER TABLEdistributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARYKEY USING INDEX dist_id_temp_idx;

http://www.postgresql.org/docs/current/static/sql-altertable.html


琪琪小站免責聲明
本論壇仍是開放式討論平台,本論壇對於所有討論的真實性、完整性及立場等,不負任何法律責任。
而一切留言之言論只代表留言者個人意見,並非本網站之立場,用戶不應信賴內容,並應自行判斷內容之真實性。
於有關情形下,用戶應尋求專業意見 (如涉及醫療、法律或投資等問題)。
由於本論壇受到「即時上載留言」運作方式所規限,故不能完全監察所有留言,若讀者發現有留言出現問題,請聯絡我們。
kikishop有權刪除任何留言及拒絕任何人士上載留言,同時亦有不刪除留言的權利。
切勿撰寫粗言穢語、誹謗、渲染色情暴力或人身攻擊的言論,敬請自律。
本網站保留一切法律權利。
FengLeo Internet Multimedia Services WorkStation
*********************************************************
Genie
WebUrl:https://www.fengleo.com/

使用道具 舉報

Archiver|手機版|小黑屋|Ki Ki BBS

Powered by Discuz! X5.0 © 2001-2026 Discuz! Team.

在本版發帖返回頂部