PostgreSQL: Introduction and Concepts

1 PostgreSQL: IntroductionandConceptsBruceM omjian19thFebruary 2004 ADDISON WESLEYB oston SanFrancisco NewYork Toronto Montreal London MunichParis Madrid CapeTown Sidney Tokyo Singapore MexicoCityManyofthedesignationsusedbyman ufacturersandsellerstodistinguishtheirpr oductsare thosedesignationsappearinthisbook,andwew ere aware ofa trademarkclaim, inthepreparationof thisbook,butmake noexpressedorimpliedwarrantyof information,pleasecontact:PearsonEducati onCorporateSalesDivisionOneLake StreetUpperSaddleRiver, 2001byAddison ofthispublicationmaybereproduced,storedi na retrievalsystem,ortransmitted,inanyformo rbyanymeans,electronic,mechanical,photoc opying,recording,orotherwise,withoutthep riorconsentofthepublisher. , : introductionandconcepts/ Momjian, '85 dc2100-045367 CIPT hisbookwaspreparedwithLYX and andreproducedbyAddison Wesleyfrom 3 4 5 6 7 8 9-MA-04030201 SecondPrinting,February 2001 Chapter1 HistoryofPOSTGRESQL3 Chapter2 IssuingDatabaseCommands$ psqltestWelcometo psql, :\copyrightfordistributionterms\h forhelpwithSQLcommands\?

2 Forhelpon internalslashcommands\g or terminatewithsemicolonto executequery\q to quittest=>Figure :psqlsessionstart-up4test=>SELECTCURRENT _USER;getpgusername---------------postgr es(1 row)test=>Figure :My rstSQLquerytest=>SELECT test->1 + 3test->;?column?----------4(1 row)test=>Figure :Multilinequerytest=>SELECT test->2 * 10 + 1test->\pSELECT2 * 10 + 1test->\g?column?----------21(1 row)test=>Figure :Backslash-pdemoChapter3 BasicSQLC ommandsDatabase Server TestFinanceDemoFigure :Tablefriendtest=>CREATETABLE friend(test(>firstnameCHAR(15),test(>las tnameCHAR(20),test(>cityCHAR(15),test(>s tateCHAR(2),test(>ageINTEGER test(>);CREATEF igure :Createtablefriendtest=>\d friendTable"friend"Attribute|Type| Modifier-----------+----------+--------- -firstname| char(15)|lastname| char(20)|city| char(15)|state| char(2)|age| integer|Figure :Exampleofbackslash-d9test=>INSERTINTO friendVALUES(test(>'Mike',test(>'Nichols ',test(>'Tampa',test(>'FL',test(>19test( >);INSERT190531 Figure :INSERT intofriendtest=>INSERTINTO friendVALUES(test(>'Cindy',test(>'Anders on',test(>'Denver',test(>'CO',test(>23te st(>);INSERT190541test=>INSERTINTO friendVALUES(test(>'Sam',test(>'Jackson' ,test(>'Allentown',test(>'PA',test(>22te st(>);INSERT190551 Figure :AdditionalfriendINSERT commands10test=>SELECT* FROM friend.))))))))))))))))))))))))

3 Firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Mike| Nichols| Tampa| FL| 19 Cindy| Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22(3 rows)Figure :My rstSELECT test=>SELECT* FROM friendWHEREage= 23;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23(1 row)Figure :My rstWHERE test=>SELECT* FROM friendWHEREage<= 22;firstname|lastname|city| state| age-----------------+----------------------+-----------------+-------+-----Mike| Nichols| Tampa| FL| 19 Sam| Jackson| Allentown| PA| 22(2 rows)Figure :More complexWHERE clause11test=>SELECT lastnameFROM friendWHEREage= 22;lastname----------------------Jackson (1 row)Figure :A singlecelltest=>SELECT city,stateFROM friendWHEREage >= 21;city| state-----------------+-------Denver| COAllentown| PA(2 rows)Figure :A blockofcellstest=>SELECT* FROM friendWHERE firstname= 'Sam';firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Sam| Jackson| Allentown| PA| 22(1 row)Figure :Comparingstring elds12test=>SELECT* FROM friend;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Mike| Nichols| Tampa| FL| 19 Cindy| Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22(3 rows)test=>INSERTINTO friendVALUES('Jim','Barnes','OceanCity', 'NJ',25);INSERT190561test=>SELECT* FROM friend;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Mike| Nichols| Tampa| FL| 19 Cindy| Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22 Jim| Barnes| OceanCity| NJ| 25(4 rows)test=>DELETEFROM friendWHERE lastname= 'Barnes'.

