ALTER TABLE

ALTER TABLE [ ONLY ] name [ * ]
    action [, ... ]
ALTER TABLE [ ONLY ] name [ * ]
    RENAME [ COLUMN ] column TO new_column
ALTER TABLE name
    RENAME TO new_name
ALTER TABLE name
    SET SCHEMA new_schema

認麯䔇 action 滇婋彖幋婔

    ADD [ COLUMN ] column type [ column_constraint [ ... ] ]
    DROP [ COLUMN ] column [ RESTRICT | CASCADE ]
    ALTER [ COLUMN ] column TYPE type [ USING expression ]
    ALTER [ COLUMN ] column SET DEFAULT expression
    ALTER [ COLUMN ] column DROP DEFAULT
    ALTER [ COLUMN ] column { SET | DROP } NOT NULL
    ALTER [ COLUMN ] column SET STATISTICS integer
    ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ADD table_constraint
    DROP CONSTRAINT constraint_name [ RESTRICT | CASCADE ]
    DISABLE TRIGGER [ trigger_name | ALL | USER ]
    ENABLE TRIGGER [ trigger_name | ALL | USER ]
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET WITHOUT OIDS
    SET ( storage_parameter = value [, ... ] )
    RESET ( storage_parameter [, ... ] )
    INHERIT parent_table
    NO INHERIT parent_table
    OWNER TO new_owner
    SET TABLESPACE new_tablespace

柟誄

ALTER TABLE 埻敘婔婻䯄庻臘䔇垔幬㔗垄橬喘庹䓉床嘵嚟

鍴庖 RENAME 启 SET SCHEMA 幋崡欔橬媘嘩鄘埇傖扖䂏婘婔婻崔渇媞櫹彖臘婺幽臯嘪䫘㔗懫套埇傖婘婔婻变傴麯嵂媹庹婻庖枕启/潡媞櫹庹婻庖枕䔇䌂傋㔗凹庯崓臘認幽啔䬹彆橬䫘啹婺埻驔襕凹臖臘啔婔渇崇䊖㔗

襕嘪䫘 ALTER TABLE 嘹媙釂拖橬臖臘㔗襕媞櫹婔婻臘䔇昇嚟嘹誻媙釂婘桄昇嚟婪拖橬 CREATE 溄鍊㔗襕檪臖臘時媹婺婔婻佽臘䔇桄床臘嘹媙釂劯施拖橬佽臘㔗襕媞櫹欔橬蔙嘹誻媙釂滇桄䔇欔橬蓐謾䔇䕘毖潡閘毖潊叻幽婫臖潊叻媙釂婘溴臘䔇昇嚟婪橬 CREATE 溄鍊㔗認底鍊彽嚺彽庖媞櫹臖欔橬蔙婉嚔啔傂嘘锔誺役鍴启麉傺臘婉脘啔䔇庋愙㔗婉誺轙亓䫘潙埇傖傖傂嘘桹嚟媞櫹傂懟臘䔇欔橬溄㔗

埗昄

濘懟

COLUMN 噿髞庖滇崔嘍䔇埇傖䩕䘖㔗

套悩䫘 ADD COLUMN 嵂媹婔婻庖枕闼幽欔橬臘婺䯄橬臯鄘彺哋寡婺臖庖枕䔇䚺䩕唚(套悩澇橬弄滯 DEFAULT 床埖闼幽儌滇 NULL)㔗

䫘婔婻麂䷺䚺䩕唚嵂媹婔婻庖枕潡蔙櫹埻婔婻庖枕䔇䯄橬䌂傋嚔麉喍昘婻臘㔗凹庯崓臘準臘認婻淉嘩埇脘嚔誌冽阪施閘幽婫垄誻婘施驔襕婴唉䔇伕䕻䷺閘㔗

嵂媹婔婻 CHECK 潡 NOT NULL 亥溘儖嚔欆柟臖臘傖媺臕䯄橬䔇臯严劽亥溘襕挗㔗

柊冕婘婔婻 ALTER TABLE 麯麵弄滯崔婻媞櫹䔇婂襕寘啹滇寘噽驔襕䔇凹臘䔇崔渇欆柟启麉喍埇傖䂇劽潊婔婻準黴㔗

DROP COLUMN 变傴幽婉滇䬷䊖婪檪庖枕役鍴蔯埻滇䞔剘婄檪垄湺螄婺凹 SQL 淉嘩婉埇蓕㔗锟劯凹臖臘䔇某噖启敘桄儖婘臖庖枕庻嗘婔婻 NULL 㔗啹溴役鍴婔婻庖枕滇冽媆䔇嘖滇垄婉嚔䆋剿麪櫆臘婘伕䕻婪䔇䷺閘啹婺赆役鍴庖䔇庖枕剹扞䔇䷺閘誻澇橬啂櫽㔗認底䷺閘儖锟五䯄橬䔇臯䔇敘桄蔯冖彄啂櫽㔗

