昄唚䌂傋䫌 2 㔕4 潡 8 庖誗䔇昘昄傖埪 4 潡 8 庖誗䔇敞䗹昄启埇锬來庥䔇償昄䂇潊㔗臘8-2彖庺庖欔橬埇䫘䌂傋㔗
臘8-2. 昄唚䌂傋
| 劉庖 | 庻嗘䷺閘 | 柟誄 | 评啘 |
|---|---|---|---|
| smallint | 2 庖誗 | 償评啘昘昄 | -32768 彄 +32767 |
| integer | 4 庖誗 | 婩䫘䔇昘昄 | -2147483648 彄 +2147483647 |
| bigint | 8 庖誗 | 崓评啘䔇昘昄 | -9223372036854775808 彄 9223372036854775807 |
| decimal | 埻阪 | 䫘潙弄滯來庥來䇞 | 方鍊彽 |
| numeric | 埻阪 | 䫘潙弄滯來庥來䇞 | 方鍊彽 |
| real | 4 庖誗 | 埻來庥婉來䇞 | 6 嘉剕誕彽昄庖來庥 |
| double precision | 8 庖誗 | 埻來庥婉來䇞 | 15 嘉剕誕彽昄庖來庥 |
| serial | 4 庖誗 | 躻嵂昘昄 | 1 彄 2147483647 |
| bigserial | 8 庖誗 | 崓评啘䔇躻嵂昘昄 | 1 彄 9223372036854775807 |
昄唚䌂傋婩麟䔇臺濘婘誗4.1.2麯柟誄㔗昄唚䌂傋凹庫橬婔喖垯昘䔇昄庥淉嘩严启庘昄㔗䕩噿媇敇臙埗蔄䆹9㔗婋麵䔇庹誗臥䂖柟誄認底䌂傋㔗
smallint, integer, bigint 䌂傋庻嗘劇䓉评啘䔇噘鄘滇昄庖䔇昄幘儌滇澇橬償昄鄘彖䔇昄庖㔗臘商庻嗘轙庺评啘傖崡䔇昄唚儖凚躘婔婻髍臇㔗
婩䫘䔇䌂傋滇 integer 啹婺垄柊冕庖婘评啘㔕庻嗘䷺閘㔕攓脘幋閘䔇橔嘿广臇㔗婔轸埻橬婘伕䕻䷺閘䘓嚹䔇施唍欉嘪䫘 smallint 㔗蔯埻橬婘 integer 䔇评啘婉崘䔇施唍欉嘪䫘 bigint 啹婺嬉蔙䂺凹媆冖崔㔗
bigint 䌂傋埇脘婉滇婘欔橬广埄婪鄘誊蘸溼䇞啹婺垄冺蕡䚡臏単凹噆庖誗昘昄䔇櫇毕㔗婘闼底澇橬噆庖誗昘昄櫇毕䔇橺単婪bigint 䔇嘩䫘启 integer 婔湙(嘖滇傉䇽剹扞噆庖誗庻嗘)㔗婉誺潏傸䕞嬉誻澇劸臘誺橬認湙䔇广埄㔗
SQL 埻弄滯庖昘昄䌂傋 integer(潡 int) 启 smallint 㔗䌂傋 bigint 启䌂傋彆劉 int2, int4, int8 鄘滇欷匘幽婫幘婘螩崔噽垄 SQL 昄扞康係䂘婺嘪䫘㔗
numeric 䌂傋埇傖庻嗘橔崔 1000 嘉來庥䔇昄庖幽婫庖䇞婄誕臯螇䞖㔗潏傸䬹彆傺螞儖垄䫘庯蘓婕麏鵺启噽垄襕挗來䇞螇䞖䔇婺劽㔗婉誺numeric 䌂傋婪䔇䞖橇誊䞖懫昘昄䌂傋潡蔙潏傸婋婔誗柟誄䔇敞䗹昄䌂傋襕晵冽崔㔗
婘锟劯䔇喙垹麯潏傸嘪䫘婋誄橇臺婔婻 numeric 䌂傋䔇湺庥(scale)滇償昄鄘彖䔇嘉昄來庥(precision)滇噘鄘昄扞嘉䔇昄䕞幘儌滇償昄䗹婴膹䔇嘉昄攂启㔗啹溴昄庖 23.5141 䔇來庥婺 6 蔯湺庥婺 4 㔗嘹埇傖螴婺昘昄䔇湺庥婺镽㔗
numeric 庖枕䔇橔崓來庥启橔崓湺庥鄘滇埇傖陉䘞䔇㔗襕弄滯婔婻庖枕䔇䌂傋婺 numeric 嘹埇傖䫘婋麵䔇臺濘
NUMERIC(precision, scale)
來庥媙釂婺溼昄湺庥埇傖婺镽潡蔙溼昄㔗埥崡
NUMERIC(precision)
锬拷庖湺庥婺 0 㔗婉婥傂嘘來庥婯湺庥䔇弄滯
NUMERIC
彍录傺婔婻埇傖庻嗘婔婻䕘彄垂䯄來庥婪鍊䔇傂懟來庥启湺庥䔇昄唚婔婻認湙䌂傋䔇庖枕儖婉嚔檪膷噖昄唚蘸寡潊傂嘘䬹垔䔇湺庥蔯婥橬湺庥弄滯䔇 numeric 庖枕儖檪膷噖唚蘸寡婺臖湺庥㔗SQL 湺庖襕挗䚺䩕䔇湺庥滇 0(幘儌滇蘸寡潊昘昄來庥)㔗潏傸蓬冖認湙啔橬䗹澇䫘㔗套悩嘹噿媄䓂洉攓闼嘹橔喘攂滇滯䇞弄滯來庥启湺庥㔗
套悩婔婻襕庻嗘䔇昄唚䔇湺庥懫庖枕弄滯䔇湺庥醻闼幽係䂘儖儺臘婖昘(啕轉庫噖)臖昄唚彄毺垔䔇償昄嘉㔗䇽劯套悩償昄䗹噥膹䔇昄扞嘉昄轙誺庖弄滯䔇來庥废寂弄滯䔇湺庥闼幽儖檕庺婔婻髍臇㔗
numeric 䌂傋䔇昄扞唚婘䬷䊖婪滇婉婥傂嘘嬉凚潡蔙劯䚔镽䔇嘵嚟庻嗘䔇㔗啹溴庖枕婪弄滯䔇來庥启湺庥鄘滇橔崓唚蔯婉滇啺垔彖陉䔇㔗婘認婻桹麵numeric 䌂傋敘䌂嚚庯 varchar(n) 蔯婉滇 char(n) 㔗垂鍙庻嗘滇懟啕婻剕誕彽嘉婴婻庖誗䇽劯婘昘婻昄扞婪媹婪噆婻庖誗䔇鵺崡嚔體㔗
鍴庖捞锔䔇昄庖唚幋崡numeric 䌂傋噕螩䫘䬹枪唚 NaN 臘䴺"婉滇婔婻昄庖"㔗傂嘘婘 NaN 婪麵䔇淉嘩鄘䫘潊埥崡婔婻 NaN 㔗套悩婘 SQL 变傴麯檪認底唚嘷嘩婔婻婩麟喍嘹媙釂婘噽变啘櫆婪剘嚘埙懫套 UPDATE table SET x = 'NaN' 㔗婘膷噖施庖严婾 NaN 滇崓償喍方噿䔇㔗
䌂傋 decimal 启 numeric 滇京昽䔇㔗婴䓉䌂傋鄘滇 SQL 湺庖㔗
昄扞䌂傋 real 启 double precision 滇婉來䇞䔇㔕埻來庥䔇昄庖䌂傋㔗垂鍙婪認底䌂傋滇 IEEE 754 湺庖庯誕彽敞䗹昄䞖橇(彖彆凹庫剘启埯來庥)䔇婔轸垂䯄崡媹婋北崇䊖単㔕淉嘩係䂘启䚡臏単凹垄䔇櫇毕㔗
婉來䇞懟叿五婔底昄唚婉脘來䇞婄蘸扵潊喙鄘湚嚟幽婫滇傖誏嚚唚庻嗘䔇啹溴庻嗘劯喉檪昄扞欷剄庺準埇脘橬婔底噞嚗㔗崇䊖認底髍臇傖埪認底髍臇滇套嘘婘螇䞖婺嚹携䔇匂庯昄庥启螇䞖橺䓏庥䔇婔婻垯昘䔇彖櫇潏傸婉嚔婘認麯誕婔準螘螺垄認麯䔇螘螺備鍊庯套婋庹䗹
套悩嘹襕挗來䇞䔇螇䞖(懫套螇䞖蘓婕麏鵺)庫嘪䫘 numeric 䌂傋㔗
套悩嘹愿䫘認底䌂傋啔傂嘘麉襕䔇崉溗螇䞖儴噽滇闼底嘹凹评啘愙喕(方䷙/婋溵)婖麉冺蕡䔇庋愙闼嘹庫臖傫䂖臇臗嘹䔇垂䯄㔗
拪婴婻敞䗹昄唚誕臯䕩京攓懫膄埇脘償㔕幘埇脘婉償嘹愿償闼湙誊蘸㔗
婘崓崔昄广埄婪real 䌂傋䔇评啘滇躿儏 1E-37 彄 1E+37 來庥躿儏滇 6 嘉償昄㔗double precision 䔇评啘锔婩滇 1E-307 彄 1E+308 來庥滇躿儏 15 嘉昄庖㔗崻崓潡蔙崻償䔇昄唚鄘嚔凚躘髍臇㔗套悩膷噖昄扞䔇來庥崻醻闼幽儖嚔埏䫘啺昘㔗崻毖誏镽䔇昄庖套悩方濘婯镽唚䔇臘䯄嘵嚟䕩寺彖儌嚔库䫘婋溵髍臇㔗
鍴庖捞锔䔇昄庖唚幋崡敞䗹䌂傋誻橬庹婻䬹枪唚
Infinity
-Infinity
NaN
PostgreSQL 誻櫇毕 SQL 湺庖臘䴺濘 float 启 float(p) 䫘庯弄滯麂來䇞䔇昄唚䌂傋㔗噽婺䔇 p 弄滯傖庯誕彽嘉臘䴺䔇橔嘯埇毖埖來庥㔗婘锬埡 real 䌂傋䔇施唍PostgreSQL 毖埖 float(1) 彄 float(24)婘锬埡 double precision 䔇施唍毖埖 float(25) 彄 float(53) 㔗婘噕螩评啘幋崡䔇 p 唚儖凚躘婔婻髍臇㔗澇橬弄滯來庥䔇 float 儖赆嘷嘩 double precision 㔗
㔊濘懟㔏PostgreSQL 7.4傖嬉婘 float(p) 麯麵䔇來庥嚔赆嘷嘩滇認幽崔嘉昄䔇剕誕彽嘉㔗彄 7.4 噾䂟赆媞櫹潊婯 SQL 湺庖对陉湺庖弄滯認婻來庥滇傖庯誕彽嘉庥麟䔇㔗啺螆 real 启 double precision 彖彆橬 24 启 53 婻庯誕彽嘉䔇嘉昄凹 IEEE 湺庖䔇敞䗹垂䯄準臘滇溼䇞䔇㔗婘麂 IEEE 广埄婪認婻昄唚埇脘䘖橬啟噞嘖滇婺庖䞔寡潏傸婘欔橬广埄婪鄘䫘庖劯湙䔇 p 唚评啘㔗
serial 启 bigserial 䌂傋婉滇䩘溼䔇䌂傋埻滇婺婘臘婺螆䘞嫇婔湺臖啔䔇楗媕婪䔇冪彷㔗䌂嚚噽垄婔底昄扞康婺䔇 AUTO_INCREMENT 匂攓㔗婘䕞嬉䔇垂䯄婺婋麵婔婻臺埖
CREATE TABLE tablename (
colname SERIAL
);
京備庯弄滯婋麵庹婻臺埖
CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;
啹溴潏傸儌录傺庖婔婻昘昄庖枕幽婫檪垄䔇䚺䩕昄唚垬毐婺傯婔婻废彖埏䫘単臂埡㔗庫䫘庖婔婻 NOT NULL 亥溘傖䇞媺 NULL 婉嚔赆某噖㔗婘崓崔昄愙喕婋嘹埇脘誻婯橕鍇媹婔婻 UNIQUE 潡 PRIMARY KEY 亥溘镪噉懟崡婄某噖麉崉䔇昄唚嘖認婻婉滇躻媘䔇㔗橔劯儖废彖埏䫘単"傯匂庯"闼婻庖枕認湙嘷臖庖枕潡臘赆役鍴䔇施唍幘婔幽役鍴垄㔗
㔊濘懟㔏PostgreSQL 7.3傖嬉serial 锊劆 UNIQUE 㔗嘖䯄婘婉喉套溴㔗套悩嘹婯橕婔婻废彖庖枕橬婔婻嫇婔亥溘潡蔙婔婻婂髞闼幽嘹䯄婘媙釂弄滯儌償噽垄昄扞䌂傋婔湙㔗
襕婘 serial庖枕婺某噖废彖婺䔇婋婔婻昄唚婂襕滇襕濘懟 serial庖枕庫臖蕋庽䚺䩕唚㔗潏傸埇傖锔誺婘 INSERT 臺埖婺檪臖庖枕毐鍴婘庖枕彖臘幋崡準垂䯄幘埇傖锔誺嘪䫘 DEFAULT 噿髞庖準垂䯄㔗
䌂傋劉 serial 启 serial4 滇京昽䔇婴蔙鄘录傺 integer 庖枕㔗䌂傋劉 bigserial 启 serial8 幘婔湙埻婉誺垄录傺婔婻 bigint 庖枕㔗套悩嘹鵇螇婘臘䔇䫘庻橘婺嘪䫘䔇湺臖昄䕞埇脘轙誺 231 婻闼幽嘹庫臖嘪䫘 bigserial 㔗
婔婻 serial 䌂傋录傺䔇废彖婘欔匂䔇庖枕赆役鍴䔇施唍躻媘役鍴㔗嘹埇傖埻役鍴废彖蔯婉役鍴庖枕婉誺認儖役鍴臖庖枕䔇䚺䩕唚臘膆嚟㔗