From 28ebe6b5d0ecafee14db78759ce906987532b704 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Wed, 21 Feb 2007 16:42:36 +0000 Subject: Update Chinese FAQs to have two versions, a traditional Chinese version (Taiwan) and a Simplified version (China (PRC)). Backpatch to 8.2.X. Daojing.Zhou --- doc/FAQ_chinese | 794 -------------------------------------------------------- 1 file changed, 794 deletions(-) delete mode 100644 doc/FAQ_chinese (limited to 'doc/FAQ_chinese') diff --git a/doc/FAQ_chinese b/doc/FAQ_chinese deleted file mode 100644 index 43d46647592..00000000000 --- a/doc/FAQ_chinese +++ /dev/null @@ -1,794 +0,0 @@ - - PostgreSQL 常è§é®é¢ï¼FAQï¼ - - æè¿æ´æ°ï¼2007 å¹´ 1 æ 5 æ¥ ææäº 15:40:20 EST - ä¸æçæè¿æ´æ°ï¼2007 å¹´ 1 æ 29 æ¥ ææä¸ 22:48: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 åå¶ä»æ°æ®åºç³»ç»æ¯èµ·æ¥å¦ä½ï¼ - -ç¨æ·å®¢æ·ç«¯é®é¢ - - 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çæä¹å¤çå容ã - _________________________________________________________________ - -ç¨æ·å®¢æ·ç«¯é®é¢ - - 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ä¼å¨ - ååºå建表çæ令æ¶èªå¨å°å¨æ¯ä¸ªæ è¯ç¬¦ä¸å åå¼å·ã å æ­ - ¤ï¼ä¸ºäºæ è¯ç¬¦çç»ä¸ï¼ä½ åºè¯¥ï¼ - * å¨å建表æ¶é¿åå°æ è¯ç¬¦ä½¿ç¨åå¼å·å¼èµ·æ¥ã - * å¨æ è¯ç¬¦ä¸åªä½¿ç¨å°ååæ¯ã - * ï¼ä¸ºäºä¸å·²åå¨çæ è¯ç¬¦ç¸åï¼å¨æ¥è¯¢ä¸ä½¿ç¨åå¼å·å°æ è¯ç¬¦å¼èµ·æ¥ã -- cgit v1.2.3