4 DELETE1test=>SELECT* FROM friend;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Mike| Nichols| Tampa| FL| 19 Cindy| Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22(3 rows)Figure :DELETE example13test=>UPDATE friendSETage= 20 WHERE firstname= 'Mike';UPDATE1test=>SELECT* FROM friend;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22 Mike| Nichols| Tampa| FL| 20(3 rows)Figure :My rstUPDATE test=>SELECT* FROM friendORDERBY state;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Mike| Nichols| Tampa| FL| 20 Sam| Jackson| Allentown| PA| 22(3 rows)Figure :UseofORDERBY test=>SELECT* FROM friendORDERBY ageDESC;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22 Mike| Nichols| Tampa| FL| 20(3 rows)Figure :ReverseORDERBY14test=>SELECT* FROM friendWHEREage>= 21 ORDERBY firstname.

5 Firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22(2 rows)Figure :UseofORDERBYandWHEREC hapter4 CustomizingQueriesCategoryTypeDescriptio ncharacterstringCHAR(length)blank-padded string, xedstoragelengthVARCHAR(length)variables toragelengthnumberINTEGER integer, +/ 2billionrangeFLOAT oatingpointnumber, 15-digitprecisionNUMERIC(precision,decim al)numberwithuser-de :Commondatatypes1516test=>CREATETABLE alltypes(test(>stateCHAR(2),test(>nameCH AR(30),test(>childrenINTEGER,test(>dista nceFLOAT,test(>budgetNUMERIC(16,2),test( >bornDATE,test(>checkinTIME,test(>starte dTIMESTAMP test(>);CREATE test=>INSERTINTO alltypestest->VALUES(test(>'PA',test(>'H ildaBlairwood',test(>3,test(> ,test(> ,test(>'9/8/1974',test(>'9:00',test(>'07 /03/199610:30:00');INSERT190731test=>SEL ECT state,name,children,distance,budgetFROM alltypes;state|name| children| distance| budget-------+-------------------------- ------+----------+----------+---------PA | HildaBlairwood|3 | | (1 row)test=>SELECT born,checkin,startedFROM alltypes;born| checkin|started------------+----------+- -----------------------1974-09-08| 09:00:00| 1996-07-0310:30:00-04(1 row)test=>\xExpandeddisplayis >SELECT* FROM alltypes;-[ RECORD1 ]----------------------------state| PAname| HildaBlairwoodchildren| 3distance| | | 1974-09-08checkin| 09:00:00started| 1996-07-0310:30:00-04 Figure :Exampleofcommondatatypes17test=>INSERTI NTO friend(firstname,lastname,city,state)tes t->VALUES('Mark','Middleton','Indianapol is','IN');INSERT190741 Figure :Insertionofspeci ccolumns18test=>SELECT* FROM friendORDERBY ageDESC.)))))))))))))))))

