PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔媆誕嬉誕

CREATE TABLE

劉䓄

CREATE TABLE -- 垔幬婔婻桄臘

臺濘

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
  { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] ... }
    [, ... ]
] )
[ INHERITS ( parent_table [, ... ] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace ]

認麯䔇 column_constraint 滇

[ CONSTRAINT constraint_name ]
{ NOT NULL | 
  NULL | 
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  CHECK ( expression ) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

蔯 table_constraint 滇

[ CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  CHECK ( expression ) |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

婘 UNIQUE 启 PRIMARY KEY 亥溘婺䔇 index_parameters 滇

[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ USING INDEX TABLESPACE tablespace ]

柟誄

CREATE TABLE 婘嘷嬉昄扞康录傺婔婻桄䔇䷺䍘臘㔗臖臘儖䫌埏庺溴变傴䔇䫘潙欔拖橬㔗

套悩䂍庺庖昇嚟劉(懫套 CREATE TABLE myschema.mytable ...)闼幽婘毺垔䔇昇嚟婺录傺臘劥彍婘嘷嬉昇嚟婺录傺㔗婘施臘庻婘庯婔婻䬹枪䔇昇嚟麯啹溴录傺婘施臘䔇施唍婉脘毺垔昇嚟劉㔗臘劉庖媙驔婘劯婔昇嚟婺䔇噽垄臘㔕废彖㔕䘵嚘㔕蓖商劉庖婺嫇婔㔗

CREATE TABLE 誻躻媘录傺婔婻婯臖臘䔇臯凹庫䔇崉劽昄扞䌂傋㔗啹溴臘婉脘启劯昇嚟婺䔇䯄橬昄扞䌂傋劯劉㔗

埇锬䔇亥溘床埖弄滯亥溘桄臯潡蔙敘桄䔇臯媙釂悇轿認底亥溘欉脘潊媘某噖潡敘桄㔗亥溘滇婔婻 SQL 凹茇垄傖崔䓉桹嚟剟媷婘臘婪垔幬橬昽昄唚䔇镖劽㔗

垔幬亥溘橬婴䓉桹濘臘亥溘启彖亥溘㔗彖亥溘滇嘩婺婔婻彖垔幬䔇婔鄘彖垔幬䔇㔗蔯臘亥溘幽婉启昊婻彖䂏婘婔蕙垄埇傖嘩䫘庯崔婻彖婪㔗懟婻彖亥溘幘埇傖喍潊臘亥溘套悩昊婻亥溘埻嘌巉婔婻彖闼幽彖亥溘埻滇严埙婪䔇䞔昕桹嚟蔯噾㔗

埗昄

TEMPORARY 潡 TEMP

录傺婺婘施臘㔗婘施臘婘嚔臺䂷溘潡(埇锬)嘷嬉庋媇䔇䂷儆(埗黙婋麵䔇 ON COMMIT)躻媘役鍴㔗鍴麂䫘昇嚟媞閄䔇劉庖嚘䫘劥彍䯄橬䔇劯劉愩幙臘婘婘施臘庻婘橘閘婘橸嚔臺誺䘋婺滇婉埇蓕䔇㔗傂嘘婘婘施臘婪录傺䔇䘵嚘幘鄘嚔赆躻媘役鍴㔗

埇傖锬拷婘 TEMPORARY 潡 TEMP 嬉麵櫆婪 GLOBAL 潡 LOCAL 㔗婉誺認凹 PostgreSQL 準臘澇橬傂嘘寺彆埇傖埗黙噚垹攓

㔗
table_name

襕录傺䔇臘䔇劉庖(埇傖䫘昇嚟媞閄)

column_name

婘桄臘婺襕录傺䔇庖枕劉庖

data_type

臖庖枕䔇昄扞䌂傋㔗垄埇傖寙拸昄䂇臘滯严㔗橬噿 PostgreSQL 櫇毕䔇昄扞䌂傋䔇敘崔媇敇臙埗蔄䆹8㔗

DEFAULT default_expr

DEFAULT 床埖䂍庖枕毺垔䚺䩕唚㔗臖昄唚埇傖滇傂嘘婉劆埻麟䔇臘膆嚟(婉噕螩嘪䫘床昖臵启凹橸臘婺䔇噽垄庖枕䔇庴埬嚘䫘)㔗䚺䩕臘膆嚟䔇昄扞䌂傋媙釂启庖枕䌂傋对陉㔗

䚺䩕臘膆嚟儖赆䫘庯傂嘘橻弄滯臖庖枕昄唚䔇某噖淉嘩㔗套悩澇橬毺垔䚺䩕唚彍䚺䩕唚婺 NULL 㔗

INHERITS ( parent_table [, ... ] )

埇锬䔇 INHERITS 床埖弄滯婔係彖䔇臘認婻桄臘躻媘傯認婔係彖臘婺䂓欪欔橬庖枕㔗

嘪䫘 INHERITS 儖婘床臘启噽佽臘幋閘录傺婔婻愩幙䔇噿係凹佽臘䂷悇䔇媞櫹锔婩幘嚔嚹携彄床臘㔗䚺䩕施欆柟佽臘䔇施唍幘嚔欆柟床臘㔗

套悩婘崔婻佽臘婺庻婘劯劉庖枕闼幽儌嚔檖只婔婻髍臇鍴麂認底庖枕䔇昄扞䌂傋婘懟婻佽臘麯鄘滇对陉䔇㔗套悩澇橬喾仕闼幽麉崉䔇庖枕婘桄臘婺肉劽潊婔婻庖枕㔗套悩彖庺䔇桄臘庖枕劉启䂓欪庖枕劯劉闼幽垄䔇昄扞䌂傋幘媙釂启䂓欪庖枕对陉幽婫認底庖枕垔幬嚔肉劽潊婔婻㔗婉誺桄庖枕埇傖弄滯婉劯庯劯劉䂓欪庖枕䔇亥溘欔橬䂓欪誺準䔇亥溘傖埪桄弄滯䔇亥溘鄘肉劽彄婔蕙幽婫劯施嘩䫘庯桄臘㔗套悩桄臘婺臖庖枕滯䇞弄滯庖䚺䩕唚闼幽溴䚺䩕唚儖襖䕡傂嘘䂓欪庖枕䔇䚺䩕唚㔗劥彍臖庖枕䔇欔橬佽庖枕䚺䩕唚鄘媙釂䕩劯劥彍儌嚔檖髍㔗

LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ]

LIKE 床埖弄滯婔婻臘桄臘躻媘傯認婻臘麯麵䂓欪欔橬庖枕劉埪噽昄扞䌂傋启麂䷺亥溘㔗

启 INHERITS 婉劯桄臘婯寘準䔇臘幋閘婘录傺媘嘩垯懘幋劯滇垯噘方噿䔇㔗婘溊臘啔䔇傂嘘媞櫹鄘婉嚔嚹携彄桄臘婺幽婫幘婉埇脘婘欆柟溊臘䔇施唍寙劆桄臘䔇昄扞㔗

庖枕䚺䩕臘膆嚟埻橬婘弄滯庖 INCLUDING DEFAULTS 幋劯欉嚔寙劆誕準㔗䚺䩕滇婉寙劆䚺䩕臘膆嚟䔇䂷悩滇桄臘婺欔橬庖枕䔇䚺䩕唚鄘滇 NULL 㔗

麂䷺亥溘儖攂滇崉彽彄桄臘婺CHECK 亥溘彍備婘毺垔庖 INCLUDING CONSTRAINTS 䔇施唍欉崉彽蔯噽傡䌂傋䔇亥溘彍愩誩幘婉嚔赆崉彽㔗溴蓇彍劯施锗䫘庯臘亥溘启彖亥溘㔗

启 INHERITS 婉劯赆崉彽䔇彖启亥溘幽婉嘪䫘䕩劯䔇劉庖誕臯肉劽㔗套悩滯䇞䔇毺垔庖䕩劯䔇劉庖潡蔙婘埥崡婔婻 LIKE 床埖婺闼幽儖嚔檖髍㔗

CONSTRAINT constraint_name

埇锬䔇彖亥溘潡臘亥溘䔇劉庖㔗套悩亥溘橸躆滇麂濘䔇闼幽噽劉庖儖嚔庺䯄婘髍臇媇敇婺啹溴償 col must be positive 認湙䔇劉庖埇傖臘膆橬䫘䔇亥溘媇敇㔗套悩亥溘劉婺劆橬䷺湚彍媙釂䫘埯嚘埙䘯垔㔗套悩澇橬毺垔亥溘劉闼幽係䂘儖嚔躻媘䫘潊婔婻劉庖㔗

NOT NULL

庖枕婉噕螩寙劆 NULL 唚

NULL

庖枕噕螩寙劆 NULL 唚認滇䚺䩕㔗

認婻床埖䔇庻婘埻滇婺启闼底麂湺庖 SQL 昄扞康噚垹㔗婉傺螞婘桄庫䫘婺嘪䫘垄㔗

UNIQUE (column constraint)
UNIQUE ( column_name [, ... ] ) (table constraint)

UNIQUE 亥溘臘䴺臘麯䔇婔婻潡崔婻庖枕䔇䂇劽媙釂婘噘臘评啘喙嫇婔㔗嫇婔亥溘䔇臯婺启彖亥溘婔湙埻婉誺崔庖虘崔臯䔇脘媕㔗

凹庯嫇婔亥溘蔯蘔NULL 赆螴婺滇庐婉䕩京䔇㔗

懟婻嫇婔亥溘鄘媙釂䂍噽嘪䫘䔇庖枕镖劽蕋庽婔婻婯噽垄嫇婔亥溘鄘婉劯䔇劉庖幽婫幘婉脘启婂髞亥溘䔇劉庖䕩劯劥彍儌赆螴婺滇劯湙䔇亥溘喍庖婴渇㔗

PRIMARY KEY (column constraint)
PRIMARY KEY ( column_name [, ... ] ) (table constraint)

婂髞亥溘臘滯臘婺䔇婔婻潡蔙婔底庖枕埻脘寙劆嫇婔(婉麉崉)䔇麂 NULL 唚㔗傯檔橇婪螾PRIMARY KEY 埻滇 UNIQUE 启 NOT NULL 䔇䂇劽婉誺檪婔喖庖枕湺臖婺婂髞劯施幘嘷䯄庖昇嚟螆螇䔇噄昄扞啹婺婂髞懟叿五埇傖拪認喖庖枕䫘啔臯䔇嫇婔湺臖㔗

婔婻臘埻脘弄滯婔婻婂髞婉䞇滇嘩婺庖枕亥溘誻滇臘亥溘㔗

婂髞亥溘嘪䫘䔇庖枕镖劽庫臖婯噽垄嫇婔亥溘鄘婉劯㔗

CHECK ( expression )

CHECK 亥溘弄滯婔婻䫘潊婄儫䂷悩䔇臘膆嚟懟渇儖襕某噖䔇桄臯潡蔙儖襕赆敘桄䔇臯䔇桄唚媙釂嘪臘膆嚟䂷悩婺䩘潡橻䘖欉脘潊媘劥彍嚔檕庺婔婻嚗婩幽婫婉嚔媞櫹昄扞康㔗弄滯婺庖枕亥溘䔇演昖亥溘庫臖埻嚘䫘臖庖枕䔇昄唚蔯婘臘亥溘麯庺䯄䔇臘膆嚟埇傖嚘䫘崔婻庖枕㔗

䕞嬉CHECK 臘膆嚟婉脘寙劆床昖臵幘婉脘嚘䫘鍴嘷嬉臯庖枕幋崡䔇埻麟㔗

REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (column constraint)
FOREIGN KEY ( column [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] [ MATCH matchtype ] [ ON DELETE action ] [ ON UPDATE action ] (table constraint)

認底床埖弄滯婔婻崡髞亥溘崡髞亥溘襕挗桄臘婺婔彖潡崔彖䂇潊䔇䂇庫臖埻寙劆/对陉赆埗蔄䔇臘婺凹庫庖枕婺䔇唚㔗套悩䩕䘖 refcolumn 彍嘪䫘 reftable 䔇婂髞㔗赆埗蔄庖枕媙釂滇赆埗蔄臘婺䔇嫇婔庖枕潡蔙婂髞㔗臙濘懟婉脘婘婘施臘启愩幙臘幋閘垔幬崡髞亥溘㔗

劏埗蔄庖枕某噖䔇昄唚儖嘪䫘䂍庺䔇对陉䌂傋婯赆埗蔄臘婺赆埗蔄彖䔇昄唚誕臯对陉㔗橬婬䓉对陉䌂傋MATCH FULL, MATCH PARTIAL, MATCH SIMPLE(䚺䩕)㔗MATCH FULL 婉噕螩婔婻崔庖枕崡髞䔇庖枕婺 NULL 鍴麂欔橬崡髞庖枕鄘婺 NULL 㔗MATCH SIMPLE 噕螩昊底崡髞庖枕婺 NULL 蔯崡髞䔇噽垄鄘彖婉滇 NULL 㔗MATCH PARTIAL 䕞嬉儔橻垂䯄㔗

埥崡嘷赆埗蔄庖枕婺䔇昄扞櫹埻䔇施唍闼幽儖凹橸臘䔇庖枕婺䔇昄扞欓臯昊䓉淉嘩㔗ON DELETE 床埖弄滯嘷赆埗蔄臘婺䔇赆埗蔄臯赆役鍴䔇施唍襕欓臯䔇淉嘩㔗䌂嚚䔇ON UPDATE 床埖弄滯赆埗蔄臘婺赆埗蔄庖枕敘桄婺桄唚䔇施唍襕欓臯䔇媘嘩㔗套悩臖臯赆敘桄嘖赆埗蔄䔇庖枕垂鍙婪澇橬埻寡闼幽儌婉嚔橬傂嘘媘嘩㔗鍴庖 NO ACTION 演昖幋崡䔇噽傡埗蔄媘嘩鄘婉脘毘誘剿嘪臖亥溘弄滯婺埇毘誘幘滇套溴㔗婋麵滇懟婻床埖䔇埇脘媘嘩

NO ACTION

䫘潊婔婻髍臇臘滯役鍴潡敘桄儖库䫘婔婻誺埉崡髞亥溘䔇媘嘩㔗套悩臖亥溘滇埇毘誘䔇幽婫套悩誻庻婘傂嘘嚘䫘臯闼幽認婻髍臇儖婘演昖亥溘䔇施唍䫘潊㔗認滇䚺䩕媘嘩㔗

RESTRICT

䫘潊婔婻臘滯役鍴潡敘桄儖凚躘誺埉崡髞亥溘䔇髍臇㔗启 NO ACTION 婔湙埻滇媘嘩婉埇毘誘㔗

CASCADE

役鍴傂嘘嚘䫘庖赆役鍴臯䔇臯潡蔙彖彆檪嚘䫘臯䔇庖枕唚敘桄婺赆埗蔄庖枕䔇桄昄唚㔗

SET NULL

檪嚘䫘臯螆䘞婺 NULL 㔗

SET DEFAULT

檪嚘䫘庖枕螆䘞婺垄傸䔇䚺䩕唚㔗

套悩赆埗蔄庖枕䂟婩敘桄闼幽䂍崡髞庖枕嵂媹婔婻䘵嚘埇脘滇劽锗䔇認湙婯崡髞庖枕䕩噿蕫䔇嚘䫘媘嘩埇傖敘橬昽婄欓臯㔗

DEFERRABLE
NOT DEFERRABLE

認婴婻噿髞庖螆䘞臖亥溘滇劥埇毘誘㔗婔婻婉埇毘誘䔇亥溘儖婘懟溇变傴幋劯鷸婪演昖㔗埇毘誘亥溘埇傖毘誘彄庋媇䂷儆嘪䫘 SET CONSTRAINTS 变傴演昖㔗䚺䩕滇 NOT DEFERRABLE 㔗䕞嬉埻橬崡髞亥溘毖埖認婻床埖㔗欔橬噽垄亥溘䌂傋鄘滇婉埇毘誘䔇㔗

INITIALLY IMMEDIATE
INITIALLY DEFERRED

套悩亥溘滇埇毘誘䔇闼幽認婻床埖弄滯演昖亥溘䔇䚺䩕施閘㔗套悩亥溘滇 INITIALLY IMMEDIATE(䚺䩕)闼幽懟溇臺埖幋劯儌䆋剿演昖垄㔗套悩亥溘滇 INITIALLY DEFERRED 闼幽埻橬婘庋媇䂷儆欉演昖垄㔗亥溘演昖䔇施閘埇傖䫘 SET CONSTRAINTS 变傴媞櫹㔗

WITH ( storage_parameter [= value] [, ... ] )

認婻床埖婺臘潡毺垔婔婻埇锬䔇庻嗘埗昄埗蓕庻嗘埗昄诙埡敘崔媇敇㔗䫘庯臘䔇 WITH 床埖誻埇傖寙劆 OIDS=TRUE 潡剘䋸䔇 OIDS 準毺垔䂍桄臘婺䔇懟婔臯鄘彖陉婔婻 OID(凹茇湺臖严)潡蔙 OIDS=FALSE 臘䴺婉彖陉 OID 㔗套悩澇橬毺垔 OIDS 邻螴臯婺埡喿庯 default_with_oids 陉䘞埗昄㔗套悩桄臘滇傯橬 OID 䔇臘䂓欪蔯準闼幽剿嘪滯䇞毺垔 OIDS=FALSE 幘儖嚺彽毬䙓 OIDS=TRUE 欓臯㔗

套悩滯䇞潡锊劆䔇毺垔庖 OIDS=FALSE 桄臘儖婉嚔庻嗘 OID 幘婉嚔婺臯彖陉 OID 㔗認儖废償 OID 䔇嚔體幽啹溴傽䚷庖 32-bit OID 螇昄単䔇冻䯇㔗啹婺婔斥螇昄単埏䫘冻䯇幋劯 OID 儖婉脘赆蓖婺嫇婔認儖崓崓鍉嘯 OID 䔇垂䫘攓㔗埥崡毐鍴庖 OID 䔇臘幘婺懟溇螄嘘废償庖 4 庖誗䔇庻嗘䷺閘傯蔯埇傖䘉冞柊剺婔底攓脘㔗

埇傖嘪䫘 ALTER TABLE 傯噾橬䔇臘婺役鍴 OID 彖㔗

WITH OIDS
WITHOUT OIDS

認底滇赆庘嚄䔇㔕彖彆京備庯 WITH (OIDS) 启 WITH (OIDS=FALSE) 䔇臺濘㔗

ON COMMIT

毓彽婘施臘婘庋媇庖䂷儆䔇臯婺㔗認婬婻锬釹滇

PRESERVE ROWS

婉麺埡傂嘘䬹彆䔇媘嘩認滇䚺䩕㔗

DELETE ROWS

役鍴婘施臘婺䔇欔橬臯㔗橸蘘婪滇婘懟渇柊庴庋媇劯躻媘欓臯婔婻 TRUNCATE 变傴㔗

DROP

役鍴婘施臘

TABLESPACE tablespace

毺垔桄臘儖襕婘 tablespace 臘䷺閘喙录傺㔗套悩澇橬弄滯儖嘪䫘 default_tablespace 套悩 default_tablespace 婺䷺闼幽儖嘪䫘昄扞康䔇䚺䩕臘䷺閘㔗

USING INDEX TABLESPACE tablespace

認婻床埖噕螩锬拷婯婔婻 UNIQUE 潡 PRIMARY KEY 亥溘䕩噿䔇䘵嚘录傺施欔婘䔇臘䷺閘㔗套悩澇橬柊冕認婻床埖認婻䘵嚘儖婘 default_tablespace 婺录傺套悩 default_tablespace 婺䷺闼幽儖嘪䫘昄扞康䔇䚺䩕臘䷺閘㔗

庻嗘埗昄

WITH 床埖埇傖婺臘毺垔庻嗘埗昄幽婘䘵嚘婪录傺 UNIQUE 潡 PRIMARY KEY 亥溘㔗䫘庯䘵嚘䔇庻嗘埗昄婘 CREATE INDEX 麯麵柟誄㔗䕞嬉嫇婔婔婻埇傖婘臘婪嘪䫘䔇庻嗘埗昄滇

FILLFACTOR

婔婻臘䔇准噙啹床(fillfactor)滇婔婻傋庯 10 启 100 幋閘䔇䍆彖昄㔗100(垯噘准噙)滇邻螴唚㔗套悩毺垔庖膄償䔇准噙啹床INSERT 淉嘩備毬䙓准噙啹床毺垔䔇䍆彖䯺准噙臘釕㔗懟婻釕婪䔇嬷嘍䷺閘儖䫘庯婘臖釕婪敘桄臯認儌嘪冖 UPDATE 橬橺嚔婘劯婔釕婪櫆䘞劯婔溇螄嘘䔇桄䬽橸認懫檪桄䬽橸櫆䘞婘噽垄釕婪敘橬昽㔗凹庯婔婻傯婉敘桄䔇臘儖准噙啹床螆婺 100 滇橔嘿锬拷嘖滇凹庯鵏乕敘桄䔇臘膄償䔇准噙啹床彍敘媹橬昽㔗

濘懟

婉傺螞婘桄庫䫘婺嘪䫘 OID 埇脘愙喕婋敘喘䔇锬拷滇嘪䫘婔婻 SERIAL 潡蔙噽垄废彖埏䫘単啔臘䔇婂髞㔗套悩婔婻庫䫘嘪䫘庖 OID 湺臖臘婺䔇䬹垔臯闼幽傺螞婘臖臘䔇 oid 庖枕婪录傺婔婻嫇婔亥溘傖䇞媺臖臘䔇 OID 剿嘪婘螇昄単冻䯇幋劯幘滇嫇婔䔇㔗套悩嘹驔襕婔婻昘婻昄扞康评啘䔇嫇婔湺臖闼幽儌襕镪噉啺螆 OID 滇虘臘嫇婔䔇嘹埇傖䫘 tableoid 启臯 OID 䔇䂇劽準垂䯄認婻䕞䔇㔗

㔊柊䴺㔏凹闼底澇橬婂髞䔇臘婉傺螞嘪䫘 OIDS=FALSE 啹婺套悩斵澇橬 OID 埽澇橬嫇婔昄扞庖枕闼幽儌冽锆湺臖䬹垔䔇臯㔗

PostgreSQL 躻媘婺懟婻嫇婔亥溘启婂髞亥溘录傺婔婻䘵嚘傖䇞媺噽嫇婔攓㔗啹溴婉媙婺婂髞庖枕滯䇞䔇录傺䘵嚘㔗埗黙 CREATE INDEX 诙埡敘崔媇敇㔗

嫇婔亥溘启婂髞婘䕞嬉䔇垂䯄麯滇婉脘䂓欪䔇㔗套悩檪䂓欪启嫇婔亥溘䂇劽婘婔蕙嚔凚躘方濘誊蘸㔗

婔婻臘婉脘轙誺 1600 婻庖枕㔗垂鍙䔇鍊彽懫認婻敘嘯啹婺誻橬噄䂇阪庥鍊彽㔗

冋床

录傺 films 启 distributors 臘

CREATE TABLE films (
    code        char(5) CONSTRAINT firstkey PRIMARY KEY,
    title       varchar(40) NOT NULL,
    did         integer NOT NULL,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute
);
CREATE TABLE distributors (
     did    integer PRIMARY KEY DEFAULT nextval('serial'),
     name   varchar(40) NOT NULL CHECK (name <> '')
);

录傺婔婻婥橬 2 䂘昄䂇䔇臘

CREATE TABLE array_int (
    vector  int[][]
);

婺臘 films 垔幬婔婻嫇婔臘亥溘㔗嫇婔臘亥溘埇傖婘臘䔇婔婻潡崔婻庖枕婪垔幬

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT production UNIQUE(date_prod)
);

垔幬婔婻演昖彖亥溘

CREATE TABLE distributors (
    did     integer CHECK (did > 100),
    name    varchar(40)
);

垔幬婔婻演昖臘亥溘

CREATE TABLE distributors (
    did     integer,
    name    varchar(40)
    CONSTRAINT con1 CHECK (did > 100 AND name <> '')
);

婺臘 films 垔幬婔婻婂髞臘亥溘㔗婂髞臘亥溘埇傖婘婔婻潡崔婻庖枕婪垔幬㔗

CREATE TABLE films (
    code        char(5),
    title       varchar(40),
    did         integer,
    date_prod   date,
    kind        varchar(10),
    len         interval hour to minute,
    CONSTRAINT code_title PRIMARY KEY(code,title)
);

婺臘 distributors 垔幬婔婻婂髞亥溘㔗婋麵婴婻冋床滇京昽䔇丸婔婻冋床嘪䫘庖臘亥溘臺濘丸庯婻嘪䫘庖彖亥溘臺濘㔗

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    PRIMARY KEY(did)
);
CREATE TABLE distributors (
    did     integer PRIMARY KEY,
    name    varchar(40)
);

婋麵認婻冋床䂍庖枕 name 蕋庽庖婔婻桺橸婩麟䚺䩕唚幽婫儖庖枕 did 䔇䚺䩕唚垬毐婺锔誺锬拷废彖凹茇䔇婋婔婻唚䫘潊㔗modtime 䔇䚺䩕唚儖滇臖臯某噖䔇施閘潿㔗

CREATE TABLE distributors (
    name      varchar(40) DEFAULT 'Luso Films',
    did       integer DEFAULT nextval('distributors_serial'),
    modtime   timestamp DEFAULT current_timestamp
);

婘臘 distributors 婪垔幬婴婻 NOT NULL 彖亥溘噽婺幋婔滯䇞䂍庺庖劉庖

CREATE TABLE distributors (
    did     integer CONSTRAINT no_null NOT NULL,
    name    varchar(40) NOT NULL
);

婺 name 庖枕垔幬婔婻嫇婔亥溘

CREATE TABLE distributors (
    did     integer,
    name    varchar(40) UNIQUE
);

婪麵䔇启婋麵認湙嘩婺婔婻臘亥溘弄滯滇婔湙䔇

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name)
);

