Transcription of PostgreSQL: Introduction and Concepts
1 PostgreSQLI ntroductionandConceptsPostgreSQLI ntroductionandConceptsBruceMomjianADDISO N WESLEYB oston SanFrancisco NewYork Toronto Montreal London MunichParis Madrid CapeTown Sidney Tokyo Singapore MexicoCityManyof thedesignationsusedbymanufacturersandsel lerstodistinguishtheirproductsare thosedesignationsappearinthisbook,andwew ere aware ofa trademarkclaim, inthepreparationofthisbook,butmake information,pleasecontact:PearsonEducati onCorporateSalesDivisionOneLake StreetUpperSaddleRiver, 2001byAddison ofthispublicationmaybereproduced,storedi na retrievalsystem,ortransmitted,inanyformo rbyanymeans,electronic,mechanical,photoc opying,recording,orotherwise,withoutthep riorconsentofthepublisher.
2 , : introductionandconcepts/ Momjian, 85 dc2100-045367 CIPT hisbookwaspreparedwithLYX andLATEX andreproducedbyAddison 2 3 4 5 6 7 8 9-MA-0403020100 FirstPrinting,November2000To mywonderfulwife,Christine,andmyfineboys, Matthew, Luke, .. DatabaseSession.. Session.. Session.. , SHOW,andRESET.. Tips.. JoinKey.. andForeignKeys.. (OIDs).. , EXCEPT,andINTERSECTC lauses.. Functions.. Operators.. Variables.. (BLOBs).. Tables.. LanguageInterface(LIBPQ).. (LIBPGEASY).. (ECPG).. ++(LIBPQ++).
3 (JDBC).. (PGTCLSH/PGTKSH).. Code.. Code.. ,Types,andAggregates.. (FAQs).. complexWHERE clause.. singlecell.. blockofcells.. columnlabel.. rangeofvalues.. customernameusingtwoqueries.. customernameusingonequery.. customername.. customercode.. one-to-manyjoin.. sequence.. sstate.. rewrittenusingANYandEXISTS.. rewrittenusingALLandEXISTS.. functioncall.. ina singletransaction.. uniqueindex.. complexEXPLAIN examples.. tableauto-destruction.. tableuse.. tablemodifications.
4 KEYcolumn.. multicolumnPRIMARY KEY.. andforeignkeys.. multicolumnforeignkey.. \pset.. s openingwindow.. s tablewindow.. ++sampleprogram.. input.. output.. joinanda function.. ().. newcopyofdatabasetest.. transactions.. transactions.. lock.. tableisolation.. s query buffercommands.. s generalcommands.. s\psetoptions.. s outputformatshortcuts.. s predefinedvariables.. s listingcommands.. s largeobjectcommands.. s command-linearguments.
5 , exceptionalonessurvivethetransitionfromt heuniversitytotherealworldandgoontobecom ea oneof a testamenttothededicationandhard nosmallfeat,maintainingandenhancinganinh eritedcodebaseare ,butalsoexpanditsuseamongtheInternetuser community. Thisbookmarksa majormilestoneinthehistory ,laterrenamedPOSTGRESQL, startedasa hada thetime,wehadnoideawherewewere ,butsimplya , it hadoccurredtousthattheInternetusercommun ityreallyneededanopensource, , ourfirstreleasewasmetwithgreatenthusiasm ,andweare very complexsystemlikePOSTGRESQLisa criticalgapin thedocumentationof coversa widerangeoftopics,fromthebasicstothemore ,BruceMomjianhasdrawnonhisexperienceinhe lpingbeginnerswithPOSTGRESQL.
6 Thetextis ,hedoesanoutstandingjobof ! It is ourhopethisbookwillnotonlyteachyouaboutu singPOSTGRESQL,butalsoinspire ,co-authorsofPostgres95xxiiiPrefaceThisb ookisaboutPOSTGRESQL, , is hard tobelievetheadvancesduringthepastfouryea rsundertheguidanceof a teamof a testamenttotheirvision, designedtoleadthereaderfromtheirfirstdat abasequery , basicknowledgeofoperatingsystemcapabilit iesisexpected, short history ofPOSTGRESQL, coveredearly, firmfoundationestablished,additionalcomm andsare eachstep,thepurposeofeachcommandis tohavereadersunderstandmore thanquery valuable, databasenoviceshouldreadtheentire book, safewaytotry ,laterchapterswillbe-gintomake more themainbodyof copyof thePOSTGRESQL manualpageswhichshouldbeconsultedanytime youhavetroublewithquery ,donotoverlooktheexcellentdocumentationt hatispart ,includingmuchPOSTGRESQL-specificfunctio nalitythatcannotbecoveredina bookof thexxvxxviPREFACE documentationare referencedinthisbookwhere ,SMALLCAPSforSQLkeywords.
7 A shiningexampleofInternetsoftware Fournier, Marc G.,inWolfville,NovaScotia,Canada,coordin atestheentire effort, providestheserver, andadministerstheprimary Website,mailinglists,ftpsite,andsourceco derepository. Lane,Tom,inPittsburgh,Pennsylvania,Unite dStates,isoftenseenworkingontheplan-ner/ optimizer, buthasleft Lockhart, ThomasG.,inPasadena,California,UnitedSta tes,worksondocumentation,datatypes(parti cularlydate/timeandgeometricobjects),and SQLstandardscompatibility. Mikheev, VadimB.,inSanFrancisco,California,United States,doeslargeprojects,likevacuum,subs elects,triggers,andmulti-versionconcurre ncycontrol (MVCC).
8 Momjian,Bruce,inPhiladelphia,Pennsylvani a,UnitedStates,maintainsFAQandTODO lists,codecleanup,patchapplication,train ingmaterials,andsomecoding. Wieck,Jan,nearHamburg,Germany, overhauledthequery rewriterulesystem,wroteourprocedurallang uagesPL/PGSQLandPL/TCL, Cain,D Arcy, J. M.,inToronto,Ontario,Canada,workedontheT CLinterface,PyGreSQL,andtheINET type. DalZotto,Massimo,nearTrento,Italy, Eisentraut,Peter, inUppsala,Sweden,hasaddedmanyfeatures,in cludinganoverhaulofpsql. Elphick,Oliver, inNewport, IsleofWight,UnitedKingdom,maintainsthePO STGRESQL packageforDebianLinux.
9 Horak,Daniel,nearPilzen,CzechRepublic,di dtheWinNTport ofPOSTGRESQL(usingtheCygwinenvironment). Inoue,Hiroshi,inFukui,Japan,improvedbtre eindexaccess. Ishii,Tatsuo,inZushi,Kanagawa,Japan,hand lesmultibyteforeignlanguagesupport andportingissues. Martin,Dr. ,in London,UnitedKingdom,createdtheECPG interfaceandhelpedintheLinuxandIrixFAQs includingsomepatchestothePOSTGRESQL code. Mergl,Edmund,inStuttgart, Germany, ,whichis availableviaCPAN. Meskes,Michael,inDusseldorf, Germany, handlesmultibyteforeignlanguagesupport andmaintainsECPG.
10 Mount,Peter, inMaidstone,Kent,UnitedKingdom,createdth eJavaJDBC interface. Nikolaidis,Byron,inBaltimore,Maryland,Un itedStates,rewroteandmaintainstheODBC interfaceforWindows. Owen,Lamar, inPisgahForest,NorthCarolina,UnitedState s,maintainstheRPMpackage. Teodorescu,Constantin,inBraila,Romania,c reatedthePGACCESS interface. Thyni,G ran,inKiruna,Sweden, Bartunov, Oleg,inMoscow, Russia,introducedthelocalesupport. Vielhaber, Vince,nearDetroit,Michigan,UnitedStates, Inthischapter, youwilllearnaboutdatabases,opensourcesof tware,andthehistory :word processors,spreadsheets, processororspreadsheetto store smallamountsof , withlargevolumesof dataordatathatmustberetrievedandupdatedf requently, databasesare ,rapiddataretrieval, SancestorwasIngres,developedattheUnivers ityofCaliforniaatBerkeley(1977 1985).