6 Firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22 Mike| Nichols| Tampa| FL| 20 Mark| Middleton| Indianapolis| IN|(4 rows)test=>SELECT* FROM friendWHEREage> 0 ORDERBY ageDESC;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22 Mike| Nichols| Tampa| FL| 20(3 rows)test=>SELECT* FROM friendWHEREage<> 99 ORDERBY ageDESC;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Cindy | Anderson| Denver| CO| 23 Sam| Jackson| Allentown| PA| 22 Mike| Nichols| Tampa| FL| 20(3 rows)test=>SELECT* FROM friendWHEREageIS NULLORDERBY ageDESC;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Mark| Middleton| Indianapolis| IN|(1 row)Figure :NULL handling19test=>INSERTINTO friendtest->VALUES('Jack','Burger',NULL, NULL,27);INSERT190751test=>SELECT* FROM friendWHERE city= state;firstname| lastname| city| state| age-----------+----------+------+------- +-----(0 rows)Figure :ComparisonofNULL elds20test=>CREATETABLE nulltest(nameCHAR(20),spouseCHAR(20));CR EATE test=>INSERTINTO nulltestVALUES('Andy','');INSERT190861te st=>INSERTINTO nulltestVALUES('Tom',NULL);INSERT190871t est=>SELECT* FROM nulltestORDERBY name;name|spouse----------------------+- ---------------------Andy|Tom|(2 rows)test=>SELECT* FROM nulltestWHERE spouse= '';name|spouse----------------------+--- -------------------Andy|(1 row)test=>SELECT* FROM nulltestWHERE spouseIS NULL.

7 Name| spouse----------------------+--------Tom |(1 row)Figure :NULL valuesandblankstrings21test=>CREATETABLE account(test(>nameCHAR(20),test(>balance NUMERIC(16,2)DEFAULT0,test(>activeCHAR(1 )DEFAULT'Y',test(>createdTIMESTAMPDEFAUL TCURRENT_TIMESTAMP test(>);CREATE test=>INSERTINTO account(name)test->VALUES('FederatedBuil ders');INSERT191031test=>SELECT* FROM account;name| balance| active|created----------------------+--- ------+--------+------------------------ FederatedBuilders| | Y| 1998-05-3021:37:48-04(1 row)Figure :UsingDEFAULT valuestest=>SELECT firstnameAS buddyFROM friendORDERBY buddy;buddy-----------------CindyJackMar kMikeSam(5 rows)Figure :Controllingcolumnlabels22test=>SELECT1 + 3 AS total;total-------4(1 row)Figure :Computationusinga columnlabeltest=>-- a singlelinecommenttest=>/* a multilinetest*>comment*/Figure :Commentstyles23test=>DELETEFROM friend;DELETE6test=>INSERTINTO friendtest->VALUES('Dean','Yeager','Plym outh','MA',24);INSERT197441test=>INSERTI NTO friendtest->VALUES('Dick','Gleason','Oce anCity','NJ',19);INSERT197451test=>INSER TINTO friendtest->VALUES('Ned','Millstone','Ce darCreek','MD',27);INSERT197461test=>INS ERTINTO friendtest->VALUES('Sandy','Gleason','Oc eanCity','NJ',25);INSERT197471test=>INSE RTINTO friendtest->VALUES('Sandy','Weber','Bost on','MA',33);INSERT197481test=>INSERTINT O friendtest->VALUES('Victor','Tabor','Wil liamsport','PA',22);INSERT197491test=>SE LECT* FROM friendORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dean| Yeager| Plymouth| MA| 24 Dick| Gleason| OceanCity| NJ| 19 Ned| Millstone| CedarCreek| MD| 27 Sandy| Gleason| OceanCity| NJ| 25 Sandy| Weber| Boston| MA| 33 Victor| Tabor| Williamsport| PA| 22(6 rows)Figure :Newfriends24test=>SELECT* FROM friendtest->WHERE firstname= 'Sandy'ANDlastname= 'Gleason'.)))))