录傺劯湙䔇臘幽婺臘傖埪嫇婔䘵嚘毺垔 70% 䯺

CREATE TABLE distributors (
    did     integer,
    name    varchar(40),
    UNIQUE(name) WITH (fillfactor=70)
)
WITH (fillfactor=70);

婘臘䷺閘 diskvol1 麯录傺 cinemas 臘

CREATE TABLE cinemas (
        id serial,
        name text,
        location text
) TABLESPACE diskvol1;

噚垹攓

CREATE TABLE 镕冻 SQL 湺庖婔底冋崡愙喕婘婋麵彖庺㔗

婘施臘

儘䞇 CREATE TEMPORARY TABLE 䔇臺濘启 SQL 湺庖䔇䌂嚚嘖滇昽悩滇婉劯䔇㔗婘湺庖麯婘施臘埻滇垔幬婔渇幽婫傯䷺喙垹嚔哋躻媘庻婘庯傂嘘驔襕垄傸䔇嚔臺婺㔗PostgreSQL 襕挗懟婻嚔臺婺垄傸嘪䫘䔇懟婻婘施臘埏庺垄傸躻噌䔇 CREATE TEMPORARY TABLE 变傴㔗認湙儌噕螩婉劯䔇嚔臺儖䕩劯䔇婘施臘劉庖䫘庯婉劯䔇䕞䔇蔯湺庖䔇垂䯄桹濘彍檪婔婻婘施臘劉庖亥溘婺噙橬䕩劯䔇臘䂷悇㔗

