\u4e00\u3001\u6570\u503c\u7c7b\u578b\uff1a<\/p>\n
\u4e0b\u9762\u662fPostgreSQL\u6240\u652f\u6301\u7684\u6570\u503c\u7c7b\u578b\u7684\u5217\u8868\u548c\u7b80\u5355\u8bf4\u660e\uff1a<\/code><\/pre>\n\u540d\u5b57 \u5b58\u50a8\u7a7a\u95f4 \u63cf\u8ff0 \u8303\u56f4
\nsmallint 2 \u5b57\u8282 \u5c0f\u8303\u56f4\u6574\u6570 -32768 \u5230 +32767
\ninteger 4 \u5b57\u8282 \u5e38\u7528\u7684\u6574\u6570 -2147483648 \u5230 +2147483647
\nbigint 8 \u5b57\u8282 \u5927\u8303\u56f4\u7684\u6574\u6570 -9223372036854775808 \u5230 9223372036854775807
\ndecimal \u53d8\u957f \u7528\u6237\u58f0\u660e\u7cbe\u5ea6\uff0c\u7cbe\u786e \u65e0\u9650\u5236
\nnumeric \u53d8\u957f \u7528\u6237\u58f0\u660e\u7cbe\u5ea6\uff0c\u7cbe\u786e \u65e0\u9650\u5236
\nreal 4 \u5b57\u8282 \u53d8\u7cbe\u5ea6\uff0c\u4e0d\u7cbe\u786e 6 \u4f4d\u5341\u8fdb\u5236\u6570\u5b57\u7cbe\u5ea6
\ndouble 8 \u5b57\u8282 \u53d8\u7cbe\u5ea6\uff0c\u4e0d\u7cbe\u786e 15 \u4f4d\u5341\u8fdb\u5236\u6570\u5b57\u7cbe\u5ea6
\nserial 4 \u5b57\u8282 \u81ea\u589e\u6574\u6570 1 \u5230 +2147483647
\nbigserial 8 \u5b57\u8282 \u5927\u8303\u56f4\u7684\u81ea\u589e\u6574\u6570 1 \u5230 9223372036854775807<\/p>\n
\n- \n
\u6574\u6570\u7c7b\u578b\uff1a
\n\u7c7b\u578bsmallint\u3001integer\u548cbigint\u5b58\u50a8\u5404\u79cd\u8303\u56f4\u7684\u5168\u90e8\u662f\u6570\u5b57\u7684\u6570\uff0c\u4e5f\u5c31\u662f\u6ca1\u6709\u5c0f\u6570\u90e8\u5206\u7684\u6570\u5b57\u3002\u8bd5\u56fe\u5b58\u50a8\u8d85\u51fa\u8303\u56f4\u4ee5\u5916\u7684\u6570\u503c\u5c06\u5bfc\u81f4\u4e00\u4e2a\u9519\u8bef\u3002\u5e38\u7528\u7684\u7c7b\u578b\u662finteger\uff0c\u56e0\u4e3a\u5b83\u63d0\u4f9b\u4e86\u5728\u8303\u56f4\u3001\u5b58\u50a8\u7a7a\u95f4\u548c\u6027\u80fd\u4e4b\u95f4\u7684\u6700\u4f73\u5e73\u8861\u3002\u4e00\u822c\u53ea\u6709\u5728\u78c1\u76d8\u7a7a\u95f4\u7d27\u5f20\u7684\u65f6\u5019\u624d\u4f7f\u7528smallint\u3002\u800c\u53ea\u6709\u5728integer\u7684\u8303\u56f4\u4e0d\u591f\u7684\u65f6\u5019\u624d\u4f7f\u7528bigint\uff0c\u56e0\u4e3a\u524d\u8005(integer)\u7edd\u5bf9\u5feb\u5f97\u591a\u3002 <\/p>\n
\n- \n
\u4efb\u610f\u7cbe\u5ea6\u6570\u503c\uff1a
\n\u7c7b\u578bnumeric\u53ef\u4ee5\u5b58\u50a8\u6700\u591a1000\u4f4d\u7cbe\u5ea6\u7684\u6570\u5b57\u5e76\u4e14\u51c6\u786e\u5730\u8fdb\u884c\u8ba1\u7b97\u3002\u56e0\u6b64\u975e\u5e38\u9002\u5408\u7528\u4e8e\u8d27\u5e01\u91d1\u989d\u548c\u5176\u5b83\u8981\u6c42\u8ba1\u7b97\u51c6\u786e\u7684\u6570\u91cf\u3002\u4e0d\u8fc7\uff0cnumeric\u7c7b\u578b\u4e0a\u7684\u7b97\u672f\u8fd0\u7b97\u6bd4\u6574\u6570\u7c7b\u578b\u6216\u8005\u6d6e\u70b9\u6570\u7c7b\u578b\u8981\u6162\u5f88\u591a\u3002
\nnumeric\u5b57\u6bb5\u7684\u6700\u5927\u7cbe\u5ea6\u548c\u6700\u5927\u6bd4\u4f8b\u90fd\u662f\u53ef\u4ee5\u914d\u7f6e\u7684\u3002\u8981\u58f0\u660e\u4e00\u4e2a\u7c7b\u578b\u4e3anumeric\u7684\u5b57\u6bb5\uff0c\u4f60\u53ef\u4ee5\u7528\u4e0b\u9762\u7684\u8bed\u6cd5\uff1a
\nNUMERIC(precision,scale)
\n\u6bd4\u5982\u6570\u5b5723.5141\u7684\u7cbe\u5ea6\u4e3a6\uff0c\u800c\u523b\u5ea6\u4e3a4\u3002
\n\u5728\u76ee\u524d\u7684PostgreSQL\u7248\u672c\u4e2d\uff0cdecimal\u548cnumeric\u662f\u7b49\u6548\u7684\u3002<\/p>\n<\/li>\n
- \n
\u6d6e\u70b9\u6570\u7c7b\u578b\uff1a
\n\u6570\u636e\u7c7b\u578breal\u548cdouble\u662f\u4e0d\u51c6\u786e\u7684\u3001\u727a\u7272\u7cbe\u5ea6\u7684\u6570\u5b57\u7c7b\u578b\u3002\u4e0d\u51c6\u786e\u610f\u5473\u7740\u4e00\u4e9b\u6570\u503c\u4e0d\u80fd\u51c6\u786e\u5730\u8f6c\u6362\u6210\u5185\u90e8\u683c\u5f0f\u5e76\u4e14\u662f\u4ee5\u8fd1\u4f3c\u7684\u5f62\u5f0f\u5b58\u50a8\u7684\uff0c\u56e0\u6b64\u5b58\u50a8\u540e\u518d\u628a\u6570\u636e\u6253\u5370\u51fa\u6765\u53ef\u80fd\u663e\u793a\u4e00\u4e9b\u7f3a\u5931\u3002<\/p>\n<\/li>\n<\/ol>\n<\/li>\n
- \n
Serial(\u5e8f\u53f7)\u7c7b\u578b\uff1a
\nserial\u548cbigserial\u7c7b\u578b\u4e0d\u662f\u771f\u6b63\u7684\u7c7b\u578b\uff0c\u53ea\u662f\u4e3a\u5728\u8868\u4e2d\u8bbe\u7f6e\u552f\u4e00\u6807\u8bc6\u505a\u7684\u6982\u5ff5\u4e0a\u7684\u4fbf\u5229\u3002
\nCREATE TABLE tablename (
\ncolname SERIAL
\n);
\n\u7b49\u4ef7\u4e8e
\nCREATE SEQUENCE tablename_colname_seq;
\nCREATE TABLE tablename(
\ncolname integer DEFAULT nextval('tablename_colname_seq') NOT NULL
\n);
\n\u8fd9\u6837\uff0c\u6211\u4eec\u5c31\u521b\u5efa\u4e86\u4e00\u4e2a\u6574\u6570\u5b57\u6bb5\u5e76\u4e14\u628a\u5b83\u7684\u7f3a\u7701\u6570\u503c\u5b89\u6392\u4e3a\u4ece\u4e00\u4e2a\u5e8f\u5217\u53d1\u751f\u5668\u53d6\u503c\u3002\u5e94\u7528\u4e86\u4e00\u4e2aNOT NULL\u7ea6\u675f\u4ee5\u786e\u4fdd\u7a7a\u503c\u4e0d\u4f1a\u88ab\u63d2\u5165\u3002\u5728\u5927\u591a\u6570\u60c5\u51b5\u4e0b\u4f60\u53ef\u80fd\u8fd8\u5e0c\u671b\u9644\u52a0\u4e00\u4e2aUNIQUE\u6216\u8005PRIMARY KEY\u7ea6\u675f\u907f\u514d\u610f\u5916\u5730\u63d2\u5165\u91cd\u590d\u7684\u6570\u503c\uff0c\u4f46\u8fd9\u4e2a\u4e0d\u662f\u81ea\u52a8\u53d1\u751f\u7684\u3002\u56e0\u6b64\uff0c\u5982\u679c\u4f60\u5e0c\u671b\u4e00\u4e2a\u5e8f\u5217\u5b57\u6bb5\u6709\u4e00\u4e2a\u552f\u4e00\u7ea6\u675f\u6216\u8005\u4e00\u4e2a\u4e3b\u952e\uff0c\u90a3\u4e48\u4f60\u73b0\u5728\u5fc5\u987b\u58f0\u660e\uff0c\u5c31\u50cf\u5176\u5b83\u6570\u636e\u7c7b\u578b\u4e00\u6837\u3002
\n\u8fd8\u9700\u8981\u53e6\u5916\u8bf4\u660e\u7684\u662f\uff0c\u4e00\u4e2aserial\u7c7b\u578b\u521b\u5efa\u7684\u5e8f\u5217\u5728\u5176\u6240\u5c5e\u5b57\u6bb5\u88ab\u5220\u9664\u65f6\uff0c\u8be5\u5e8f\u5217\u4e5f\u5c06\u88ab\u81ea\u52a8\u5220\u9664\uff0c\u4f46\u662f\u5176\u5b83\u60c5\u51b5\u4e0b\u662f\u4e0d\u4f1a\u88ab\u5220\u9664\u7684\u3002\u56e0\u6b64\uff0c\u5982\u679c\u4f60\u60f3\u7528\u540c\u4e00\u4e2a\u5e8f\u5217\u53d1\u751f\u5668\u540c\u65f6\u7ed9\u51e0\u4e2a\u5b57\u6bb5\u63d0\u4f9b\u6570\u636e\uff0c\u90a3\u4e48\u5c31\u5e94\u8be5\u4ee5\u72ec\u7acb\u5bf9\u8c61\u7684\u65b9\u5f0f\u521b\u5efa\u8be5\u5e8f\u5217\u53d1\u751f\u5668\u3002 <\/p>\n<\/li>\n<\/ol>\n
\u4e8c\u3001\u5b57\u7b26\u7c7b\u578b\uff1a<\/p>\n
\u4e0b\u9762\u662fPostgreSQL\u6240\u652f\u6301\u7684\u5b57\u7b26\u7c7b\u578b\u7684\u5217\u8868\u548c\u7b80\u5355\u8bf4\u660e\uff1a<\/code><\/pre>\n\u540d\u5b57 \u63cf\u8ff0
\nvarchar(n) \u53d8\u957f\uff0c\u6709\u957f\u5ea6\u9650\u5236
\nchar(n) \u5b9a\u957f,\u4e0d\u8db3\u8865\u7a7a\u767d
\ntext \u53d8\u957f\uff0c\u65e0\u957f\u5ea6\u9650\u5236
\nSQL \u5b9a\u4e49\u4e86\u4e24\u79cd\u57fa\u672c\u7684\u5b57\u7b26\u7c7b\u578b\uff0cvarchar(n)\u548cchar(n)\uff0c\u8fd9\u91cc\u7684n\u662f\u4e00\u4e2a\u6b63\u6574\u6570\u3002\u4e24\u79cd\u7c7b\u578b\u90fd\u53ef\u4ee5\u5b58\u50a8\u6700\u591an\u4e2a\u5b57\u7b26\u957f\u7684\u5b57\u4e32\uff0c\u8bd5\u56fe\u5b58\u50a8\u66f4\u957f\u7684\u5b57\u4e32\u5230\u8fd9\u4e9b\u7c7b\u578b\u7684\u5b57\u6bb5\u91cc\u4f1a\u4ea7\u751f\u4e00\u4e2a\u9519\u8bef\uff0c\u9664\u975e\u8d85\u51fa\u957f\u5ea6\u7684\u5b57\u7b26\u90fd\u662f\u7a7a\u767d\uff0c\u8fd9\u79cd\u60c5\u51b5\u4e0b\u8be5\u5b57\u4e32\u5c06\u88ab\u622a\u65ad\u4e3a\u6700\u5927\u957f\u5ea6\u3002\u5982\u679c\u6ca1\u6709\u957f\u5ea6\u58f0\u660e\uff0cchar\u7b49\u4e8echar(1)\uff0c\u800cvarchar\u5219\u53ef\u4ee5\u63a5\u53d7\u4efb\u4f55\u957f\u5ea6\u7684\u5b57\u4e32\u3002
\nMyTest=> CREATE TABLE testtable(first_col varchar(2));
\nCREATE TABLE
\nMyTest=> INSERT INTO testtable VALUES('333'); --\u63d2\u5165\u5b57\u7b26\u4e32\u7684\u957f\u5ea6\uff0c\u8d85\u8fc7\u5176\u5b57\u6bb5\u5b9a\u4e49\u7684\u957f\u5ea6\uff0c\u56e0\u6b64\u62a5\u9519\u3002
\nERROR: value too long for type character varying(2)
\n--\u63d2\u5165\u5b57\u7b26\u4e32\u4e2d\uff0c\u8d85\u51fa\u5b57\u6bb5\u5b9a\u4e49\u957f\u5ea6\u7684\u90e8\u5206\u662f\u7a7a\u683c\uff0c\u56e0\u6b64\u53ef\u4ee5\u63d2\u5165\uff0c\u4f46\u662f\u7a7a\u767d\u7b26\u88ab\u622a\u65ad\u3002
\nMyTest=> INSERT INTO testtable VALUES('33 ');
\nINSERT 0 1
\nMyTest=> SELECT * FROM testtable;
\nfirst_col<\/h2>\n 33\n(1 row)\n\u8fd9\u91cc\u9700\u8981\u6ce8\u610f\u7684\u662f\uff0c\u5982\u679c\u662f\u5c06\u6570\u503c\u8f6c\u6362\u6210char(n)\u6216\u8005varchar(n)\uff0c\u90a3\u4e48\u8d85\u957f\u7684\u6570\u503c\u5c06\u88ab\u622a\u65ad\u6210n\u4e2a\u5b57\u7b26\uff0c\u800c\u4e0d\u4f1a\u629b\u51fa\u9519\u8bef\u3002\nMyTest=> select 1234::varchar(2);\n varchar\n---------\n 12\n(1 row)\n\u6700\u540e\u9700\u8981\u63d0\u793a\u7684\u662f\uff0c\u8fd9\u4e09\u79cd\u7c7b\u578b\u4e4b\u95f4\u6ca1\u6709\u6027\u80fd\u5dee\u522b\uff0c\u53ea\u4e0d\u8fc7\u662f\u5728\u4f7f\u7528char\u7c7b\u578b\u65f6\u589e\u52a0\u4e86\u5b58\u50a8\u5c3a\u5bf8\u3002\u867d\u7136\u5728\u67d0\u4e9b\u5176\u5b83\u7684\u6570\u636e\u5e93\u7cfb\u7edf\u91cc\uff0cchar(n)\u6709\u4e00\u5b9a\u7684\u6027\u80fd\u4f18\u52bf\uff0c\u4f46\u5728PostgreSQL\u91cc\u6ca1\u6709\u3002\u5728\u5927\u591a\u6570\u60c5\u51b5\u4e0b\uff0c\u5e94\u8be5\u4f7f\u7528text\u6216\u8005varchar\u3002<\/code><\/pre>\n\u4e09\u3001\u65e5\u671f\/\u65f6\u95f4\u7c7b\u578b\uff1a<\/p>\n
\u4e0b\u9762\u662fPostgreSQL\u6240\u652f\u6301\u7684\u65e5\u671f\/\u65f6\u95f4\u7c7b\u578b\u7684\u5217\u8868\u548c\u7b80\u5355\u8bf4\u660e\uff1a<\/code><\/pre>\n\u540d\u5b57 \u5b58\u50a8\u7a7a\u95f4 \u63cf\u8ff0 \u6700\u4f4e\u503c \u6700\u9ad8\u503c \u5206\u8fa8\u7387
\ntimestamp[\u65e0\u65f6\u533a] 8\u5b57\u8282 \u5305\u62ec\u65e5\u671f\u548c\u65f6\u95f4 4713 BC 5874897AD 1\u6beb\u79d2\/14\u4f4d
\ntimestamp[\u542b\u65f6\u533a] 8\u5b57\u8282 \u65e5\u671f\u548c\u65f6\u95f4\uff0c\u5e26\u65f6\u533a 4713 BC 5874897AD 1\u6beb\u79d2\/14\u4f4d
\ninterval 12\u5b57\u8282 \u65f6\u95f4\u95f4\u9694 -178000000\u5e74 178000000\u5e74 1\u6beb\u79d2\/14\u4f4d
\ndate 4\u5b57\u8282 \u53ea\u7528\u4e8e\u65e5\u671f 4713 BC 32767AD 1\u5929
\ntime[\u65e0\u65f6\u533a] 8\u5b57\u8282 \u53ea\u7528\u4e8e\u4e00\u65e5\u5185\u65f6\u95f4 00:00:00 24:00:00 1\u6beb\u79d2\/14\u4f4d<\/p>\n
1. \u65e5\u671f\/\u65f6\u95f4\u8f93\u5165:\n\u4efb\u4f55\u65e5\u671f\u6216\u8005\u65f6\u95f4\u7684\u6587\u672c\u8f93\u5165\u5747\u9700\u8981\u7531\u5355\u5f15\u53f7\u5305\u56f4\uff0c\u5c31\u8c61\u4e00\u4e2a\u6587\u672c\u5b57\u7b26\u4e32\u4e00\u6837\u3002\n1). \u65e5\u671f:\n\u4ee5\u4e0b\u4e3a\u5408\u6cd5\u7684\u65e5\u671f\u683c\u5f0f\u5217\u8868\uff1a<\/code><\/pre>\n\u4f8b\u5b50 \u63cf\u8ff0
\nJanuary 8, 1999 \u5728\u4efb\u4f55datestyle\u8f93\u5165\u6a21\u5f0f\u4e0b\u90fd\u65e0\u6b67\u4e49
\n1999-01-08 ISO-8601\u683c\u5f0f\uff0c\u4efb\u4f55\u65b9\u5f0f\u4e0b\u90fd\u662f1999\u5e741\u67088\u53f7\uff0c(\u5efa\u8bae\u683c\u5f0f)
\n1\/8\/1999 \u6b67\u4e49\uff0c\u5728MDY\u4e0b\u662f1\u67088\u53f7\uff1b\u5728 DMY\u6a21\u5f0f\u4e0b\u8bfb\u505a8\u67081\u65e5
\n1\/18\/1999 \u5728MDY\u6a21\u5f0f\u4e0b\u8bfb\u505a1\u670818\u65e5\uff0c\u5176\u5b83\u6a21\u5f0f\u4e0b\u88ab\u62d2\u7edd
\n01\/02\/03 MDY\u6a21\u5f0f\u4e0b\u76842003\u5e741\u67082\u65e5\uff1bDMY\u6a21\u5f0f\u4e0b\u76842003\u5e742\u67081\u65e5\uff1bYMD \u6a21\u5f0f\u4e0b\u76842001\u5e742\u67083\u65e5
\n1999-Jan-08 \u4efb\u4f55\u6a21\u5f0f\u4e0b\u90fd\u662f1\u67088\u65e5
\nJan-08-1999 \u4efb\u4f55\u6a21\u5f0f\u4e0b\u90fd\u662f1\u67088\u65e5
\n08-Jan-1999 \u4efb\u4f55\u6a21\u5f0f\u4e0b\u90fd\u662f1\u67088\u65e5
\n99-Jan-08 \u5728YMD\u6a21\u5f0f\u4e0b\u662f1\u67088\u65e5\uff0c\u5426\u5219\u9519\u8bef
\n08-Jan-99 1\u67088\u65e5\uff0c\u9664\u4e86\u5728YMD\u6a21\u5f0f\u4e0b\u662f\u9519\u8bef\u7684\u4e4b\u5916
\nJan-08-99 1\u67088\u65e5\uff0c\u9664\u4e86\u5728YMD\u6a21\u5f0f\u4e0b\u662f\u9519\u8bef\u7684\u4e4b\u5916
\n19990108 ISO-8601; \u4efb\u4f55\u6a21\u5f0f\u4e0b\u90fd\u662f1999\u5e741\u67088\u65e5
\n990108 ISO-8601; \u4efb\u4f55\u6a21\u5f0f\u4e0b\u90fd\u662f1999\u5e741\u67088\u65e5
\n2). \u65f6\u95f4\uff1a
\n\u4ee5\u4e0b\u4e3a\u5408\u6cd5\u7684\u65f6\u95f4\u683c\u5f0f\u5217\u8868\uff1a<\/p>\n
\u4f8b\u5b50 \u63cf\u8ff0
\n04:05:06.789 ISO 8601
\n04:05:06 ISO 8601
\n04:05 ISO 8601
\n040506 ISO 8601
\n04:05 AM \u4e0e04:05\u4e00\u6837\uff1bAM\u4e0d\u5f71\u54cd\u6570\u503c
\n04:05 PM \u4e0e16:05\u4e00\u6837\uff1b\u8f93\u5165\u5c0f\u65f6\u6570\u5fc5\u987b <= 12
\n04:05:06.789-8 ISO 8601
\n04:05:06-08:00 ISO 8601
\n04:05-08:00 ISO 8601
\n040506-08 ISO 8601
\n3). \u65f6\u95f4\u6233\uff1a
\n\u65f6\u95f4\u6233\u7c7b\u578b\u7684\u6709\u6548\u8f93\u5165\u7531\u4e00\u4e2a\u65e5\u671f\u548c\u65f6\u95f4\u7684\u8054\u63a5\u7ec4\u6210\uff0c\u540e\u9762\u8ddf\u7740\u4e00\u4e2a\u53ef\u9009\u7684\u65f6\u533a\u3002\u56e0\u6b64\uff0c1999-01-08 04:05:06\u548c1999-01-08 04:05:06 -8:00\u90fd\u662f\u6709\u6548\u7684\u6570\u503c\u3002<\/p>\n
\n- \n
\u793a\u4f8b\uff1a
\n1). \u5728\u63d2\u5165\u6570\u636e\u4e4b\u524d\u5148\u67e5\u770bdatestyle\u7cfb\u7edf\u53d8\u91cf\u7684\u503c\uff1a
\nMyTest=> show datestyle;
\nDateStyle<\/h2>\n
ISO, YMD
\n(1 row)
\n2). \u521b\u5efa\u5305\u542b\u65e5\u671f\u3001\u65f6\u95f4\u548c\u65f6\u95f4\u6233\u7c7b\u578b\u7684\u793a\u4f8b\u8868\uff1a
\nMyTest=> CREATE TABLE testtable (id integer, date_col date, time_col time, timestamp_col timestamp);
\nCREATE TABLE
\n3). \u63d2\u5165\u6570\u636e\uff1a
\nMyTest=> INSERT INTO testtable(id,date_col) VALUES(1, DATE'01\/02\/03'); --datestyle\u4e3aYMD
\nINSERT 0 1
\nMyTest=> SELECT id, date_col FROM testtable;
\nid | date_col
\n----+------------
\n1 | 2001-02-03
\n(1 row)<\/p>\n
MyTest=> set datestyle = MDY;
\nSET
\nMyTest=> INSERT INTO testtable(id,date_col) VALUES(2, DATE'01\/02\/03'); --datestyle\u4e3aMDY
\nINSERT 0 1
\nMyTest=> SELECT id,date_col FROM testtable;
\nid | date_col
\n----+------------
\n1 | 2001-02-03
\n2 | 2003-01-02 <\/p>\n
MyTest=> INSERT INTO testtable(id,time_col) VALUES(3, TIME'10:20:00'); --\u63d2\u5165\u65f6\u95f4\u3002
\nINSERT 0 1
\nMyTest=> SELECT id,time_col FROM testtable WHERE time_col IS NOT NULL;
\nid | time_col
\n----+----------
\n3 | 10:20:00
\n(1 row)<\/p>\n
MyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(4, DATE'01\/02\/03');
\nINSERT 0 1
\nMyTest=> INSERT INTO testtable(id,timestamp_col) VALUES(5, TIMESTAMP'01\/02\/03 10:20:00');
\nINSERT 0 1
\nMyTest=> SELECT id,timestamp_col FROM testtable WHERE timestamp_col IS NOT NULL;
\nid | timestamp_col
\n----+---------------------
\n4 | 2003-01-02 00:00:00
\n5 | 2003-01-02 10:20:00
\n(2 rows)<\/p>\n<\/li>\n<\/ol>\n
\u56db\u3001\u5e03\u5c14\u7c7b\u578b\uff1a<\/p>\n
PostgreSQL\u652f\u6301\u6807\u51c6\u7684SQL boolean\u6570\u636e\u7c7b\u578b\u3002boolean\u53ea\u80fd\u6709\u4e24\u4e2a\u72b6\u6001\u4e4b\u4e00\uff1a\u771f(True)\u6216 \u5047(False)\u3002\u8be5\u7c7b\u578b\u5360\u75281\u4e2a\u5b57\u8282\u3002\n\"\u771f\"\u503c\u7684\u6709\u6548\u6587\u672c\u503c\u662f\uff1a\nTRUE\n't'\n'true'\n'y'\n'yes'\n'1'\n\u800c\u5bf9\u4e8e\"\u5047\"\u800c\u8a00\uff0c\u4f60\u53ef\u4ee5\u4f7f\u7528\u4e0b\u9762\u8fd9\u4e9b\uff1a\nFALSE\n'f'\n'false'\n'n'\n'no'\n'0'<\/code><\/pre>\n\u3000 \u89c1\u5982\u4e0b\u4f7f\u7528\u65b9\u5f0f\uff1a
\nMyTest=> CREATE TABLE testtable (a boolean, b text);
\nCREATE TABLE
\nMyTest=> INSERT INTO testtable VALUES(TRUE, 'sic est');
\nINSERT 0 1
\nMyTest=> INSERT INTO testtable VALUES(FALSE, 'non est');
\nINSERT 0 1
\nMyTest=> SELECT FROM testtable;
\na | b
\n---+---------
\nt | sic est
\nf | non est
\n(2 rows)
\nMyTest=> SELECT <\/em> FROM testtable WHERE a;
\na | b
\n---+---------
\nt | sic est
\n(1 row)
\nMyTest=> SELECT * FROM testtable WHERE a = true;
\na | b
\n---+---------
\nt | sic est
\n(1 row)<\/p>\n\u4e94\u3001\u4f4d\u4e32\u7c7b\u578b\uff1a<\/p>\n
\u4f4d\u4e32\u5c31\u662f\u4e00\u4e321\u548c0\u7684\u5b57\u4e32\u3002\u5b83\u4eec\u53ef\u4ee5\u7528\u4e8e\u5b58\u50a8\u548c\u89c6\u89c9\u5316\u4f4d\u63a9\u7801\u3002\u6211\u4eec\u6709\u4e24\u79cd\u7c7b\u578b\u7684SQL\u4f4d\u7c7b\u578b\uff1abit(n)\u548cbit varying(n); \u8fd9\u91cc\u7684n\u662f\u4e00\u4e2a\u6b63\u6574\u6570\u3002bit\u7c7b\u578b\u7684\u6570\u636e\u5fc5\u987b\u51c6\u786e\u5339\u914d\u957f\u5ea6n; \u8bd5\u56fe\u5b58\u50a8\u77ed\u4e9b\u6216\u8005\u957f\u4e00\u4e9b\u7684\u6570\u636e\u90fd\u662f\u9519\u8bef\u7684\u3002\u7c7b\u578bbit varying\u6570\u636e\u662f\u6700\u957fn\u7684\u53d8\u957f\u7c7b\u578b\uff1b\u66f4\u957f\u7684\u4e32\u4f1a\u88ab\u62d2\u7edd\u3002\u5199\u4e00\u4e2a\u6ca1\u6709\u957f\u5ea6\u7684bit\u7b49\u6548\u4e8ebit(1)\uff0c\u6ca1\u6709\u957f\u5ea6\u7684bit varying\u76f8\u5f53\u4e8e\u6ca1\u6709\u957f\u5ea6\u9650\u5236\u3002\n\u9488\u5bf9\u8be5\u7c7b\u578b\uff0c\u6700\u540e\u9700\u8981\u63d0\u9192\u7684\u662f\uff0c\u5982\u679c\u6211\u4eec\u660e\u786e\u5730\u628a\u4e00\u4e2a\u4f4d\u4e32\u503c\u8f6c\u6362\u6210bit(n)\uff0c\u90a3\u4e48\u5b83\u7684\u53f3\u8fb9\u5c06\u88ab\u622a\u65ad\u6216\u8005\u5728\u53f3\u8fb9\u8865\u9f50\u96f6\uff0c\u76f4\u5230\u521a\u597dn\u4f4d\uff0c\u800c\u4e0d\u4f1a\u629b\u51fa\u4efb\u4f55\u9519\u8bef\u3002\u7c7b\u4f3c\u5730\uff0c\u5982\u679c\u6211\u4eec\u660e\u786e\u5730\u628a\u4e00\u4e2a\u4f4d\u4e32\u6570\u503c\u8f6c\u6362\u6210bit varying(n)\uff0c\u5982\u679c\u5b83\u8d85\u8fc7n\u4f4d\uff0c\u90a3\u4e48\u5b83\u7684\u53f3\u8fb9\u5c06\u88ab\u622a\u65ad\u3002 \u89c1\u5982\u4e0b\u5177\u4f53\u4f7f\u7528\u65b9\u5f0f\uff1a \nMyTest=> CREATE TABLE testtable (a bit(3), b bit varying(5));\nCREATE TABLE\nMyTest=> INSERT INTO testtable VALUES (B'101', B'00');\nINSERT 0 1\nMyTest=> INSERT INTO testtable VALUES (B'10', B'101');\nERROR: bit string length 2 does not match type bit(3)\nMyTest=> INSERT INTO testtable VALUES (B'10'::bit(3), B'101');\nINSERT 0 1\nMyTest=> SELECT * FROM testtable;\n a | b\n-----+-----\n 101 | 00\n 100 | 101\n(2 rows)\nMyTest=> SELECT B'11'::bit(3);\n bit\n-----\n 110\n(1 row)<\/code><\/pre>\n\u516d\u3001\u6570\u7ec4\uff1a<\/p>\n
1. \u6570\u7ec4\u7c7b\u578b\u58f0\u660e\uff1a\n1). \u521b\u5efa\u5b57\u6bb5\u542b\u6709\u6570\u7ec4\u7c7b\u578b\u7684\u8868\u3002\nCREATE TABLE sal_emp (\n name text,\n pay_by_quarter integer[] --\u8fd8\u53ef\u4ee5\u5b9a\u4e49\u4e3ainteger[4]\u6216integer ARRAY[4]\n);\n2). \u63d2\u5165\u6570\u7ec4\u6570\u636e\uff1a\nMyTest=# INSERT INTO sal_emp VALUES ('Bill', '{11000, 12000, 13000, 14000}');\nINSERT 0 1\nMyTest=# INSERT INTO sal_emp VALUES ('Carol', ARRAY[21000, 22000, 23000, 24000]);\nINSERT 0 1\nMyTest=# SELECT * FROM sal_emp;\n name | pay_by_quarter\n--------+---------------------------\n Bill | {11000,12000,13000,14000}\n Carol | {21000,22000,23000,24000}\n(2 rows) \n\n2. \u8bbf\u95ee\u6570\u7ec4\uff1a\n\u548c\u5176\u4ed6\u8bed\u8a00\u4e00\u6837\uff0cPostgreSQL\u4e2d\u6570\u7ec4\u4e5f\u662f\u901a\u8fc7\u4e0b\u6807\u6570\u5b57(\u5199\u5728\u65b9\u62ec\u5f27\u5185)\u7684\u65b9\u5f0f\u8fdb\u884c\u8bbf\u95ee\uff0c\u53ea\u662fPostgreSQL\u4e2d\u6570\u7ec4\u5143\u7d20\u7684\u4e0b\u6807\u662f\u4ece1\u5f00\u59cbn\u7ed3\u675f\u3002\nMyTest=# SELECT pay_by_quarter[3] FROM sal_emp;\n pay_by_quarter\n----------------\n 13000\n 23000\n(2 rows)\nMyTest=# SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];\n name\n------\n Bill\n Carol\n(2 rows)\nPostgreSQL\u4e2d\u8fd8\u63d0\u4f9b\u4e86\u8bbf\u95ee\u6570\u7ec4\u8303\u56f4\u7684\u529f\u80fd\uff0c\u5373ARRAY[\u811a\u6807\u4e0b\u754c:\u811a\u6807\u4e0a\u754c]\u3002\nMyTest=# SELECT name,pay_by_quarter[1:3] FROM sal_emp;\n name | pay_by_quarter\n--------+---------------------\n Bill | {11000,12000,13000}\n Carol | {21000,22000,23000}\n(2 rows)\n\n3. \u4fee\u6539\u6570\u7ec4\uff1a\n1). \u4ee3\u66ff\u5168\u90e8\u6570\u7ec4\u503c\uff1a\n--UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000] WHERE name = 'Carol'; \u4e5f\u53ef\u4ee5\u3002\nMyTest=# UPDATE sal_emp SET pay_by_quarter = '{31000,32000,33000,34000}' WHERE name = 'Carol';\nUPDATE 1\nMyTest=# SELECT * FROM sal_emp;\n name | pay_by_quarter\n--------+---------------------------\n Bill | {11000,12000,13000,14000}\n Carol | {31000,32000,33000,34000}\n(2 rows)\n2). \u66f4\u65b0\u6570\u7ec4\u4e2d\u67d0\u4e00\u5143\u7d20\uff1a\nMyTest=# UPDATE sal_emp SET pay_by_quarter[4] = 15000 WHERE name = 'Bill';\nUPDATE 1\nMyTest=# SELECT * FROM sal_emp;\n name | pay_by_quarter\n--------+---------------------------\n Carol | {31000,32000,33000,34000}\n Bill | {11000,12000,13000,15000}\n(2 rows)\n3). \u66f4\u65b0\u6570\u7ec4\u67d0\u4e00\u8303\u56f4\u7684\u5143\u7d20\uff1a\nMyTest=# UPDATE sal_emp SET pay_by_quarter[1:2] = '{37000,37000}' WHERE name = 'Carol';\nUPDATE 1\nMyTest=# SELECT * FROM sal_emp;\n name | pay_by_quarter\n--------+---------------------------\n Bill | {11000,12000,13000,15000}\n Carol | {37000,37000,33000,34000}\n(2 rows)\n4). \u76f4\u63a5\u8d4b\u503c\u6269\u5927\u6570\u7ec4\uff1a\nMyTest=# UPDATE sal_emp SET pay_by_quarter[5] = 45000 WHERE name = 'Bill';\nUPDATE 1\nMyTest=# SELECT * FROM sal_emp;\n name | pay_by_quarter\n--------+---------------------------------\n Carol | {37000,37000,33000,34000}\n Bill | {11000,12000,13000,15000,45000}\n(2 rows)\n\n4. \u5728\u6570\u7ec4\u4e2d\u68c0\u7d22\uff1a\n1). \u6700\u7b80\u5355\u76f4\u63a5\u7684\u65b9\u6cd5\uff1a\nSELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR\n pay_by_quarter[2] = 10000 OR\n pay_by_quarter[3] = 10000 OR\n pay_by_quarter[4] = 10000; \n2). \u66f4\u52a0\u6709\u6548\u7684\u65b9\u6cd5\uff1a\nSELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter); --\u6570\u7ec4\u5143\u7d20\u4e2d\u6709\u4efb\u4f55\u4e00\u4e2a\u7b49\u4e8e10000\uff0cwhere\u6761\u4ef6\u5c06\u6210\u7acb\u3002\nSELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter); --\u53ea\u6709\u5f53\u6570\u7ec4\u4e2d\u6240\u6709\u7684\u5143\u7d20\u90fd\u7b49\u4e8e10000\u65f6\uff0cwhere\u6761\u4ef6\u624d\u6210\u7acb\u3002<\/code><\/pre>\n\u4e03\u3001\u590d\u5408\u7c7b\u578b\uff1a<\/p>\n
PostgreSQL\u4e2d\u590d\u5408\u7c7b\u578b\u6709\u4e9b\u7c7b\u4f3c\u4e8eC\u8bed\u8a00\u4e2d\u7684\u7ed3\u6784\u4f53\uff0c\u4e5f\u53ef\u4ee5\u88ab\u89c6\u4e3aOracle\u4e2d\u7684\u8bb0\u5f55\u7c7b\u578b\uff0c\u4f46\u662f\u8fd8\u662f\u611f\u89c9\u590d\u5408\u7c7b\u578b\u8fd9\u4e2a\u547d\u540d\u6bd4\u8f83\u8d34\u5207\u3002\u5b83\u5b9e\u9645\u4e0a\u53ea\u662f\u4e00\u4e2a\u5b57\u6bb5\u540d\u548c\u5b83\u4eec\u7684\u6570\u636e\u7c7b\u578b\u7684\u5217\u8868\u3002PostgreSQL\u5141\u8bb8\u50cf\u7b80\u5355\u6570\u636e\u7c7b\u578b\u90a3\u6837\u4f7f\u7528\u590d\u5408\u7c7b\u578b\u3002\u6bd4\u5982\uff0c\u8868\u5b57\u6bb5\u53ef\u4ee5\u58f0\u660e\u4e3a\u4e00\u4e2a\u590d\u5408\u7c7b\u578b\u3002\n1. \u58f0\u660e\u590d\u5408\u7c7b\u578b:\n\u4e0b\u9762\u662f\u4e24\u4e2a\u7b80\u5355\u7684\u58f0\u660e\u793a\u4f8b\uff1a\nCREATE TYPE complex AS (\n r double,\n i double\n); \nCREATE TYPE inventory_item AS (\n name text,\n supplier_id integer,\n price numeric\n);\n\u548c\u58f0\u660e\u4e00\u4e2a\u6570\u636e\u8868\u76f8\u6bd4\uff0c\u58f0\u660e\u7c7b\u578b\u65f6\u9700\u8981\u52a0AS\u5173\u952e\u5b57\uff0c\u540c\u65f6\u5728\u58f0\u660eTYPE\u65f6\u4e0d\u80fd\u5b9a\u4e49\u4efb\u4f55\u7ea6\u675f\u3002\u4e0b\u9762\u6211\u4eec\u770b\u4e00\u4e0b\u5982\u4f55\u5728\u8868\u4e2d\u6307\u5b9a\u590d\u5408\u7c7b\u578b\u7684\u5b57\u6bb5\uff0c\u5982\uff1a\nCREATE TABLE on_hand (\n item inventory_item,\n count integer\n);\n\u6700\u540e\u9700\u8981\u6307\u51fa\u7684\u662f\uff0c\u5728\u521b\u5efa\u8868\u7684\u65f6\u5019\uff0cPostgreSQL\u4e5f\u4f1a\u81ea\u52a8\u521b\u5efa\u4e00\u4e2a\u4e0e\u8be5\u8868\u5bf9\u5e94\u7684\u590d\u5408\u7c7b\u578b\uff0c\u540d\u5b57\u4e0e\u8868\u5b57\u76f8\u540c\uff0c\u5373\u8868\u793a\u8be5\u8868\u7684\u590d\u5408\u7c7b\u578b\u3002\n\n2. \u590d\u5408\u7c7b\u578b\u503c\u8f93\u5165\uff1a\n\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528\u6587\u672c\u5e38\u91cf\u7684\u65b9\u5f0f\u8868\u793a\u590d\u5408\u7c7b\u578b\u503c\uff0c\u5373\u5728\u5706\u62ec\u53f7\u91cc\u5305\u56f4\u5b57\u6bb5\u503c\u5e76\u4e14\u7528\u9017\u53f7\u5206\u9694\u5b83\u4eec\u3002\u4f60\u4e5f\u53ef\u4ee5\u5c06\u4efb\u4f55\u5b57\u6bb5\u503c\u7528\u53cc\u5f15\u53f7\u62ec\u8d77\uff0c\u5982\u679c\u503c\u672c\u8eab\u5305\u542b\u9017\u53f7\u6216\u8005\u5706\u62ec\u53f7\uff0c\u90a3\u4e48\u5c31\u7528\u53cc\u5f15\u53f7\u62ec\u8d77\uff0c\u5bf9\u4e8e\u4e0a\u9762\u7684inventory_item\u590d\u5408\u7c7b\u578b\u7684\u8f93\u5165\u5982\u4e0b\uff1a\n'(\"fuzzy dice\",42,1.99)'\n\u5982\u679c\u5e0c\u671b\u7c7b\u578b\u4e2d\u7684\u67d0\u4e2a\u5b57\u6bb5\u4e3aNULL\uff0c\u53ea\u9700\u5728\u5176\u5bf9\u5e94\u7684\u4f4d\u7f6e\u4e0d\u4e88\u8f93\u5165\u5373\u53ef\uff0c\u5982\u4e0b\u9762\u7684\u8f93\u5165\u4e2dprice\u5b57\u6bb5\u7684\u503c\u4e3aNULL\uff0c\n'(\"fuzzy dice\",42,)'\n\u5982\u679c\u53ea\u662f\u9700\u8981\u4e00\u4e2a\u7a7a\u5b57\u4e32\uff0c\u800c\u975eNULL\uff0c\u5199\u4e00\u5bf9\u53cc\u5f15\u53f7\uff0c\u5982\uff1a\n'(\"\",42,)'\n\u5728\u66f4\u591a\u7684\u573a\u5408\u4e2dPostgreSQL\u63a8\u8350\u4f7f\u7528ROW\u8868\u8fbe\u5f0f\u6765\u6784\u5efa\u590d\u5408\u7c7b\u578b\u503c\uff0c\u4f7f\u7528\u8be5\u79cd\u65b9\u5f0f\u76f8\u5bf9\u7b80\u5355\uff0c\u65e0\u9700\u8003\u8651\u66f4\u591a\u6807\u8bc6\u5b57\u7b26\u95ee\u9898\uff0c\u5982\uff1a\nROW('fuzzy dice', 42, 1.99)\nROW('', 42, NULL)\n\u6ce8\uff1a\u5bf9\u4e8eROW\u8868\u8fbe\u5f0f\uff0c\u5982\u679c\u91cc\u9762\u7684\u5b57\u6bb5\u6570\u91cf\u8d85\u8fc71\u4e2a\uff0c\u90a3\u4e48\u5173\u952e\u5b57ROW\u5c31\u53ef\u4ee5\u7701\u7565\uff0c\u56e0\u6b64\u4ee5\u4e0a\u5f62\u5f0f\u53ef\u4ee5\u7b80\u5316\u4e3a\uff1a\n('fuzzy dice', 42, 1.99)\n('', 42, NULL)\n\n3. \u8bbf\u95ee\u590d\u5408\u7c7b\u578b\uff1a\n\u8bbf\u95ee\u590d\u5408\u7c7b\u578b\u4e2d\u7684\u5b57\u6bb5\u548c\u8bbf\u95ee\u6570\u636e\u8868\u4e2d\u7684\u5b57\u6bb5\u5728\u5f62\u5f0f\u4e0a\u6781\u4e3a\u76f8\u4f3c\uff0c\u53ea\u662f\u4e3a\u4e86\u5bf9\u4e8c\u8005\u52a0\u4ee5\u533a\u5206\uff0cPostgreSQL\u8bbe\u5b9a\u5728\u8bbf\u95ee\u590d\u5408\u7c7b\u578b\u4e2d\u7684\u5b57\u6bb5\u65f6\uff0c\u7c7b\u578b\u90e8\u5206\u9700\u8981\u7528\u5706\u62ec\u53f7\u62ec\u8d77\uff0c\u4ee5\u907f\u514d\u6df7\u6dc6\uff0c\u5982\uff1a\nSELECT (item).name FROM on_hand WHERE (item).price > 9.99;\n\u5982\u679c\u5728\u67e5\u8be2\u4e2d\u4e5f\u9700\u8981\u7528\u5230\u8868\u540d\uff0c\u90a3\u4e48\u8868\u540d\u548c\u7c7b\u578b\u540d\u90fd\u9700\u8981\u88ab\u5706\u62ec\u53f7\u62ec\u8d77\uff0c\u5982\uff1a\nSELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;\n\n4. \u4fee\u6539\u590d\u5408\u7c7b\u578b\uff1a\n\u89c1\u5982\u4e0b\u51e0\u4e2a\u793a\u4f8b\uff1a\n--\u76f4\u63a5\u63d2\u5165\u590d\u5408\u7c7b\u578b\u7684\u6570\u636e\uff0c\u8fd9\u91cc\u662f\u901a\u8fc7ROW\u8868\u8fbe\u5f0f\u6765\u5b8c\u6210\u7684\u3002\nINSERT INTO on_hand(item) VALUES(ROW(\"fuzzy dice\",42,1.99));\n--\u5728\u66f4\u65b0\u64cd\u4f5c\u4e2d\uff0c\u4e5f\u662f\u53ef\u4ee5\u901a\u8fc7ROW\u8868\u8fbe\u5f0f\u6765\u5b8c\u6210\u3002\nUPDATE on_hand SET item = ROW(\"fuzzy dice\",42,1.99) WHERE count = 0;\n--\u5728\u66f4\u65b0\u590d\u5408\u7c7b\u578b\u4e2d\u7684\u4e00\u4e2a\u5b57\u6bb5\u65f6\uff0c\u6211\u4eec\u4e0d\u80fd\u5728SET\u540e\u9762\u51fa\u73b0\u7684\u5b57\u6bb5\u540d\u5468\u56f4\u52a0\u5706\u62ec\u53f7\uff0c\n--\u4f46\u662f\u5728\u7b49\u53f7\u53f3\u8fb9\u7684\u8868\u8fbe\u5f0f\u91cc\u5f15\u7528\u540c\u4e00\u4e2a\u5b57\u6bb5\u65f6\u5374\u9700\u8981\u5706\u62ec\u53f7\u3002\nUPDATE on_hand SET item.price = (item).price + 1 WHERE count = 0;\n--\u53ef\u4ee5\u5728\u63d2\u5165\u4e2d\uff0c\u76f4\u63a5\u63d2\u5165\u590d\u5408\u7c7b\u578b\u4e2d\u5b57\u6bb5\u3002\nINSERT INTO on_hand (item.supplier_id, item.price) VALUES(100, 2.2);\n\n\u8be5\u7bc7\u535a\u5ba2\u662f\u5bf9PostgreSQL\u5b98\u65b9\u6587\u6863\u4e2d\u201c\u6570\u636e\u7c7b\u578b\u201d\u7ae0\u8282\u7684\u7b80\u5355\u5f52\u7eb3\uff0c\u8fd9\u91cc\u4e4b\u6240\u4ee5\u7528\u4e00\u7bc7\u72ec\u7acb\u7684\u535a\u5ba2\u6765\u4e13\u95e8\u4ecb\u7ecd\uff0c\u4e0d\u4ec5\u662f\u4e3a\u4e86\u7cfb\u7edf\u5b66\u4e60\uff0c\u4e5f\u4fbf\u4e8e\u4eca\u540e\u9700\u8981\u65f6\u7684\u5feb\u901f\u67e5\u9605\u3002<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"\u4e00\u3001\u6570\u503c\u7c7b\u578b\uff1a \u4e0b\u9762\u662fPostgreSQL\u6240\u652f\u6301\u7684\u6570\u503c\u7c7b\u578b\u7684\u5217\u8868\u548c\u7b80\u5355\u8bf4\u660e\uff1a \u540d\u5b57 \u5b58\u50a8\u7a7a\u95f4 \u63cf\u8ff0 \u8303\u56f4 smallint 2 \u5b57\u8282 \u5c0f\u8303\u56f4\u6574\u6570 -32768 \u5230 +32767 integer 4 \u5b57\u8282 \u5e38\u7528\u7684\u6574\u6570 -2147483648 \u5230 +2147483647 bigint 8 \u5b57\u8282 \u5927\u8303\u56f4\u7684\u6574\u6570 -9223372036854775808 \u5230 9223372036854775807 decimal \u53d8\u957f \u7528\u6237\u58f0\u660e\u7cbe\u5ea6\uff0c\u7cbe\u786e \u65e0\u9650\u5236 numeric \u53d8\u957f \u7528\u6237\u58f0\u660e\u7cbe\u5ea6\uff0c\u7cbe\u786e \u65e0\u9650\u5236 real 4 \u5b57\u8282 \u53d8\u7cbe\u5ea6\uff0c\u4e0d\u7cbe\u786e 6 \u4f4d\u5341\u8fdb\u5236\u6570\u5b57\u7cbe\u5ea6 double 8 \u5b57\u8282 \u53d8\u7cbe\u5ea6\uff0c\u4e0d\u7cbe\u786e 15 \u4f4d\u5341\u8fdb\u5236\u6570\u5b57\u7cbe\u5ea6 serial 4 \u5b57\u8282 \u81ea\u589e\u6574\u6570 1 \u5230 +2147483647 bigserial 8 \u5b57\u8282 \u5927\u8303\u56f4\u7684\u81ea\u589e\u6574\u6570 1 \u5230 9223372036854775807 \u6574\u6570\u7c7b\u578b\uff1a \u7c7b\u578bsmallint\u3001integer\u548cbigint\u5b58\u50a8\u5404\u79cd\u8303\u56f4\u7684\u5168\u90e8\u662f\u6570\u5b57\u7684\u6570\uff0c\u4e5f\u5c31\u662f\u6ca1\u6709\u5c0f\u6570\u90e8\u5206\u7684\u6570\u5b57\u3002\u8bd5\u56fe\u5b58\u50a8\u8d85\u51fa\u8303\u56f4\u4ee5\u5916\u7684\u6570\u503c\u5c06\u5bfc\u81f4\u4e00\u4e2a\u9519\u8bef\u3002\u5e38\u7528\u7684\u7c7b\u578b\u662finteger\uff0c\u56e0\u4e3a\u5b83\u63d0\u4f9b\u4e86\u5728\u8303\u56f4\u3001\u5b58\u50a8\u7a7a\u95f4\u548c\u6027\u80fd\u4e4b\u95f4\u7684\u6700\u4f73\u5e73\u8861\u3002\u4e00\u822c\u53ea\u6709\u5728\u78c1\u76d8\u7a7a\u95f4\u7d27\u5f20\u7684\u65f6\u5019\u624d\u4f7f\u7528smallint\u3002\u800c\u53ea\u6709\u5728integer\u7684\u8303\u56f4\u4e0d\u591f\u7684\u65f6\u5019\u624d\u4f7f\u7528bigint\uff0c\u56e0\u4e3a\u524d\u8005(integer)\u7edd\u5bf9\u5feb\u5f97\u591a\u3002 \u4efb\u610f\u7cbe\u5ea6\u6570\u503c\uff1a \u7c7b\u578bnumeric\u53ef\u4ee5\u5b58\u50a8\u6700\u591a1000\u4f4d\u7cbe\u5ea6\u7684\u6570\u5b57\u5e76\u4e14\u51c6\u786e\u5730\u8fdb\u884c\u8ba1\u7b97\u3002\u56e0\u6b64\u975e\u5e38\u9002\u5408\u7528\u4e8e\u8d27\u5e01\u91d1\u989d\u548c\u5176\u5b83\u8981\u6c42\u8ba1\u7b97\u51c6\u786e\u7684\u6570\u91cf\u3002\u4e0d\u8fc7\uff0cnumeric\u7c7b\u578b\u4e0a\u7684\u7b97\u672f\u8fd0\u7b97\u6bd4\u6574\u6570\u7c7b\u578b\u6216\u8005\u6d6e\u70b9\u6570\u7c7b\u578b\u8981\u6162\u5f88\u591a\u3002 numeric\u5b57\u6bb5\u7684\u6700\u5927\u7cbe\u5ea6\u548c\u6700\u5927\u6bd4\u4f8b\u90fd\u662f\u53ef\u4ee5\u914d\u7f6e\u7684\u3002\u8981\u58f0\u660e\u4e00\u4e2a\u7c7b\u578b\u4e3anumeric\u7684\u5b57\u6bb5\uff0c\u4f60\u53ef\u4ee5\u7528\u4e0b\u9762\u7684\u8bed\u6cd5\uff1a NUMERIC(precision,scale) \u6bd4\u5982\u6570\u5b5723.5141\u7684\u7cbe\u5ea6\u4e3a6\uff0c\u800c\u523b\u5ea6\u4e3a4\u3002 \u5728\u76ee\u524d\u7684PostgreSQL\u7248\u672c\u4e2d\uff0cdecimal\u548cnumeric\u662f\u7b49\u6548\u7684\u3002 \u6d6e\u70b9\u6570\u7c7b\u578b\uff1a \u6570\u636e\u7c7b\u578breal\u548cdouble\u662f\u4e0d\u51c6\u786e\u7684\u3001\u727a\u7272\u7cbe\u5ea6\u7684\u6570\u5b57\u7c7b\u578b\u3002\u4e0d\u51c6\u786e\u610f\u5473\u7740\u4e00\u4e9b\u6570\u503c\u4e0d\u80fd\u51c6\u786e\u5730\u8f6c\u6362\u6210\u5185\u90e8\u683c\u5f0f\u5e76\u4e14\u662f\u4ee5\u8fd1\u4f3c\u7684\u5f62\u5f0f\u5b58\u50a8\u7684\uff0c\u56e0\u6b64\u5b58\u50a8\u540e\u518d\u628a\u6570\u636e\u6253\u5370\u51fa\u6765\u53ef\u80fd\u663e\u793a\u4e00\u4e9b\u7f3a\u5931\u3002 Serial(\u5e8f\u53f7)\u7c7b\u578b\uff1a serial\u548cbigserial\u7c7b\u578b\u4e0d\u662f\u771f\u6b63\u7684\u7c7b\u578b\uff0c\u53ea\u662f\u4e3a\u5728\u8868\u4e2d\u8bbe\u7f6e\u552f\u4e00\u6807\u8bc6\u505a\u7684\u6982\u5ff5\u4e0a\u7684\u4fbf\u5229\u3002 CREATE TABLE tablename ( colname SERIAL ); \u7b49\u4ef7\u4e8e CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename( colname integer DEFAULT nextval(‘tablename_colname_seq’) NOT NULL ); \u8fd9\u6837\uff0c\u6211\u4eec\u5c31\u521b\u5efa\u4e86\u4e00\u4e2a\u6574\u6570\u5b57\u6bb5\u5e76\u4e14\u628a\u5b83\u7684\u7f3a\u7701\u6570\u503c\u5b89\u6392\u4e3a\u4ece\u4e00\u4e2a\u5e8f\u5217\u53d1\u751f\u5668\u53d6\u503c\u3002\u5e94\u7528\u4e86\u4e00\u4e2aNOT NULL\u7ea6\u675f\u4ee5\u786e\u4fdd\u7a7a\u503c\u4e0d\u4f1a\u88ab\u63d2\u5165\u3002\u5728\u5927\u591a\u6570\u60c5\u51b5\u4e0b\u4f60\u53ef\u80fd\u8fd8\u5e0c\u671b\u9644\u52a0\u4e00\u4e2aUNIQUE\u6216\u8005PRIMARY KEY\u7ea6\u675f\u907f\u514d\u610f\u5916\u5730\u63d2\u5165\u91cd\u590d\u7684\u6570\u503c\uff0c\u4f46\u8fd9\u4e2a\u4e0d\u662f\u81ea\u52a8\u53d1\u751f\u7684\u3002\u56e0\u6b64\uff0c\u5982\u679c\u4f60\u5e0c\u671b\u4e00\u4e2a\u5e8f\u5217\u5b57\u6bb5\u6709\u4e00\u4e2a\u552f\u4e00\u7ea6\u675f\u6216\u8005\u4e00\u4e2a\u4e3b\u952e\uff0c\u90a3\u4e48\u4f60\u73b0\u5728\u5fc5\u987b\u58f0\u660e\uff0c\u5c31\u50cf\u5176\u5b83\u6570\u636e\u7c7b\u578b\u4e00\u6837\u3002 \u8fd8\u9700\u8981\u53e6\u5916\u8bf4\u660e\u7684\u662f\uff0c\u4e00\u4e2aserial\u7c7b\u578b\u521b\u5efa\u7684\u5e8f\u5217\u5728\u5176\u6240\u5c5e\u5b57\u6bb5\u88ab\u5220\u9664\u65f6\uff0c\u8be5\u5e8f\u5217\u4e5f\u5c06\u88ab\u81ea\u52a8\u5220\u9664\uff0c\u4f46\u662f\u5176\u5b83\u60c5\u51b5\u4e0b\u662f\u4e0d\u4f1a\u88ab\u5220\u9664\u7684\u3002\u56e0\u6b64\uff0c\u5982\u679c\u4f60\u60f3\u7528\u540c\u4e00\u4e2a\u5e8f\u5217\u53d1\u751f\u5668\u540c\u65f6\u7ed9\u51e0\u4e2a\u5b57\u6bb5\u63d0\u4f9b\u6570\u636e\uff0c\u90a3\u4e48\u5c31\u5e94\u8be5\u4ee5\u72ec\u7acb\u5bf9\u8c61\u7684\u65b9\u5f0f\u521b\u5efa\u8be5\u5e8f\u5217\u53d1\u751f\u5668\u3002 \u4e8c\u3001\u5b57\u7b26\u7c7b\u578b\uff1a \u4e0b\u9762\u662fPostgreSQL\u6240\u652f\u6301\u7684\u5b57\u7b26\u7c7b\u578b\u7684\u5217\u8868\u548c\u7b80\u5355\u8bf4\u660e\uff1a \u540d\u5b57 \u63cf\u8ff0 varchar(n) \u53d8\u957f\uff0c\u6709\u957f\u5ea6\u9650\u5236 char(n) \u5b9a\u957f,\u4e0d\u8db3\u8865\u7a7a\u767d text \u53d8\u957f\uff0c\u65e0\u957f\u5ea6\u9650\u5236 SQL \u5b9a\u4e49\u4e86\u4e24\u79cd\u57fa\u672c\u7684\u5b57\u7b26\u7c7b\u578b\uff0cvarchar(n)\u548cchar(n)\uff0c\u8fd9\u91cc\u7684n\u662f\u4e00\u4e2a\u6b63\u6574\u6570\u3002\u4e24\u79cd\u7c7b\u578b\u90fd\u53ef\u4ee5\u5b58\u50a8\u6700\u591an\u4e2a\u5b57\u7b26\u957f\u7684\u5b57\u4e32\uff0c\u8bd5\u56fe\u5b58\u50a8\u66f4\u957f\u7684\u5b57\u4e32\u5230\u8fd9\u4e9b\u7c7b\u578b\u7684\u5b57\u6bb5\u91cc\u4f1a\u4ea7\u751f\u4e00\u4e2a\u9519\u8bef\uff0c\u9664\u975e\u8d85\u51fa\u957f\u5ea6\u7684\u5b57\u7b26\u90fd\u662f\u7a7a\u767d\uff0c\u8fd9\u79cd\u60c5\u51b5\u4e0b\u8be5\u5b57\u4e32\u5c06\u88ab\u622a\u65ad\u4e3a\u6700\u5927\u957f\u5ea6\u3002\u5982\u679c\u6ca1\u6709\u957f\u5ea6\u58f0\u660e\uff0cchar\u7b49\u4e8echar(1)\uff0c\u800cvarchar\u5219\u53ef\u4ee5\u63a5\u53d7\u4efb\u4f55\u957f\u5ea6\u7684\u5b57\u4e32\u3002 MyTest=> CREATE TABLE testtable(first_col varchar(2)); CREATE TABLE MyTest=> INSERT INTO testtable VALUES(‘333’); –\u63d2\u5165\u5b57\u7b26\u4e32\u7684\u957f\u5ea6\uff0c\u8d85\u8fc7\u5176\u5b57\u6bb5\u5b9a\u4e49\u7684\u957f\u5ea6\uff0c\u56e0\u6b64\u62a5\u9519\u3002 ERROR: value too long for type character varying(2) –\u63d2\u5165\u5b57\u7b26\u4e32\u4e2d\uff0c\u8d85\u51fa\u5b57\u6bb5\u5b9a\u4e49\u957f\u5ea6\u7684\u90e8\u5206\u662f\u7a7a\u683c\uff0c\u56e0\u6b64\u53ef\u4ee5\u63d2\u5165\uff0c\u4f46\u662f\u7a7a\u767d\u7b26\u88ab\u622a\u65ad\u3002 MyTest=> INSERT INTO testtable VA…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","footnotes":""},"categories":[89],"tags":[],"class_list":["post-1637","post","type-post","status-publish","format-standard","hentry","category-postgres-db"],"_links":{"self":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1637","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1637"}],"version-history":[{"count":1,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1637\/revisions"}],"predecessor-version":[{"id":1638,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=\/wp\/v2\/posts\/1637\/revisions\/1638"}],"wp:attachment":[{"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1637"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1637"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oneai.eu.org\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1637"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}