PostgreSQL 常è§é®é¢ï¼FAQï¼ æè¿æ´æ°ï¼2007 å¹´ 1 æ 30 æ¥ ææäº 17:37:01 EST ä¸æçæè¿æ´æ°ï¼2007 å¹´ 2 æ 1 æ¥ ææå 12:55:04 CST ç®åç»´æ¤äººåï¼Bruce Momjian (pgman@candle.pha.pa.us) ä¸æçç»´æ¤äººåï¼Daojing.Zhouï¼doudou586@gmail.comï¼ æ¬ææ¡£çææ°çæ¬å¯ä»¥å¨ http://www.postgresql.org/files/documentation/faqs/FAQ.htmlæ¥çã ä¸æä½ç³»ç»å¹³å°ç¸å³çé®é¢å¯å¨http://www.postgresql.org/docs/faq/éæ¾å°ç­ æ¡ã _________________________________________________________________ 常è§é®é¢ 1.1)PostgreSQL æ¯ä»ä¹ï¼è¯¥æä¹åé³ï¼ 1.2)è°æ§å¶å管çPostgreSQL ï¼ 1.3)PostgreSQLççææ¯ä»ä¹ï¼ 1.4)PostgreSQLå¯ä»¥è¿è¡å¨åªäºæä½ç³»ç»å¹³å°ä¸ï¼ 1.5)æä»åªéè½å¾å°PostgreSQLï¼ 1.6)ææ°ççPostgreSQL æ¯ä»ä¹ï¼ 1.7)æä»åªéè½å¾å°å¯¹PostgreSQL çæ¯æï¼ 1.8)æå¦ä½æ交ä¸ä¸ªBUGæ¥åï¼ 1.9)æå¦ä½äºè§£å·²ç¥ç BUG ææ缺çåè½ï¼ 1.10)è½å¤è·åçææ°ææ¡£æåªäºï¼ 1.11)æåºè¯¥ææ ·å¦ä¹ SQL ï¼ 1.12)å¦ä½æ交补ä¸ææ¯å å¥å¼åéä¼ï¼ 1.13)PostgreSQL åå¶ä»æ°æ®åºç³»ç»æ¯èµ·æ¥å¦ä½ï¼ 1.14)PostgreSQL å¯ä»¥å¤çæè¿å个å½å®¶å¤æ¶å¶çåååï¼ ç¨æ·å®¢æ·ç«¯é®é¢ 2.1)æ们å¯ä»¥ç¨ä»ä¹è¯è¨åPostgreSQL æ交éï¼ 2.2)æä»ä¹å·¥å·å¯ä»¥æPostgreSQL ç¨äº Web 页é¢ï¼ 2.3)PostgreSQL æ¥æå¾å½¢ç¨æ·çé¢åï¼ ç³»ç»ç®¡çé®é¢ 3.1)æææ ·æè½æPostgreSQL è£å¨ /usr/local/pgsql 以å¤çå°æ¹ï¼ 3.2)æå¦ä½æ§å¶æ¥èªå¶ä»çµèçè¿æ¥ï¼ 3.3)æææ ·è°æ´æ°æ®åºæå¡å¨ä»¥è·å¾æ´å¥½çæ§è½ï¼ 3.4)PostgreSQL éå¯ä»¥è·å¾ä»ä¹æ ·çè°è¯ç¹æ§ï¼ 3.5)为ä»ä¹å¨è¯å¾è¿æ¥ç»å½æ¶æ¶å°âSorry, too many clientsâ æ¶æ¯ï¼ 3.6)PostgreSQLçå级è¿ç¨æåªäºåå®¹ï¼ 3.7)(使ç¨PostgreSQL)æéè¦ä½¿ç¨ä»ä¹è®¡ç®æºç¡¬ä»¶ ï¼ æä½é®é¢ 4.1) å¦ä½åªéæ©ä¸ä¸ªæ¥è¯¢ç»æç头å è¡ï¼ææ¯éæºçä¸è¡ï¼ 4.2) å¦ä½æ¥ç表ãç´¢å¼ãæ°æ®åºä»¥åç¨æ·çå®ä¹ï¼å¦ä½æ¥çpsqléç¨å°çæ¥è¯¢æ令并æ¾ç ¤ºå®ä»¬ï¼ 4.3) å¦ä½æ´æ¹ä¸ä¸ªå段çæ°æ®ç±»åï¼ 4.4) åæ¡è®°å½ï¼å个表ï¼ä¸ä¸ªæ°æ®åºçæ大éå¶æ¯å¤å°ï¼ 4.5) åå¨ä¸ä¸ªå¸åçææ¬æ件éçæ°æ®éè¦å¤å°ç£ç空é´ï¼ 4.6) 为ä»ä¹æçæ¥è¯¢å¾æ¢ï¼ä¸ºä»ä¹è¿äºæ¥è¯¢æ²¡æå©ç¨ç´¢å¼ï¼ 4.7) æå¦ä½æè½çå°æ¥è¯¢ä¼åå¨æ¯ææ ·è¯ä¼°å¤çæçæ¥è¯¢çï¼ 4.8) æææ ·åæ£å表达å¼æç´¢å大å°åæ å³çæ­ £å表达å¼æ¥æ¾ï¼ææ ·å©ç¨ç´¢å¼è¿è¡å¤§å°åæ å³æ¥æ¾ï¼ 4.9) å¨ä¸ä¸ªæ¥è¯¢éï¼æææ ·æ£æµä¸ä¸ªå段æ¯å¦ä¸º NULLï¼æå¦ä½æè½åç¡®æåºèä¸è®ºæå段æ¯å¦å«NULLå¼ï¼ 4.10) åç§å符类åä¹é´æä»ä¹ä¸åï¼ 4.11.1) æææ ·å建ä¸ä¸ªåºåå·åææ¯èªå¨éå¢çåæ®µï¼ 4.11.2) æå¦ä½è·å¾ä¸ä¸ªæå¥çåºåå·çå¼ï¼ 4.11.3) åæ¶ä½¿ç¨ currval() ä¼å¯¼è´åå¶ä»ç¨æ·çå²çªæåµåï¼ 4.11.4) 为ä»ä¹ä¸å¨äºå¡å¼å¸¸ä¸æ¢åéç¨åºåå·å¢ï¼ä¸ºä»ä¹å¨åºåå·å段çåå¼ä¸­ åå¨é´æå¢ï¼ 4.12) ä»ä¹æ¯ OIDï¼ä»ä¹æ¯ CTID ï¼ 4.13) 为ä»ä¹ææ¶å°é误信æ¯âERROR: Memory exhausted in AllocSetAlloc()âï¼ 4.14) æå¦ä½æè½ç¥éæè¿è¡ç PostgreSQL ççæ¬ï¼ 4.15) æå¦ä½å建ä¸ä¸ªç¼ºçå¼æ¯å½åæ¶é´çåæ®µï¼ 4.16) å¦ä½æ§è¡å¤è¿æ¥ï¼outer joinï¼æ¥è¯¢ï¼ 4.17) å¦ä½æ§è¡æ¶åå¤ä¸ªæ°æ®åºçæ¥è¯¢ï¼ 4.18) å¦ä½è®©å½æ°è¿åå¤è¡æå¤åæ°æ®ï¼ 4.19) 为ä»ä¹æå¨ä½¿ç¨PL/PgSQLå½æ°åå临æ¶è¡¨æ¶ä¼æ¶å°é误信æ¯ârelation with OID ##### does not existâï¼ 4.20) ç®åæåªäºæ°æ®å¤å¶æ¹æ¡å¯ç¨ï¼ 4.21) 为ä½æ¥è¯¢ç»ææ¾ç¤ºç表åæååä¸æçæ¥è¯¢è¯å¥ä¸­ çä¸åï¼ä¸ºä½å¤§åç¶æä¸è½ä¿çï¼ _________________________________________________________________ 常è§é®é¢ 1.1)PostgreSQL æ¯ä»ä¹ï¼è¯¥æä¹åé³ï¼ PostgreSQL è¯»ä½ Post-Gres-Q-Lï¼ææ¶åä¹ç®ç§°ä¸ºPostgres ãæ³å¬ä¸ä¸å¶åé³ç人åå¯ä»è¿éä¸è½½å£°é³æä»¶ï¼ MP3 æ ¼å¼ ã PostgreSQL æ¯é¢åç®æ çå³ç³»æ°æ®åºç³»ç»ï¼å®å·æä¼ ç»åä¸æ°æ®åºç³»ç»çææåè½ï¼åæ¶åå«æå°å ¨ä¸ä¸ä»£ DBMS ç³»ç»ç使ç¨çå¢å¼ºç¹æ§ãPostgreSQL æ¯èªç±åè´¹çï¼å¹¶ä¸æææºä»£ç é½å¯ä»¥è·å¾ã PostgreSQL çå¼åéä¼ä¸»è¦ä¸ºå¿æ¿èï¼ä»ä»¬éå¸ä¸çåå°å¹¶éè¿äºèç½è¿è¡èç³»ï¼è¿æ¯ä¸ä¸ªç¤¾å ºå¼å项ç®ï¼å®ä¸è¢«ä»»ä½å¬å¸æ§å¶ã å¦æ³å å¥å¼åéä¼ï¼è¯·åè§å¼å人å常è§é®é¢ï¼FAQï¼ http://www.postgresql.org/files/documentation/faqs/FAQ_DEV.html 1.2) è°æ§å¶PostgreSQL ï¼ å¦æä½ å¨å¯»æ¾PostgreSQLçæé¨äººï¼ææ¯ä»ä¹ä¸­ 央å§åä¼ï¼ææ¯ä»ä¹æå±å¬å¸ï¼ä½ åªè½æ¾å¼äº---å 为ä¸ä¸ªä¹ä¸å­ å¨ï¼ä½æ们çç¡®æä¸ä¸ª æ ¸å¿å§åä¼åCVS管çç»ï¼ä½è¿äºå·¥ä½ç»ç设ç«ä¸»è¦æ¯ä¸ºäºè¿è¡ç®¡çå·¥ä½èä¸æ ¯å¯¹PostgreSQLè¿è¡ç¬å å¼æ§å¶ï¼PostgreSQL项ç®æ¯ç±ä»»ä½äººå å¯åå çå¼å人å社åºåææç¨æ·æ§å¶çï¼ä½ æéè¦åçå°±æ¯è®¢éé®ä»¶å表ï¼åä¸è®¨è®º å³å¯ï¼è¦åä¸PostgreSQLçå¼åè¯¦è§ å¼å人å常é®é¢ (Developer's FAQ) è·åä¿¡æ¯ï¼ã 1.3)PostgreSQLççææ¯ä»ä¹? PostgreSQLçåå¸éµä»ç»å¸çBSDçæãå®å许ç¨æ·ä¸éç®çå°ä½¿ç¨PostgreSQLï¼çè³ä½ å¯ä»¥éå®PostgreSQLèä¸å«æºä»£ç ä¹å¯ä»¥ï¼å¯ä¸çéå¶å°±æ¯ä½ ä¸è½å 软件èªè º«é®é¢èåæ们追è¯æ³å¾è´£ä»»ï¼å¦å¤å°±æ¯è¦æ±ææç软件æ·è´ä¸­ é¡»åæ¬ä»¥ä¸çæ声æãä¸é¢å°±æ¯æ们æ使ç¨çBSDçæ声æåå®¹ï¼ PostgreSQLæ°æ®åºç®¡çç³»ç» é¨åçæï¼cï¼1996-2005ï¼PostgreSQL å¨çå¼åå°ç»ï¼é¨åçæï¼cï¼1994-1996 å å·å¤§å¦è£äº ï¼Portions copyright (c) 1996-2005,PostgreSQL Global Development Group Portions Copyright (c) 1994-6 Regents of the University of Californiaï¼ å许为任ä½ç®ç使ç¨ï¼æ·è´ï¼ä¿®æ¹åååè¿ä¸ªè½¯ä»¶åå®çææ¡£èä¸æ¶åä»»ä½è´¹ç ¨ï¼ 并ä¸æ é¡»ç¾ç½²å æ­ ¤è产ççè¯æï¼åææ¯ä¸é¢ççæ声æåæ¬æ®µä»¥åä¸é¢ä¸¤æ®µæååºç°å¨æææ·è´ä¸ã ï¼Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.ï¼ å¨ä»»ä½æåµä¸ï¼å å·å¤§å¦é½ä¸æ¿æå 使ç¨æ­ ¤è½¯ä»¶åå¶ææ¡£è导è´ç对任ä½å½äºäººçç´æ¥çï¼ é´æ¥çï¼ç¹æ®çï¼éå çæèç¸ä¼´èççæåï¼åæ¬å©çæ失ç责任ï¼å³ä½¿å å·å¤§å­ ¦å·²ç»å»ºè®®äºè¿äºæ失çå¯è½æ§æ¶ä¹æ¯å¦æ¤ã ï¼IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.ï¼ å å·å¤§å¦æç¡®æ¾å¼ä»»ä½ä¿è¯ï¼åæ¬ä½ä¸å±éäºæä¸ç¹å®ç¨éçåä¸åå©ççéå«ä¿è¯ã è¿éæä¾çè¿ä»½è½¯ä»¶æ¯åºäºâå½ä½æ¯âçåºç¡çï¼å èå å·å¤§å­ ¦æ²¡æ责任æä¾ç»´æ¤ï¼æ¯æï¼æ´æ°ï¼å¢å¼ºæèä¿®æ¹çæå¡ã ï¼THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.ï¼ 1.4)PostgreSQL å¯ä»¥è¿è¡å¨åªäºæä½ç³»ç»å¹³å°ä¸ï¼ ä¸è¬è¯´æ¥ï¼ä»»ä½ç°å¨å¯¹ UNIX å¼å®¹çæä½ç³»ç»ä¹ä¸é½è½è¿è¡PostgreSQL ãå¨å®è£æåéååºäºåå¸æ¶ç»è¿æç¡®æµè¯çå¹³å°ã PostgreSQlä¹å¯ä»¥ç´æ¥è¿è¡å¨åºäºå¾®è½¯Windows-NTçæä½ç³»ç»ï¼å¦Win2000 SP4ï¼WinXP å Win2003ï¼å·²å¶ä½å®æçå®è£åå¯ä» http://pgfoundry.org/projects/pginstallerä¸è½½ï¼åºäºMSDOSçWindowsæä½ç³ »ç» ï¼Win95ï¼Win98ï¼WinMeï¼éè¦éè¿Cygwin模æç¯å¢è¿è¡PostgreSQLã åæ¶ä¹æä¸ä¸ªä¸ºNovell Netware 6å¼åççæ¬å¯ä» http://forge.novell.comè·åï¼ä¸ºOS/2(eComStation)å¼åççæ¬å¯ä» http://hobbes.nmsu.edu/cgi-bin/h-search?sh=1&button=Search&key=postgre SQL&stype=all&sort=type&dir=%2F ä¸è½½ã 1.5) æä»åªéè½å¾å°PostgreSQLï¼ éè¿æµè§å¨å¯ä»http://www.postgresql.org/ftp/ä¸è½½ï¼ä¹å¯éè¿FTPï¼ä» ftp://ftp.PostgreSQL.org/pub/ç«ç¹ä¸è½½ã 1.6) ææ°ççPostgreSQLæ¯ä»ä¹ï¼ PostgreSQL ææ°ççæ¬æ¯çæ¬ 8.2.1 ã æ们计åæ¯å¹´åå¸ä¸ä¸ªä¸»è¦å级çæ¬ï¼æ¯å 个æåå¸ä¸ä¸ªå°çæ¬ã 1.7) æä»åªéè½å¾å°å¯¹PostgreSQLçæ¯æï¼ PostgreSQL社åºéè¿é®ä»¶å表为å¶å¤§å¤æ°ç¨æ·æä¾å¸®å©ï¼è®¢éé®ä»¶å表ç主 ç«ç¹æ¯ http://www.postgresql.org/community/lists/ï¼ä¸è¬æåµä¸ï¼åå å¥General æ Bugé®ä»¶å表æ¯ä¸ä¸ªè¾å¥½çå¼å§ã 主è¦çIRCé¢éæ¯å¨FreeNode(irc.freenode.net)ç#postgresqlï¼ä¸ºäºè¿ä¸æ­ ¤é¢éï¼å¯ä»¥ä½¿ç¨UNIXç¨åºircï¼å¶æä»¤æ ¼å¼ï¼ irc -c '#postgresql' "$USER" irc.freenode.net ï¼æè使ç¨å¶ä»IRC客æ·ç«¯ç¨åºãå¨æ¤ç½ç»ä¸è¿å­ å¨ä¸ä¸ªPostgreSQLç西ççé¢é(#postgersql-es)åæ³è¯é¢é (#postgresql-fr)ãåæ ·å°ï¼å¨EFNETä¸ä¹æä¸ä¸ªPostgreSQLç交æµé¢éã å¯æä¾åä¸æ¯æçå¬å¸å表å¯å¨http://techdocs.postgresql.org/companies.phpæµ è§ã 1.8) æå¦ä½æ交ä¸ä¸ªBUGæ¥åï¼ å¯è®¿é® http://www.postgresql.org/support/submitbugï¼å¡«åBugä¸æ¥è¡¨æ ¼å³å¯ï¼åæ ·ä¹å¯è®¿é®ftpç«ç¹ftp://ftp.PostgreSQL.org/pub/ æ£æ¥ææ æ´æ°çPostgreSQLçæ¬æè¡¥ä¸ã éè¿ä½¿ç¨Bugæäº¤è¡¨æ ¼ææ¯åå¾PostgreSQLé®ä»¶å表çBugé常ä¼æ以ä¸ä¹ä¸åå¤ï ¼ * ææ交å容ä¸æ¯ä¸ä¸ªBugåå¶ä¸æ¯Bugçåå ã * ææ交å容æ¯ä¸ä¸ªå·²ç¥çBug并ä¸å·²ç»å å¥TODOå¾å¤çä»»å¡å表ã * ææ交çBugå·²å¨å½åçæ¬ä¸è¢«ä¿®æ£ã * ææ交çBug已修æ£ä½å°æªå°è¡¥ä¸å å¥ç°å¨çåå¸è½¯ä»¶åã * 请æ±æ交èæä¾æ´è¯¦ç»çä¿¡æ¯ï¼ + æä½ç³»ç» + PostgreSQLçæ¬ + å¯éç°Bugçæµè¯æ¡ä¾ + è°è¯ä¿¡æ¯ + è°è¯è·è¸ªè¾åº * ææ交å容æ¯ä¸ä¸ªæ°Bugï¼å°æ§è¡ä»¥ä¸å·¥ä½ï¼ + å建ä¸ä¸ªæ°è¡¥ä¸å¹¶å°å¶å å¥ä¸ä¸ä¸ªä¸»è¦çæ¬ææ¯å°çæ¹è¿çæ¬ä¸ã + æ¤Bugææ¶ä¸è½ä¿®æ£ï¼å°è¢«å è³TODOå¾å¤çä»»å¡å表ã 1.9) æå¦ä½äºè§£å·²ç¥ç BUG ææ缺çåè½ï¼ PostgreSQL æ¯æä¸ä¸ªæ©å±ç SQL:2003 çåéãåéæ们çTODO å表ï¼äºè§£å·²ç¥Bugå表ãæ缺çåè½åå°æ¥çå¼å计åã è¦æ±å¢å æ°åè½çç³è¯·é常ä¼æ¶å°ä»¥ä¸ä¹ä¸çåå¤ï¼ * 该åè½å·²å å¥TODOå¾å¤çä»»å¡å表ã * 该åè½ä¸æ¯å¿é¡»çï¼å ä¸ºï¼ + å®æ¯ç°æçä¸ç¬¦åSQLæ åçæåè½çéå¤ã + 该åè½æ§ä¼å¤§å¤§å¢å 代ç çå¤æç¨åºï¼è带æ¥ç好å¤æ¯å¾®ä¸è¶³éçã + 该åè½æ¯ä¸å®å¨ææ¯ä¸å¯é çã * 该åè½å°è¢«å å¥TODOå¾å¤çä»»å¡å表ã PostgreSQLä¸ä½¿ç¨Bugè·è¸ªç³»ç»ï¼å 为æ们åç°å¨é®ä»¶å表中 ç´æ¥åå¤ä»¥åä¿è¯TODOä»»å¡å表æ»æ¯å¤äºææ°ç¶æçæ¹å¼å·¥ä½æçä¼æ´é«ä¸äºãäºå®ä ¸ï¼Bugä¸ä¼å¨æ们ç软件ä¸åå¨å¾é¿æ¶é´ï¼ 对影åå¾å¤ç¨æ·çBugä¹æ»æ¯å¾å¿«ä¼è¢«ä¿®æ£ãå¯ä¸è½æ¾å°æææ¹è¿ãæé«åä¿®æ­ £çå°æ¹æ¯CVSçæ¥å¿ä¿¡æ¯ï¼å³ä½¿æ¯å¨è½¯ä»¶æ°çæ¬çåå¸ä¿¡æ¯ä¸­ ä¹ä¸ä¼ååºæ¯ä¸å¤ç软件æ´æ°ã 1.10) è½å¤è·åçææ°ææ¡£æåªäºï¼ PostgreSQLåå«å¤§éçææ¡£ï¼ä¸»è¦æ详ç»çåèæåï¼æå页åä¸äºçæµè¯ä¾åãåè§ /doc ç®å½ï¼è¯æ³¨ï¼åºä¸º $PGHOME/docï¼ã ä½ è¿å¯ä»¥å¨çº¿æµè§PostgreSQLçæåï¼å¶ç½åæ¯ï¼http://www.PostgreSQL.org/d ocsã æ两æ¬å³äºPostgreSQLç书å¨çº¿æä¾ï¼å¨ http://www.PostgreSQL.org/docs/awbook.html å http://www.commandprompt.com/ppbook/ ã ä¹æ大éçPostgreSQL书ç±å¯ä¾è´­ ä¹°ï¼å¶ä¸æ为æµè¡çä¸æ¬æ¯ç±Korry Douglasç¼åçãå¨ http://techdocs.PostgreSQL.org/techdocs/bookreviews.phpä¸ ä¸æ大éæå³PostgreSQL书ç±çç®ä»ã å¨ http://techdocs.PostgreSQL.org/ä¸æ¶éäºæå³PostgreSQLç大éææ¯æç« ã 客æ·ç«¯çå½ä»¤è¡ç¨åºpsqlæä¸äºä»¥ \d å¼å¤´çå½ä»¤ï¼å¯æ¾ç¤ºå³äºç±»åï¼æä½ç¬¦ï¼å½æ°ï¼èåçä¿¡æ¯ï¼ä½¿ç¨ \? å¯ä»¥æ¾ç¤ºææå¯ç¨çå½ä»¤ã æ们ç web ç«ç¹åå«æ´å¤çææ¡£ã 1.11) æåºè¯¥ææ ·å¦ä¹ SQL ï¼ é¦åèèä¸è¿°æå°çä¸PostgreSQLç¸å³ç书ç±ï¼å¦å¤ä¸æ¬æ¯Teach Yourself SQL in 21 Days, Second Editionï¼å¶è¯¦ç»ä»ç»çç½åæ¯ http://members.tripod.com/er4ebus/sql/index.htmï¼ æ们ç许å¤ç¨æ·å欢The Practical SQL Handbookï¼ Bowman, Judith S. ç¼åï¼Addison-Wesleyå¬å¸åºçï¼å¶ä»çåå欢 The Complete Reference SQL, Groff ç¼åï¼McGraw-Hillå¬å¸åºçã å¨ä¸åç½åä¸ä¹æå¾å¥½çæç¨ï¼ä»ä»¬æ¯ * http://www.intermedia.net/support/sql/sqltut.shtm * http://sqlcourse.com. * http://www.w3schools.com/sql/default.asp * http://mysite.verizon.net/Graeme_Birchall/id1.html 1.12)å¦ä½æ交补ä¸ææ¯å å¥å¼åéä¼ï¼ è¯¦è§ å¼å人å常è§é®é¢ (Developer's FAQ) ã 1.13)PostgreSQLåå¶ä»æ°æ®åºç³»ç»æ¯èµ·æ¥å¦ä½ï¼ è¯ä»·è½¯ä»¶æ好å ç§æ¹æ³ï¼åè½ï¼æ§è½ï¼å¯é æ§ï¼æ¯æåä»·æ ¼ã åè½ PostgreSQL æ¥æ大ååç¨æ°æ®åºæå¤çåè½ï¼ä¾å¦ï¼äºå¡ï¼å­ æ¥è¯¢ï¼è§¦åå¨ï¼è§å¾ï¼å¤é®åèå®æ´æ§åå¤æçéå®çã æ们è¿æä¸äºå®ä»¬æ²¡æçç¹æ§ï¼å¦ç¨æ·å®ä¹ç±»åï¼ç»§æ¿ï¼è§ååå¤çæ¬å¹¶è ¡æ§å¶ä»¥åå°éçäºç¨çã æ§è½ PostgreSQLåå¶ä»åç¨åå¼æºçæ°æ®åºå·æ类似çæ§è½ã对æäºå¤çå®æ¯è¾å¿« ï¼å¯¹å¶ä»ä¸äºå¤çå®æ¯è¾æ¢ã ä¸å¶ä»æ°æ®åºç¸æ¯ï¼æ们çæ§è½ä¼å£éå¸¸å¨ +/- 10%ä¹é´ã å¯é æ§ æ们é½ç¥éæ°æ®åºå¿é¡»æ¯å¯é çï¼å¦åå®å°±ä¸ç¹ç¨é½æ²¡æãæ们åªååå°åå¸ ç»è¿è®¤çæµè¯çï¼ç¼ºé·æå°ç稳å®ä»£ç ãæ¯ä¸ªçæ¬è³å°æä¸ä¸ªæç beta æµè¯æ¶é´ï¼å¹¶ä¸æ们çåå¸åå²æ¾ç¤ºæ们å¯ä»¥æä¾ç¨³å®çï¼ç¢åºçï¼å¯ç¨ä ºç产使ç¨ççæ¬ãæ们ç¸ä¿¡å¨è¿æ¹é¢æ们ä¸å¶ä»çæ°æ®åºè½¯ä»¶æ¯ç¸å½çã æ¯æ æ们çé®ä»¶å表æä¾ä¸ä¸ªé常大çå¼å人ååç¨æ·çç»ä»¥å¸®å©è§£å³æç¢°å °çä»»ä½é®é¢ãæ们ä¸è½ä¿è¯æ»æ¯è½è§£å³é®é¢ï¼ç¸æ¯ä¹ä¸ï¼åç¨æ°æ®åºè½¯ 件ä¹å¹¶ä¸æ¯æ»è½å¤æä¾è§£å³æ¹æ³ã ç´æ¥ä¸å¼å人åï¼ç¨æ·ç¾¤ï¼æååæºç¨åºæ¥è§¦ä½¿PostgreSQLçæ¯ææ¯å¶ä»æ° æ®åºè¿è¦å¥½ãè¿æä¸äºåä¸æ§çå¨é¢ææ¯æ¯æï¼å¯ä»¥ç»æä¾ç»é£äºéè¦ç人ãï¼ åé1.7 å°èï¼ ä»·æ ¼ æ们对任ä½ç¨éé½åè´¹ï¼åæ¬åç¨åéåç¨ç®çã ä½ å¯ä»¥ä¸å éå¶å°åä½ ç产åéå å¥æ们ç代ç ï¼é¤äºé£äºæ们å¨ä¸é¢çç æ声æé声æç BSDçæä¹å¤çå容ã 1.14) PostgreSQLå¯ä»¥å¤çæè¿å个å½å®¶å¤æ¶å¶çååå? PostgreSQL 8.0ä¹åççæ¬æ¯ä½¿ç¨æä½ç³»ç»ä¸­ çæ¶åºæ°æ®åºæ¥å¤çå¤æ¶å¶çä¿¡æ¯ï¼èª8.0çå以åççæ¬PostgreSQLä¼èªèº«å«æææ°çæ ¶åºä¿¡æ¯ã _________________________________________________________________ ç¨æ·å®¢æ·ç«¯é®é¢ 2.1) æ们å¯ä»¥ç¨ä»ä¹è¯è¨åPostgreSQLæ交éï¼ PostgreSQL(缺çæåµ)åªå®è£æCåååµå¼Cçæ¥å£ï¼å¶ä»çæ¥å£é½æ¯ç¬ç«ç项ç®ï¼è½å¤ åå«ä¸è½½ï¼è¿äºæ¥å£é¡¹ç®ç¬ç«çå¥½å¤ æ¯ä»ä»¬å¯ä»¥æåèªçåå¸è®¡åååèªç¬ç«çå¼åç»ã ä¸äºç¼ç¨è¯­ è¨å¦PHPé½æ访é®PostgreSQLçæ¥å£ï¼PerlãTCLãPython以åå¾å¤å¶ä»è¯è¨çæ¥å£å¨ http://gborg.postgresql.orgç½ç«ä¸çDrivers/Interfaceså°èå¯æ¾å°ï¼ 并ä¸éè¿Internetå¾å®¹ææç´¢å°ã 2.2) æä»ä¹å·¥å·å¯ä»¥æPostgreSQLç¨äº Web 页é¢ï¼ ä¸ä¸ªä»ç»ä»¥æ°æ®åºä¸ºåå°çæºä¸éçç«ç¹æ¯ï¼http://www.webreview.comã å¯¹äº Web éæï¼PHP æ¯ä¸ä¸ªæ好çæ¥å£ãå®å¨http://www.php.net/ã 对äºå¤æçä»»å¡ï¼å¾å¤äººéç¨ Perl æ¥å£å 使ç¨CGI.pmçDBD::Pg æ mod_perl ã 2.3)PostgreSQLæ¥æå¾å½¢ç¨æ·çé¢åï¼ åä¸ç¨æ·ææ¯å¼æºå¼å人åè½æ¾å°å¾å¤çæå³PostgreSQLçGUIå¾å½¢å·¥å·è½¯ä»¶ï¼å¨ PostgreSQL社åºææ¡£æä¸ä¸ªè¯¦ç»çå表ã _________________________________________________________________ ç³»ç»ç®¡çé®é¢ 3.1)æææ ·è½æPostgreSQLè£å¨ /usr/local/pgsql 以å¤çå°æ¹ï¼ å¨è¿è¡ configure æ¶å ä¸ --prefix é项ã 3.2) æå¦ä½æ§å¶æ¥èªå¶ä»çµèçè¿æ¥ï¼ 缺çæåµä¸ï¼PostgreSQLåªå许æ¥èªæ¬æºä¸éè¿ unix åå¥æ¥åæTCP/IPæ¹å¼çè¿æ¥ã ä½ åªæå¨ä¿®æ¹äºéç½®æ件postgresql.conf中 çlisten_addressesï¼ä¸ä¹å¨éç½®æ件$PGDATA/pg_hba.confä¸æå¼äº åºäºè¿ç¨çµèï¼ host-based ï¼ç身份认è¯ï¼å¹¶éæ°å¯å¨PostgreSQLï¼å¦åå¶ä»çµèæ¯ä¸è½ä¸ä½ çPostgreSQL æå¡å¨è¿è¡è¿æ¥çã 3.3) æææ ·è°æ´æ°æ®åºå¼æ以è·å¾æ´å¥½çæ§è½ï¼ æä¸ä¸ªä¸»è¦æ¹é¢å¯ä»¥æåPostgreSQLçæ½è½ã æ¥è¯¢æ¹å¼çåå è¿ä¸»è¦æ¶åä¿®æ¹æ¥è¯¢æ¹å¼ä»¥è·åæ´å¥½çæ§è½: + å建索å¼ï¼åæ¬è¡¨è¾¾å¼åé¨åç´¢å¼ï¼ + 使ç¨COPYè¯å¥ä»£æ¿å¤ä¸ªInsertè¯å¥ï¼ + å°å¤ä¸ªSQLè¯å¥ç»æä¸ä¸ªäºå¡ä»¥åå°æ交äºå¡çå¼éï¼ + ä»ä¸ä¸ªç´¢å¼ä¸æåå¤æ¡è®°å½æ¶ä½¿ç¨CLUSTERï¼ + ä»ä¸ä¸ªæ¥è¯¢ç»æä¸ååºé¨åè®°å½æ¶ä½¿ç¨LIMITï¼ + 使ç¨é¢ç¼è¯å¼æ¥è¯¢ï¼Prepared Query)ï¼ + 使ç¨ANALYZE以ä¿æ精确çä¼åç»è®¡ï¼ + å®æä½¿ç¨ VACUUM æ pg_autovacuum + è¿è¡å¤§éæ°æ®æ´æ¹æ¶åå é¤ç´¢å¼ï¼ç¶åé建索å¼ï¼ æå¡å¨çéç½® éç½®æ件postgres.conf中 çå¾å¤è®¾ç½®é½ä¼å½±åæ§è½ï¼ææåæ°çå表å¯è§ï¼ 管çåæå/æ°æ®åºæå¡å¨è¿è¡ç¯å¢/æ°æ®åºæå¡å¨è¿è¡éç½®ï¼ æå³åæ°ç解éå¯è§ï¼http://www.varlena.com/varlena/GeneralBits/Tid bits/annotated_conf_e.html å http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.htmlã 硬件çéæ© è®¡ç®æºç¡¬ä»¶å¯¹æ§è½çå½±åå¯æµè§ http://candle.pha.pa.us/main/writings/pgsql/hw_performance/inde x.html å http://www.powerpostgresql.com/PerfList/ã 3.4)PostgreSQLéå¯ä»¥è·å¾ä»ä¹æ ·çè°è¯ç¹æ§ï¼ PostgreSQL æå¾å¤ç±»ä¼¼ log_* çæå¡å¨éç½®åéå¯ç¨äºæ¥è¯¢çæå°åè¿ç¨ç»è®¡ï¼èè¿äºå·¥ä½å¯¹è°è¯åæ§è½æµè¯å¾æå¸ ®å©ã 3.5) 为ä»ä¹å¨è¯å¾è¿æ¥æ¶æ¶å°âSorry, too many clientsï¼å·²æ太å¤ç¨æ·è¿æ¥ï¼âæ¶æ¯ï¼ è¿è¡¨ç¤ºä½ 已达å°ç¼ºç100个并ååå°è¿ç¨æ°çéå¶ï¼ä½ éè¦éè¿ä¿®æ¹postgres ql.confæ件ä¸çmax_connectionså¼æ¥ å¢å postmasterçåå°å¹¶åå¤çæ°ï¼ä¿®æ¹åééæ°å¯å¨postmasterã 3.6)PostgreSQLçå级è¿ç¨æåªäºå容 ï¼ PostgreSQL å¼åç»å¯¹æ¯æ¬¡å°çæ¬çå级主è¦åªåäºä¸äºBugä¿®æ£å·¥ä½ï¼å æ¤ä» 7.4.8 åçº§å° 7.4.9 ä¸éè¦ dump å restoreï¼ä»éè¦åæ­ ¢æ°æ®åºæå¡å¨ï¼å®è£æ´æ°åç软件åï¼ç¶åéå¯æå¡å¨å³å¯ã ææPostgreSQLçç¨æ·åºè¯¥å¨ææ¥è¿ï¼ä½ æ使ç¨ç主çæ¬ï¼çå°æ¹è¿çæ¬åå¸å°½å¿«åç º§ã尽管æ¯æ¬¡å级å¯è½é½æä¸ç¹é£é©ï¼PostgreSQLçå°æ¹è¿çä»ä»æ¯è®¾è®¡ç¨æ¥ä ¿®æ­ £ä¸äºBugçï¼ä»£ç æ¹å¨è¾å°ï¼æ以é£é©è¿æ¯å¾å°çãPostgreSQL社åºè®¤ä¸ºä¸è¬æ åµä¸ä¸å级çé£é©è¿æ¯å¤äºå级çã 主çæ¬çå级ï¼ä¾å¦ä» 7.3 å° 7.4ï¼é常ä¼ä¿®æ¹ç³»ç»è¡¨åæ°æ®è¡¨çåé¨æ ¼å¼ã è¿äºæ¹åä¸è¬æ¯è¾å¤æï¼å æ¤æ们ä¸ç»´ææ°æ®æ件çååå¼å®¹æ§ãå æ¤ä»èçæ¬ä¸­ è¿è¡æ°æ®å¯¼åºï¼dumpï¼/ç¶åå¨æ°çæ¬ä¸­ è¿è¡æ°æ®å¯¼å¥ï¼reloadï¼å¯¹ä¸»çæ¬çå级æ¯å¿é¡»çã 3.7)(使ç¨PostgreSQL)æéè¦ä½¿ç¨ä»ä¹è®¡ç®æºç¡¬ä»¶ ï¼ ç±äºè®¡ç®æºç¡¬ä»¶å¤§å¤æ°æ¯å¼å®¹çï¼äººä»¬æ»æ¯å¾åäºç¸ä¿¡ææ计ç®æºç¡¬ä»¶è ´¨éä¹æ¯ç¸åçãäºå®ä¸ä¸æ¯ï¼ ECC RAMï¼å¸¦å¥å¶æ ¡éªçååï¼ï¼SCSI ï¼ç¡¬çï¼åä¼è´¨ç主æ¿æ¯ä¸äºä¾¿å®è´§è¦æ´å å¯é ä¸å·ææ´å¥½çæ§è½ãPostgreSQL å ä¹å¯ä»¥è¿è¡å¨ä»»ä½ç¡¬ä»¶ä¸ï¼ ä½å¦æå¯é æ§åæ§è½å¯¹ä½ çç³»ç»å¾éè¦ï¼ä½ å°±éè¦å¨é¢çç 究ä¸ä¸ä½ ç硬件éç ½®äºãå¨æ们çé®ä»¶å表ä¸ä¹æå³äº 硬件éç½®åæ§ä»·æ¯ç讨论ã _________________________________________________________________ æä½é®é¢ 4.1) å¦ä½åªéæ©ä¸ä¸ªæ¥è¯¢ç»æç头å è¡ï¼ææ¯éæºçä¸è¡ï¼ å¦æä½ åªæ¯è¦æåå è¡æ°æ®ï¼å¹¶ä¸ä½ å¨æ§è¡æ¥è¯¢ä¸­ ç¥éç¡®åçè¡æ°ï¼ä½ å¯ä»¥ä½¿ç¨LIMITåè½ã å¦ææä¸ä¸ªç´¢å¼ä¸ ORDER BY中 çæ¡ä»¶å¹éï¼PostgreSQL å¯è½å°±åªå¤çè¦æ±ç头å æ¡è®°å½ï¼ ï¼å¦åå°å¯¹æ´ä¸ªæ¥è¯¢è¿è¡å¤çç´å°çæéè¦çè¡ï¼ãå¦æå¨æ§è¡æ¥è¯¢åè½æ¶ä¸ç¥éç¡®å çè®°å½æ°ï¼ å¯ä½¿ç¨æ¸¸æ (cursor)åFETCHåè½ã å¯ä½¿ç¨ä»¥ä¸æ¹æ³æåä¸è¡éæºè®°å½çï¼ SELECT cols FROM tab ORDER BY random() LIMIT 1 ; 4.2) å¦ä½æ¥ç表ãç´¢å¼ãæ°æ®åºä»¥åç¨æ·çå®ä¹ï¼å¦ä½æ¥çpsqléç¨å°çæ¥è¯¢æ令并æ¾ç¤ºå®ä»¬ ï¼ å¨psqlä¸ä½¿ç¨ \dt å½ä»¤æ¥æ¾ç¤ºæ°æ®è¡¨çå®ä¹ï¼è¦äºè§£psql中 çå®æ´å½ä»¤å表å¯ä½¿ç¨\? ï¼å¦å¤ï¼ä½ ä¹å¯ä»¥é读 psql çæºä»£ç æ件pgsql/src/bin/psql/describe.cï¼å®åæ¬ä¸ºçæpsqlåææ å½ä»¤çè¾åºçææ SQL å½ä»¤ãä½ è¿å¯ä»¥å¸¦ -E é项å¯å¨ psqlï¼ è¿æ ·å®å°æå°åºä½ å¨psql中 æç»åºçå½ä»¤æ§è¡æ¶çåé¨å®é使ç¨çSQLæ¥è¯¢è¯­ å¥ãPostgreSQLä¹æä¾äºä¸ä¸ªå¼å®¹SQLçINFORMATION SCHEMAæ¥å£ï¼ ä½ å¯ä»¥ä»è¿éè·åå³äºæ°æ®åºçä¿¡æ¯ã å¨ç³»ç»ä¸ä¹æä¸äºä»¥pg_ æ头çç³»ç»è¡¨ä¹æè¿°äºè¡¨çå®ä¹ã ä½¿ç¨ psql -l æ令å¯ä»¥ååºææçæ°æ®åºã ä¹å¯ä»¥æµè§ä¸ä¸ pgsql/src/tutorial/syscat.sourceæ件ï¼å®å举äºå¾å¤å¯ä»æ°æ®åºç³»ç»è¡¨ä¸ è·åä¿¡æ¯çSELECTè¯æ³ã 4.3) å¦ä½æ´æ¹ä¸ä¸ªå段çæ°æ®ç±»åï¼ å¨8.0çæ¬éæ´æ¹ä¸ä¸ªå段çæ°æ®ç±»åå¾å®¹æï¼å¯ä½¿ç¨ ALTER TABLE ALTER COLUMN TYPE ã å¨ä»¥åççæ¬ä¸ï¼å¯ä»¥è¿æ ·åï¼ BEGIN; ALTER TABLE tab ADD COLUMN new_col new_data_type; UPDATE tab SET new_col = CAST(old_col AS new_data_type); ALTER TABLE tab DROP COLUMN old_col; COMMIT; ä½ ç¶åå¯ä»¥ä½¿ç¨VACUUM FULL tab æ令æ¥ä½¿ç³»ç»æ¶åæ ææ°æ®æå ç¨ç空é´ã 4.4) åæ¡è®°å½ï¼å个表ï¼å个æ°æ®åºçæ大éå¶æ¯å¤å°ï¼ ä¸é¢æ¯ä¸äºéå¶ï¼ å个æ°æ®åºæå¤§å°ºå¯¸ï¼ æ éå¶ï¼å·²åå¨æ 32TB çæ°æ®åºï¼ å个表çæå¤§å°ºå¯¸ï¼ 32 TB ä¸è¡è®°å½çæå¤§å°ºå¯¸ï¼ 1.6 TB ä¸ä¸ªå段çæ大尺寸? 1 GB ä¸ä¸ªè¡¨éæ大è¡æ°ï¼ æ éå¶ ä¸ä¸ªè¡¨éæ大åæ°ï¼ 250-1600 ï¼ä¸åç±»åæå³ï¼ ä¸ä¸ªè¡¨éçæ大索å¼æ°éï¼ æ éå¶ å½ç¶ï¼å®éä¸æ²¡æçæ£çæ éå¶ï¼è¿æ¯è¦åå¯ç¨ç£ç空é´ãå¯ç¨åå/交æ¢åºçå¶çº¦ã äºå®ä¸ï¼å½ä¸è¿°è¿äºæ°å¼åå¾å¼å¸¸å°å¤§æ¶ï¼ç³»ç»æ§è½ä¹ä¼åå¾å¤§å½±åã å表çæå¤§å¤§å° 32 TB ä¸éè¦æä½ç³»ç»å¯¹å个æ件ä¹éè¿ä¹å¤§çæ¯æã大表ç¨å¤ä¸ª 1 GB çæä»¶å­ å¨ï¼å æ¤æ件系ç»å¤§å°çéå¶æ¯ä¸éè¦çã å¦æ缺ççå大å°å¢é¿å° 32K ï¼æ大çå表大å°åæ大åæ°è¿å¯ä»¥å¢å å°ååã æä¸ä¸ªéå¶å°±æ¯ä¸è½å¯¹å¤§å°å¤äº2000å­ èçåå建索å¼ã幸è¿å°æ¯è¿æ ·çç´¢å¼å¾å°ç¨å°ãéè¿å¯¹å¤å­ èåçå容è¿è¡MD5åç¨è¿ç®ç»æè¿è¡å½æ°ç´¢å¼å¯å¯¹åçå¯ä¸æ§å¾å°ä¿è¯ï¼ 并ä¸å¨ææ£ç´¢å许对åä¸çåè¯è¿è¡æç´¢ã 4.5) åå¨ä¸ä¸ªå¸åçææ¬æ件éçæ°æ®éè¦å¤å°ç£ç空é´ï¼ ä¸ä¸ª Postgres æ°æ®åºï¼å­ å¨ä¸ä¸ªææ¬æ件ï¼æå ç¨ç空é´æå¤å¯è½éè¦ç¸å½äºè¿ä¸ªææ¬æ件èªèº«å¤§å°5åçç£ ç空é´ã ä¾å¦ï¼å设æä¸ä¸ª 100,000 è¡çæ件ï¼æ¯è¡æä¸ä¸ªæ´æ°åä¸ä¸ªææ¬æè¿°ã å设ææ¬ä¸²çå¹³åé¿åº¦ä¸º20åèãææ¬æ件å ç¨ 2.8 MBãå­ æ¾è¿äºæ°æ®çPostgreSQLæ°æ®åºæä»¶å¤§çº¦æ¯ 6.4 MB: 28 åè: æ¯è¡ç头ï¼å¤§çº¦å¼ï¼ 24 åè: ä¸ä¸ªæ´æ°åå段åä¸ä¸ªææ¬åå段 + 4 åè: 页é¢åæååç»çæé ---------------------------------------- 56 åèæ¯è¡ PostgreSQL æ°æ®é¡µç大å°æ¯ 8192 åè (8 KB)ï¼åï¼ 8192 åèæ¯é¡µ ------------------- = 146 è¡/æ°æ®é¡µï¼åä¸åæ´ï¼ 56 åèæ¯è¡ 100000 æ°æ®è¡ -------------------- = 685 æ°æ®é¡µï¼åä¸åæ´ï¼ 146 è¡/æ°æ®é¡µ 685 æ°æ®é¡µ * 8192 åè/页 = 5,611,520 åèï¼5.6 MBï¼ ç´¢å¼ä¸éè¦è¿ä¹å¤çé¢å¤æ¶èï¼ä½ä¹ç¡®å®åæ¬è¢«ç´¢å¼çæ°æ®ï¼å æ­ ¤å®ä»¬ä¹å¯è½å¾å¤§ã 空å¼NULLåæ¾å¨ä½å¾ä¸ï¼å æ¤å ç¨å¾å°ç空é´ã 4.6) 为ä»ä¹æçæ¥è¯¢å¾æ¢ï¼ä¸ºä»ä¹è¿äºæ¥è¯¢æ²¡æå©ç¨ç´¢å¼ï¼ 并éæ¯ä¸ªæ¥è¯¢é½ä¼èªå¨ä½¿ç¨ç´¢å¼ãåªæå¨è¡¨ç大å°è¶è¿ä¸ä¸ªæå°å¼ï¼å¹¶ä¸æ¥ 询åªä¼éä¸è¡¨ä¸è¾å°æ¯ä¾çè®°å½æ¶æä¼éç¨ç´¢å¼ã è¿æ¯å 为索å¼æ«æå¼èµ·çéå³ç£çååå¯è½æ¯ç´æ¥å°è¯»å表ï¼é¡ºåºæ«æï¼æ´æ¢ã 为äºå¤æ­ æ¯å¦ä½¿ç¨ç´¢å¼ï¼PostgreSQLå¿é¡»è·å¾æå³è¡¨çç»è®¡å¼ãè¿äºç»è®¡å¼å¯ä»¥ä½¿ç ¨ VACUUM ANALYZEï¼æ ANALYZE è·å¾ã 使ç¨ç»è®¡å¼ï¼ä¼åå¨ç¥é表中 æå¤å°è¡ï¼å°±è½å¤æ´å¥½å°å¤ææ¯å¦å©ç¨ç´¢å¼ã ç»è®¡å¼å¯¹ç¡®å®ä¼åçè¿æ¥é¡ºåºåè¿æ¥æ¹æ³ä¹å¾æç¨ãå¨è¡¨çå容åçååæ¶ï¼åºå®æè¿ è¡ç»è®¡å¼çæ´æ°æ¶éã ç´¢å¼é常ä¸ç¨äº ORDER BY ææ§è¡è¿æ¥ã对ä¸ä¸ªå¤§è¡¨çä¸æ¬¡é¡ºåºæ«æååä¸æ¬¡æåºé常æ¯ç´¢å¼æ«æè¦å¿«ãç¶ èï¼å¦æå° LIMIT å ORDER BY ç»åå¨ä¸èµ·ä½¿ç¨çè¯ï¼é常å°ä¼ä½¿ç¨ç´¢å¼ï¼å 为è¿æ¶ä»è¿å表中 çä¸å°é¨åè®°å½ã å¦æä½ ç¡®ä¿¡PostgreSQLçä¼åå¨ä½¿ç¨é¡ºåºæ«ææ¯ä¸æ£ç¡®çï¼ä½ å¯ä»¥ä½¿ç¨SET enable_seqscan TO 'off'æ令æ¥å³é顺åºæ«æï¼ ç¶åå次è¿è¡æ¥è¯¢ï¼ä½ å°±å¯ä»¥çåºä½¿ç¨ä¸ä¸ªç´¢å¼æ«ææ¯å¦ç¡®å®è¦å¿«ä¸äºã å½ä½¿ç¨éé符æä½ï¼ä¾å¦ LIKE æ ~ æ¶ï¼ç´¢å¼åªè½å¨ç¹å®çæåµä¸ä½¿ç¨ï¼ * å符串çå¼å§é¨åå¿é¡»æ¯æ®éå符串ï¼ä¹å°±æ¯è¯´ï¼ + LIKE 模å¼ä¸è½ä»¥ % æ头ã + ~ ï¼æ£å表达å¼ï¼æ¨¡å¼å¿é¡»ä»¥ ^ æ头ã * å符串ä¸è½ä»¥å¹éå¤ä¸ªå符ç模å¼ç±»æ头ï¼ä¾å¦ [a-e]ã * 大å°åæ å³çæ¥æ¾ï¼å¦ ILIKE å ~* çä¸ä½¿ç¨ç´¢å¼ï¼ä½å¯ä»¥ç¨ 4.8 èæè¿°ç表达å¼ç´¢å¼ã * å¨å initdb æ¶å¿é¡»éç¨ç¼ºççæ¬å°è®¾ç½® C localeï¼å 为系ç»ä¸å¯è½ç¥éå¨éC localeæåµæ¶ä¸ä¸ä¸ªæ大å符æ¯ä»ä¹ã å¨è¿ç§æåµä¸ï¼ä½ å¯ä»¥å建ä¸ä¸ªç¹æ®çtext_pattern_opsç´¢å¼æ¥ç¨äºLIKE çç´¢å¼ã å¨8.0ä¹åççæ¬ä¸­ ï¼é¤éè¦æ¥è¯¢çæ°æ®ç±»ååç´¢å¼çæ°æ®ç±»åç¸å¹éï¼å¦åç´¢å¼ç»å¸¸æ¯æªè¢«ç¨å°ï¼ç ¹å«æ¯å¯¹int2,int8åæ°å¼åçç´¢å¼ã 4.7) æå¦ä½æè½çå°æ¥è¯¢ä¼åå¨æ¯ææ ·è¯ä¼°å¤çæçæ¥è¯¢ï¼ åè EXPLAIN æå页ã 4.8) æææ ·åæ£å表达å¼æç´¢å大å°åæ å³çæ­ £å表达å¼æ¥æ¾ï¼ææ ·å©ç¨ç´¢å¼è¿è¡å¤§å°åæ å³æ¥æ¾ï¼ æä½ç¬¦ ~ å¤çæ£å表达å¼å¹éï¼è ~* å¤ç大å°åæ å³çæ­ £å表达å¼å¹éã大å°åæ å³ç LIKE åç§æ为 ILIKEã 大å°åæ å³ççå¼æ¯è¾é常ååï¼ SELECT * FROM tab WHERE lower(col) = 'abc'; è¿æ ·å°ä¸ä¼ä½¿ç¨æ åçç´¢å¼ãä½æ¯å¯ä»¥å建ä¸ä¸ªå¨è¿ç§æåµä¸ä½¿ç¨ç表达å¼ç ´¢å¼: CREATE INDEX tabindex ON tab (lower(col)); å¦æä¸è¿°ç´¢å¼å¨å建æ¶å å¥UNIQUE约æï¼è½ç¶ç´¢å¼å段èªèº«å容å¯ä»¥å­ å¨å¤§å°åä¸éçå容ï¼ä½å¦ææUNIQUE约æåï¼è¿äºå容ä¸è½ä»ä»æ¯å¤§å°åä¸åï¼å¦åä ¼é æå²çªï¼ã为äºä¿è¯ä¸åçè¿ç§æåµï¼å¯ä»¥ä½¿ç¨CHECK约ææ¡ä»¶ææ¯è§¦åå¨å¨å½ å¥æ¶è¿è¡éå¶ã 4.9) å¨ä¸ä¸ªæ¥è¯¢éï¼æææ ·æ£æµä¸ä¸ªå段æ¯å¦ä¸º NULL ï¼æå¦ä½æè½åç¡®æåºèä¸è®ºæå­ æ®µæ¯å¦å« NULL å¼ï¼ ç¨ IS NULL å IS NOT NULL æµè¯è¿ä¸ªå段ï¼å·ä½æ¹æ³å¦ä¸ï¼ SELECT * FROM tab WHERE col IS NULL; 为äºè½å¯¹å« NULLå段æåºï¼å¯å¨ ORDER BY æ¡ä»¶ä¸ä½¿ç¨ IS NULLå IS NOT NULL 修饰符ï¼æ¡ä»¶ä¸ºç true å°æ¯æ¡ä»¶ä¸ºåfalse æå¨åé¢ï¼ä¸é¢çä¾å­ å°±ä¼å°å« NULL çè®°å½æå¨ç»æçä¸é¢é¨åï¼ SELECT * FROM tab ORDER BY (col IS NOT NULL) 4.10) åç§å符类åä¹é´æä»ä¹ä¸åï¼ ç±»å åé¨å称 说æ VARCHAR(n) varchar æå®äºæ大é¿åº¦ï¼åé¿å­ 符串ï¼ä¸è¶³å®ä¹é¿åº¦çé¨åä¸è¡¥é½ CHAR(n) bpchar å®é¿å符串ï¼å®éæ°æ®ä¸è¶³å®ä¹é¿åº¦æ¶ï¼ä»¥ç©ºæ ¼è¡¥é½ TEXT text 没æç¹å«çä¸ééå¶ï¼ä»åè¡çæ大é¿åº¦éå¶ï¼ BYTEA bytea åé¿åèåºåï¼ä½¿ç¨NULLå符ä¹æ¯å许çï¼ "char" char å个å符 å¨ç³»ç»è¡¨åå¨ä¸äºé误信æ¯éä½ å°çå°åé¨å称ã ä¸é¢æåçååç§ç±»åæ¯"varlena"ï¼åé¿ï¼ç±»åï¼ä¹å°±æ¯è¯´ï¼å¼å¤´çåä¸ªå­ èæ¯é¿åº¦ï¼åé¢ææ¯æ°æ®ï¼ã äºæ¯å®éå ç¨ç空é´æ¯å£°æç大å°è¦å¤ä¸äºã ç¶èè¿äºç±»åå¦å®ä¹å¾é¿æ¶é½å¯ä»¥è¢«å缩åå¨ï¼å æ­ ¤ç£ç空é´ä¹å¯è½æ¯é¢æ³çè¦å°ã VARCHAR(n) å¨åå¨éå¶äºæ大é¿åº¦çåé¿å符串æ¯æ好çã TEXT éç¨äºå­ å¨æ大å¯è¾¾ 1Gå·¦å³ä½æªå®ä¹éå¶é¿åº¦çå符串ã CHAR(n) æéåäºåå¨é¿åº¦ç¸åçå符串ã CHAR(n)ä¼æ ¹æ®æç»å®çå­ æ®µé¿åº¦ä»¥ç©ºæ ¼è¡¥è¶³ï¼ä¸è¶³çå段å容ï¼ï¼ è VARCHAR(n) åªå­ å¨æç»å®çæ°æ®å容ã BYTEA ç¨äºåå¨äºè¿å¶æ°æ®ï¼å°¤å¶æ¯åå« NULL å­ èçå¼ãè¿äºç±»åå·æå·®ä¸å¤çæ§è½ã 4.11.1) æææ ·å建ä¸ä¸ªåºåå·ææ¯èªå¨éå¢çåæ®µï¼ PostgreSQL æ¯æ SERIAL æ°æ®ç±»åãï¼å­ 段å®ä¹ä¸ºSERIALåï¼å°èªå¨å建ä¸ä¸ªåºåçæå¨ï¼ä¾å¦ï¼ CREATE TABLE person ( id SERIAL, name TEXT ); ä¼èªå¨è½¬æ¢ä¸ºä»¥ä¸SQLè¯å¥ï¼ CREATE SEQUENCE person_id_seq; CREATE TABLE person ( id INT4 NOT NULL DEFAULT nextval('person_id_seq'), name TEXT ); åè create_sequence æå页è·åå³äºåºåçæå¨çæ´å¤ä¿¡æ¯ã 4.11.2) æå¦ä½è·å¾ä¸ä¸ªæå¥çåºåå·çå¼ï¼ ä¸ç§æ¹æ³æ¯å¨æå¥ä¹ååç¨å½æ° nextval() ä»åºå对象éæ£ç´¢åºä¸ä¸ä¸ª SERIAL å¼ï¼ç¶ååç¨æ¤å¼ç²¾ç¡®å°æå¥ãä½¿ç¨ 4.11.1 éçä¾è¡¨ï¼å¯ç¨ä¼ªç è¿æ ·æè¿°ï¼ new_id = execute("SELECT nextval('person_id_seq')"); execute("INSERT INTO person (id, name) VALUES (new_id, 'Blaise Pascal')"); è¿æ ·è¿è½å¨å¶ä»æ¥è¯¢ä¸ä½¿ç¨åæ¾å¨ new_id éçæ°å¼ï¼ä¾å¦ï¼ä½ä¸ºåç§ person 表çå¤é®ï¼ã 注æèªå¨å建ç SEQUENCE 对象çå称å°ä¼æ¯ __seqï¼ è¿é table å serialcolumn åå«æ¯ä½ ç表çå称åä½ ç SERIAL å段çå称ã 类似çï¼å¨ SERIAL 对象缺çæå¥åä½ å¯ä»¥ç¨å½æ° currval() æ£ç´¢åèµå¼ç SERIAL å¼ï¼ä¾å¦ï¼ execute("INSERT INTO person (name) VALUES ('Blaise Pascal')"); new_id = execute("SELECT currval('person_id_seq')"); 4.11.3) åæ¶ä½¿ç¨ currval() ä¼å¯¼è´åå¶ä»ç¨æ·çå²çªæåµåï¼ ä¸ä¼ãcurrval() è¿åçæ¯ä½ æ¬æ¬¡ä¼è¯è¿ç¨æèµçå¼èä¸æ¯ææç¨æ·çå½åå¼ã 4.11.4) 为ä»ä¹ä¸å¨äºå¡å¼å¸¸ä¸æ¢åéç¨åºåå·å¢ï¼ä¸ºä»ä¹å¨åºåå·å段çåå¼ä¸åå¨é´æ­ å¢ï¼ 为äºæé«å¹¶åæ§ï¼åºåå·å¨éè¦çæ¶åèµäºæ­ £å¨è¿è¡çäºå¡ï¼å¹¶ä¸å¨äºå¡ç»æä¹åä¸è¿è¡éå®ï¼ è¿å°±ä¼å¯¼è´å¼å¸¸ä¸­ æ¢çäºå¡åï¼åºåå·ä¼åºç°é´éã 4.12) ä»ä¹æ¯ OID ï¼ä»ä¹æ¯ CTID ï¼ PostgreSQL éå建çæ¯ä¸è¡è®°å½é½ä¼è·å¾ä¸ä¸ªå¯ä¸çOIDï¼é¤éå¨å建表æ¶ä½¿ç¨WITHOUT OIDSé项ã OIDå建æ¶ä¼èªå¨çæä¸ä¸ª4åèçæ´æ°ï¼ææ OID å¨ç¸åºPostgreSQLæå¡å¨ä¸åæ¯å¯ä¸çã ç¶èï¼å®å¨è¶è¿40亿æ¶å°æº¢åºï¼ OIDæ­ ¤åä¼åºç°éå¤ãPostgreSQL å¨å®çåé¨ç³»ç»è¡¨éä½¿ç¨ OID å¨è¡¨ä¹é´å»ºç«èç³»ã å¨ç¨æ·çæ°æ®è¡¨ä¸ï¼æ好æ¯ä½¿ç¨SERIAlæ¥ä»£æ¿OID å 为SERIALåªè¦ä¿è¯å¨å个表中 çæ°å¼æ¯å¯ä¸çå°±å¯ä»¥äºï¼è¿æ ·å®æº¢åºçå¯è½æ§å°±é常å°äºï¼ SERIAL8å¯ç¨æ¥ä¿å8åèçåºåæ°å¼ã CTID ç¨äºæ è¯å¸¦çæ°æ®åï¼å°åï¼åï¼ååï¼å移çç¹å®çç©çè¡ã CTID å¨è®°å½è¢«æ´æ¹æéè½½ååçæ¹åãç´¢å¼æ°æ®ä½¿ç¨å®ä»¬æåç©çè¡ã 4.13) 为ä»ä¹ææ¶å°é误信æ¯âERROR: Memory exhausted in AllocSetAlloc()âï¼ è¿å¾å¯è½æ¯ç³»ç»çèæååç¨åäºï¼æèåæ ¸å¯¹æäºèµæºæè¾ä½çéå¶å¼ãå¨å¯å¨ postmaster ä¹åè¯è¯ä¸é¢çå½ä»¤ï¼ ulimit -d 262144 limit datasize 256m åå³äºä½ ç¨ç shellï¼ä¸é¢å½ä»¤åªæä¸æ¡è½æåï¼ä½æ¯å®å°æä½ çè¿ç¨æ°æ®æ®µéå¶è®¾å¾æ¯è¾é«ï¼ å èä¹è®¸è½è®©æ¥è¯¢å®æãè¿æ¡å½ä»¤åºç¨äºå½åè¿ç¨ï¼ä»¥åææå¨è¿æ¡å½ä»¤è¿è¡ååå »ºçåè¿ç¨ã å¦æä½ æ¯å¨è¿è¡SQL客æ·ç«¯æ¶å 为åå°è¿åäºå¤ªå¤çæ°æ®èåºç°é®é¢ï¼è¯·å¨è¿è¡ 客æ·ç«¯ä¹åæ§è¡ä¸è¿°å½ä»¤ã 4.14) æå¦ä½æè½ç¥éæè¿è¡çPostgreSQLççæ¬ï¼ ä» psql éï¼è¾å¥ SELECT version();æ令ã 4.15) æå¦ä½å建ä¸ä¸ªç¼ºçå¼æ¯å½åæ¶é´çåæ®µï¼ ä½¿ç¨ CURRENT_TIMESTAMPï¼ CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); 4.16) æææ ·è¿è¡ outer join ï¼å¤è¿æ¥ï¼ï¼ PostgreSQL éç¨æ åç SQL è¯æ³æ¯æå¤è¿æ¥ãè¿éæ¯ä¸¤ä¸ªä¾åï¼ SELECT * FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col); ææ¯ SELECT * FROM t1 LEFT OUTER JOIN t2 USING (col); è¿ä¸¤ä¸ªçä»·çæ¥è¯¢å¨ t1.col å t2.col ä¸åè¿æ¥ï¼å¹¶ä¸è¿å t1 中 æææªè¿æ¥çè¡ï¼é£äºå¨ t2 ä¸æ²¡æå¹éçè¡ï¼ã å³[å¤]è¿æ¥ï¼RIGHT OUTER JOINï¼å°è¿å t2 ä¸æªè¿æ¥çè¡ã å®å¨å¤è¿æ¥ï¼FULL OUTER JOINï¼å°è¿å t1 å t2 ä¸æªè¿æ¥çè¡ã å³é®å OUTER å¨å·¦[å¤]è¿æ¥ãå³[å¤]è¿æ¥åå®å¨[å¤]è¿æ¥ä¸­ æ¯å¯éçï¼æ®éè¿æ¥è¢«ç§°ä¸ºåè¿æ¥ï¼INNER JOINï¼ã 4.17) å¦ä½ä½¿ç¨æ¶åå¤ä¸ªæ°æ®åºçæ¥è¯¢ï¼ 没æåæ³æ¥è¯¢å½åæ°æ®åºä¹å¤çæ°æ®åºã å 为PostgreSQLè¦å è½½ä¸æ°æ®åºç¸å³çç³»ç»ç®å½ï¼ç³»ç»è¡¨ï¼ï¼å æ­ ¤è·¨æ°æ®åºçæ¥è¯¢å¦ä½æ§è¡æ¯ä¸å®çã éå å¢å¼æ¨¡åcontrib/dblinkå许éç¨å½æ°è°ç¨å®ç°è·¨åºæ¥è¯¢ãå½ç¶ç¨æ·ä¹å¯ä»¥ åæ¶è¿æ¥å°ä¸åçæ°æ®åºæ§è¡æ¥è¯¢ç¶åå¨å®¢æ·ç«¯å并ç»æã 4.18) å¦ä½è®©å½æ°è¿åå¤è¡æå¤åæ°æ®ï¼ å¨å½æ°ä¸è¿åæ°æ®è®°å½éçåè½æ¯å¾å®¹æ使ç¨çï¼è¯¦æåè§ï¼ http://techdocs.postgresql.org/guides/SetReturningFunctions 4.19) 为ä»ä¹æå¨ä½¿ç¨PL/PgSQLå½æ°åå临æ¶è¡¨æ¶ä¼æ¶å°é误信æ¯ârelation with OID ##### does not existâï¼ PL/PgSQLä¼ç¼åå½æ°çèæ¬å容ï¼ç±æ¤å¸¦æ¥çä¸ä¸ªä¸å¥½çå¯ä½ç¨æ¯è¥ä¸ä¸ª PL/PgSQL å½æ°è®¿é®äºä¸ä¸ªä¸´æ¶è¡¨ï¼ç¶å该表被å é¤å¹¶é建äºï¼åå次è°ç¨è¯¥å½æ°å °å¤±è´¥ï¼ å 为ç¼åçå½æ°å容ä»ç¶æåæ§ç临æ¶è¡¨ã解å³çæ¹æ³æ¯å¨ PL/PgSQL ä¸ç¨EXECUTE 对临æ¶è¡¨è¿è¡è®¿é®ãè¿æ ·ä¼ä¿è¯æ¥è¯¢å¨æ§è¡åæ»ä¼è¢«éæ°è§£æã 4.20) ç®åæåªäºæ°æ®å¤å¶æ¹æ¡å¯ç¨ï¼ âå¤å¶âåªæ¯ä¸ä¸ªæ¯è¯ï¼æ好å ç§å¤å¶ææ¯å¯ç¨ï¼æ¯ç§é½æä¼ç¹å缺ç¹ï¼ 主/ä»å¤å¶æ¹å¼æ¯å许ä¸ä¸ªä¸»æå¡å¨æ¥å读/åçç³è¯·ï¼èå¤ä¸ªä»æå¡å¨åªè½æ¥åè ¯»/SELECTæ¥è¯¢çç³è¯·ï¼ ç®åææµè¡ä¸åè´¹ç主/ä»PostgreSQLå¤å¶æ¹æ¡æ¯ Slony-I ã å¤ä¸ªä¸»æå¡å¨çå¤å¶æ¹å¼å许å°è¯»/åçç³è¯·åéç»å¤å°ç计ç®æºï¼è¿ç§æ¹å¼ç±äºé è¦å¨å¤å°æå¡å¨ä¹é´åæ¥æ°æ®åå¨ å¯è½ä¼å¸¦æ¥è¾ä¸¥éçæ§è½æ失ï¼Pgclusteræ¯ç®åè¿ç§æ¹æ¡ 中 æ好çï¼èä¸è¿å¯ä»¥åè´¹ä¸è½½ã ä¹æä¸äºåä¸éä»è´¹ååºäºç¡¬ä»¶çæ°æ®å¤å¶æ¹æ¡ï¼æ¯æä¸è¿°åç§å¤å¶æ¨¡åã 4.21) 为ä½æ¥è¯¢ç»ææ¾ç¤ºç表åæååä¸æçæ¥è¯¢è¯å¥ä¸çä¸åï¼ä¸ºä½å¤§åç¶æä¸è½ä¿çï¼ æ常è§çåå æ¯å¨å建表æ¶å¯¹è¡¨åææ¯åå使ç¨äºåå¼å·ââï¼å½ä½¿ç¨äºåå¼å·åï¼è¡ ¨åæååï¼ç§°ä¸ºæ è¯ç¬¦ï¼åå¨æ¶æ¯åºå 大å°åçï¼ è¿æè°çä½ å¨æ¥è¯¢æ¶è¡¨åæååä¹åºä½¿ç¨åå¼å·ï¼ä¸äºå·¥å·è½¯ä»¶ï¼åpgAdminä¼å¨ ååºå建表çæ令æ¶èªå¨å°å¨æ¯ä¸ªæ è¯ç¬¦ä¸å åå¼å·ã å æ­ ¤ï¼ä¸ºäºæ è¯ç¬¦çç»ä¸ï¼ä½ åºè¯¥ï¼ * å¨å建表æ¶é¿åå°æ è¯ç¬¦ä½¿ç¨åå¼å·å¼èµ·æ¥ã * å¨æ è¯ç¬¦ä¸åªä½¿ç¨å°ååæ¯ã * ï¼ä¸ºäºä¸å·²åå¨çæ è¯ç¬¦ç¸åï¼å¨æ¥è¯¢ä¸ä½¿ç¨åå¼å·å°æ è¯ç¬¦å¼èµ·æ¥ã