湺庖垔幬䔇婘施臘䔇臯婺赆幪濕婄媘䘖庖㔗PostgreSQL 婘認桹麵婪䔇臯婺䌂嚚庯螩崔噽垄 SQL 昄扞康係䂘㔗

湺庖婺䔇噘匔启匔鄘婘施臘幋閘䔇寺彆婘 PostgreSQL 麯婉庻婘啹婺認䓉寺彆埡喿庯昇庖䔇楗媕蔯 PostgreSQL 澇橬認婻楗媕㔗庺庯噚垹蔄荏PostgreSQL 儖毖埖婘施臘弄滯婺䔇 GLOBAL 启 LOCAL 噿髞庖嘖滇傡傸澇橬傂嘘嘩䫘㔗

婘施臘䔇 ON COMMIT 床埖幘䌂嚚庯 SQL 湺庖嘖滇橬底寺彆㔗套悩媘䘖庖 ON COMMIT 床埖SQL 湺庖弄滯䚺䩕䔇臯婺滇 ON COMMIT DELETE ROWS 㔗嘖滇 PostgreSQL 麯䔇䚺䩕臯婺滇 ON COMMIT PRESERVE ROWS 㔗婘 SQL 湺庖麯婉庻婘 ON COMMIT DROP 锬釹㔗