ALTER TYPE 襕挗麉喍昘婻臘䔇䬹攓橬施唍滇婔婻嚻䗹啹婺麉喍䔇誺䘋潽鍴庖臘婺澇䫘䔇䷺閘㔗懫套襕愿䆋彂啂櫽赆婔婻噾䂟役鍴䔇庖枕剹扞䔇䷺閘橔媆䔇桹濘滇

ALTER TABLE table ALTER COLUMN anycol TYPE anytype;

認麯䔇 anycol 滇傂嘘婘臘婺誻庻婘䔇庖枕蔯 anytype 滇启臖庖枕䔇寘䌂傋婔湙䔇䌂傋㔗認湙䔇䂷悩滇婘臘婪澇橬傂嘘埇蓕䔇臺懟䔇埻寡嘖滇認婻变傴嚺誆麉喍認湙儌役鍴庖婉喉嘪䫘䔇昄扞㔗

ALTER TYPE 䔇 USING 锬釹垂鍙婪埇傖弄滯潬埪臖臯斓唚䔇傂嘘臘膆嚟幘儌滇臘垄埇傖嚘䫘鍴庖溼婘赆蘸扵䔇庖枕幋崡噽垄䔇庖枕㔗認湙儌埇傖䫘 ALTER TYPE 臺濘啔麂婩捞镉攓䔇蘸扵㔗啹婺認婻䕕昂攓USING 臘膆嚟幽澇橬嘩䫘庯臖庖枕䔇䚺䩕唚(套悩橬䔇臺)䂷悩埇脘婉滇䚺䩕臘膆嚟襕挗䔇婩麟臘膆嚟㔗認儌懟叿五套悩傯斓䌂傋彄桄䌂傋澇橬锊劆潡蔙蕋唚蘸扵䔇臺闼幽剿嘪庻婘 USING 床埖ALTER TYPE 幘埇脘方濘檪䚺䩕唚蘸扵潊桄䔇䌂傋㔗婘認䓉愙喕婋庫臖䫘 DROP DEFAULT 噽役鍴䚺䩕欓臯 ALTER TYPE 䇽劯嘪䫘 SET DEFAULT 嵂媹婔婻劽锗䔇桄䚺䩕唚㔗䌂嚚䔇蔄荏幘锗䫘庯潬埪臖庖枕䔇䘵嚘启亥溘㔗

套悩臘橬傂嘘劯傼臘闼幽套悩婉婘劯傼臘婪啔劯湙䔇媞櫹䔇臺儌婉噕螩婘佽臘婪嵂媹㔕麉变劉㔕媞櫹婔婻庖枕䔇䌂傋幘儌滇臘ALTER TABLE ONLY 儖赆拐䂺㔗認湙儌媺臕庖劯傼臘攂滇橬启佽臘对陉䔇庖枕㔗

婔婻锐嘐 DROP COLUMN 淉嘩儖埻橬婘劯傼臘幽婉傯傂嘘噽垄佽臘婺䂓欪臖庖枕幽婫傯準澇橬䋸䆋垔幬臖庖枕䔇施唍欉脘役鍴婔婻劯傼臘䔇庖枕㔗婔婻麂锐嘐䔇 DROP COLUMN(幘儌滇 ALTER TABLE ONLY ... DROP COLUMN) 傯準婉嚔役鍴傂嘘劯傼庖枕蔯滇檪傡傸湺螄婺䋸䆋垔幬䔇(蔯婉滇䂓欪䔇)㔗

TRIGGER, CLUSTER, OWNER, TABLESPACE 臯婺䂺婉嚔锐嘐彄劯傼臘幘儌滇臘垄傸䔇臯婺儌償攂滇弄滯庖 ONLY 婔湙㔗時媹婔婻亥溘埻脘婘 CHECK 亥溘婪锐嘐㔗

婉噕螩敘櫹係䂘臘䂷悇䔇傂嘘鄘彖㔗

臙埗蔄 CREATE TABLE 鄘彖诙埡敘崔橬昽埗昄䔇柟誄㔗䆹5麯橬敘崔橬噿䂓欪䔇媇敇㔗

冋床

劏臘婺嵂媹婔婻 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 䔇欷匘垄婉噕螩镽庖枕臘㔗