8 Firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Sandy | Gleason| OceanCity| NJ| 25(1 row)Figure :WHERE testforSandyGleasontest=>SELECT* FROM friendtest->WHERE state= 'NJ'OR state= 'PA'test->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dick| Gleason| OceanCity| NJ| 19 Sandy| Gleason| OceanCity| NJ| 25 Victor| Tabor| Williamsport| PA| 22(3 rows)Figure :FriendsinNewJerseyandPennsylvaniaCompar isonOperatorlessthan<lessthanorequal<=equal=greaterthanorequal>=greaterthan>notequal<>or!= :Comparisonoperators25test=>SELECT* FROM friendtest->WHERE firstname= 'Victor'ANDstate= 'PA'OR state= 'NJ'test->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dick| Gleason| OceanCity| NJ| 19 Sandy| Gleason| OceanCity| NJ| 25 Victor| Tabor| Williamsport| PA| 22(3 rows)Figure :IncorrectlymixingANDandORclausestest=>S ELECT* FROM friendtest->WHERE firstname= 'Victor'AND(state= 'PA'OR state= 'NJ')test->ORDERBY firstname.

9 Firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Victo r| Tabor| Williamsport| PA| 22(1 row)Figure :CorrectlymixingANDandORclausesCompariso nOperationbeginswithDLIKE'D%'containsaDL IKE'%D%'hasDinsecondpositionLIKE'_D%'beg inswithDandcontainseLIKE'D%e%'beginswith D, containse, thenfLIKE'D%e%f%'beginswithnon-DNOTLIKE' D%' :LIKE comparisons26test=>SELECT*test->FROM friendtest->WHEREage >= 22 ANDage<= 25test->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dean| Yeager| Plymouth| MA| 24 Sandy| Gleason| OceanCity| NJ| 25 Victor| Tabor| Williamsport| PA| 22(3 rows)test=>SELECT*test->FROM friendtest->WHEREage BETWEEN22 AND25test->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dean| Yeager| Plymouth| MA| 24 Sandy| Gleason| OceanCity| NJ| 25 Victor| Tabor| Williamsport| PA| 22(3 rows)Figure :Selectinga rangeofvaluestest=>SELECT* FROM friendtest->WHERE firstnameLIKE'D%'test->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dean| Yeager| Plymouth| MA| 24 Dick| Gleason| OceanCity| NJ| 19(2 rows)Figure :FirstnamebeginswithD27 ComparisonOperatorregularexpression regularexpression,case-insensitive *notequaltoregularexpression!

10 Notequaltoregularexpression,case-insensi tive! * :RegularexpressionoperatorsTestSpecialCh aractersstart end$ [ccc]setofcharactersnotequal[ ccc]rangeofcharacters[c-c]rangeofcharact ersnotequal[ c-c]zero oroneofpreviouscharacter?zero ormultipleofpreviouscharacters*oneormult ipleofpreviouscharacters+ORoperator| :Regularexpressionspecialcharacters28 TestOperationbeginswithD ' D'containsD 'D'Dinsecondposition ' .D'beginswithDandcontainse ' D.*e'beginswithD, containse, andthenf ' D.*e.*f'containsA,B,C, orD '[A-D]'or '[ABCD]'containsAora * 'a'or '[Aa]'doesnotcontainD! 'D'doesnotbeginwithD! ' D'or ' [ D]'beginswithD,withoneoptionalleadingspa ce ' ?D'beginswithD, withoptionalleadingspaces ' *D'beginswithD,withatleastoneleadingspac e ' +D'endswithG, withoptionaltrailingspaces 'G *$' :Examplesofregularexpressions29test=>SEL ECT* FROM friendtest->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Dean| Yeager| Plymouth| MA| 24 Dick| Gleason| OceanCity| NJ| 19 Ned| Millstone| CedarCreek| MD| 27 Sandy| Gleason| OceanCity| NJ| 25 Sandy| Weber| Boston| MA| 33 Victor| Tabor| Williamsport| PA| 22(6 rows)test=>-- firstnamebeginswith'S'test=>SELECT* FROM friendtest->WHERE firstname ' S'test->ORDERBY firstname;firstname|lastname|city| state| age-----------------+------------------- ---+-----------------+-------+-----Sandy | Gleason| OceanCity| NJ| 25 Sandy| Weber| Boston| MA| 33(2 rows)test=>-- f