彖演昖亥溘

SQL 湺庖臘 CHECK 彖亥溘埻脘嚘䫘傡傸嘩䫘䔇庖枕埻橬 CHECK 臘亥溘欉脘嚘䫘崔婻庖枕㔗PostgreSQL 幽婉嚺彽認婻鍊彽垄檪庖枕启臘亥溘䩋嘩䕩劯䔇婩薪㔗

NULL "亥溘"

NULL "亥溘"(垂鍙婪婉滇亥溘)滇 PostgreSQL 凹 SQL 湺庖䔇欷匘寙拸垄滇婺庖启噽垄婔底昄扞康係䂘噚垹傖埪婺庖启 NOT NULL 亥溘凹䓄㔗啹婺垄滇傂嘘庖枕䔇䚺䩕欔傖垄䔇庺䯄滇澇橬懟幬䔇㔗

䂓欪

锔誺 INHERITS 床埖䔇崔麉䂓欪滇 PostgreSQL 臺蘔䔇欷匘㔗SQL:1999 埪傖劯䔇湺庖嘪䫘婉劯䔇臺濘启臺幬垔幬庖剘䂓欪㔗SQL:1999鼯湚䔇䂓欪誻澇橬婘 PostgreSQL 婺垂䯄㔗

镽庖枕臘

PostgreSQL 噕螩录傺澇橬庖枕䔇臘(懫套 CREATE TABLE foo();)㔗認滇凹 SQL 湺庖䔇欷匘湺庖婉噕螩庻婘镽庖枕臘㔗镽庖枕臘橸躆澇傔幽䫘嘖滇䥕溵傡傸嚔䂍 ALTER TABLE DROP COLUMN 婥準冽喺攻䔇愙喕欔傖認婻施唍媘蓖湺庖䔇鍊彽楗媕麂婩橙斔㔗

WITH 床埖

WITH 床埖滇 PostgreSQL 䔇欷匘劯湙庻嗘埗昄启 OID 幘滇欷匘㔗

臘䷺閘

PostgreSQL 䔇臘䷺閘楗媕婉滇湺庖䔇婩薪㔗啹溴 TABLESPACE 启 USING INDEX TABLESPACE 鄘滇欷匘㔗

埽蓕

ALTER TABLE, DROP TABLE, CREATE TABLESPACE
劯锔饡釕嬉誕
CREATE SEQUENCE婪婔亓CREATE TABLE AS