book

Practical PostgreSQL

by Joshua D. Drake, John C. Worsley

January 2002

Intermediate to advanced

640 pages

16h 39m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Commercial PostgreSQL ProductsOpen Source Versus Commercial ProductsThe Bottom LineCommercial SupportCommunity Support
Required Software PackagesOptional PackagesDisk Space
Step 1: Creating the “postgres” UserStep 2: Installing the PostgreSQL Source PackageStep 3: Configuring the Source TreeStep 4: Compiling the SourceStep 5: Regression TestingStep 6: Installing Compiled Programs and LibrariesStep 7: Setting Environment VariablesStep 8: Initializing and Starting PostgreSQLStep 9: Configuring the PostgreSQL SysV ScriptStep 10: Creating a Database
A Brief History of SQLSQL and Its PredecessorsSQL Standards
Understanding DatabasesUnderstanding Tables
The Anatomy of a SQL StatementToken Formatting ConsiderationsKeywords and IdentifiersQuoted identifiersWhen quotes are requiredIdentifier validityConstantsString constantsBit string constantsInteger constantsFloating-point constantsBoolean constantsSpecial Character SymbolsPunctuation symbolsOperator symbolsCommentsPutting It All Together
NULL ValuesBoolean ValuesCharacter TypesNumeric TypesThe numeric typeThe money typeThe serial typeDate and Time TypesBackward compatibilityDate conventionsTime conventionsTimestampsIntervalsBuilt-in date and time constantsGeometric TypesArraysArrays in tablesArray constantsType Coercion
System ColumnsObject IdentifiersPlanning Ahead
Starting psqlIntroduction to psql SyntaxExecuting QueriesEntering queries at the psql promptEditing the query buffer
Creating Tables with CREATE TABLECREATE TABLE syntaxCreating an example tableExamining a created tableAltering Tables with ALTER TABLEAdding columnsSetting and removing default valuesRenaming a tableRenaming columnsAdding constraintsChanging ownershipRestructuring Existing TablesRestructuring with CREATE TABLE ASRestructuring with CREATE TABLE and INSERT INTODestroying Tables with DROP TABLE
Inserting New ValuesInserting Values from Other Tables with SELECTCopying Values from External Files with COPYBinary formatThe difference between COPY and \copyCOPY TOCopying WITH OIDS
A Simple SELECTSpecifying Target ColumnsExpressions, Constants, and AliasesSelecting Sources with the FROM ClauseAliasing FROM SourcesRemoving Duplicate Rows with DISTINCTQualifying with the WHERE ClauseJoining Data Sets with JOINCross joinsInner and outer join syntaxInner joinsOuter joinsIntricate joinsGrouping Rows with GROUP BYSorting Rows with ORDER BYSetting Row Range with LIMIT and OFFSETComparing Sets with UNION, INTERSECT and EXCEPTUsing Case ExpressionsCreating Tables from Other Tables
Updating Entire ColumnsUpdating Several ColumnsUpdating from Several Sources
Creating a ViewApplying ViewsDestroying a view
Using OperatorsCharacter String OperatorsBasic comparisonString concatenationRegular expression matching operatorsNumeric OperatorsMathematical operatorsNumeric comparison operatorsNumeric comparison keywordsBinary operatorsLogical OperatorsUsing Operators with NULLOperator Precedence
Using FunctionsMathematical Functionsabs( )acos( )asin( )atan( )atan2( )cbrt( )ceil( )cos( )cot( )degrees( )exp( )floor( )ln( )log( )mod( )pi( )pow( )radians( )random( )round( )sin( )sqrt( )tan( )trunc( )Character String Functionsascii( )btrim( )char_length( )chr( )initcap( )length( )like( ) and ilike( )lower( )lpad( )ltrim( )octet_length( )position( )repeat( )rpad( )rtrim( )strpos( )substr( )substring( )to_ascii( )translate( )trim( )upper( )Date and Time Functionscurrent_datecurrent_timecurrent_timestampdate_part( )date_trunc( )extract( )isfinite( )now( )timeofday( )Type Conversion Functionsbitfromint4( )bittoint4( )to_char( ) with numbersto_char( ) with timestampsto_date( )to_number( )to_timestamp( )timestamp( )Aggregate FunctionsAggregate expressionsavg( )count( )max( )min( )stddev( )sum( )variance( )
Command Line OptionsSlash CommandsFormatting commandsInformation display commandsPostgreSQL and psql informative commandsInput and output commandsSystem commandsUsing External Files to Enter QueriesThe Readline and History LibrariesVariable SubstitutionAbout the psql PromptModifying the promptPrompt examples
Installation and Basic ConfigurationManaging UsersManaging GroupsCreating DatabasesCreating TablesAdding fields to a tableInserting and updating valuesDeleting valuesUsing QueriesManually designing a queryUsing the visual designerExecuting a queryModifying a queryCreating Functions
Creating an IndexUnique indicesIndex typesFunctional indicesDestroying an Index
Using ConstraintsColumn constraintsTable constraintsAdding a constraintRemoving a constraintInheritanceCreating a child tableUsing inherited tablesModifying inherited tables
Creating an Array ColumnInserting Values into Array ColumnsSelecting Values From Array ColumnsArray subscriptsArray slicesArray dimensionsUpdating Values in Array Columns
SequencesCreating a sequenceViewing a sequenceUsing a sequenceDestroying a sequenceTriggersCreating a triggerViewing a triggerRemoving a trigger
Using Transaction BlocksUsing CursorsDeclaring a cursorFetching from a cursorMoving a cursorClosing a cursor
Creating New FunctionsCreating SQL functionsCreating C functionsDestroying functionsCreating New OperatorsCreating an operatorOverloading an operatorDropping an operator
Password AuthenticationThe pg_hba.conf fileStructure of the pg_hba.conf fileExample pg_hba.conf entriesThe pg_ident.conf fileAuthentication Failure
Built-in SSLSSH/OpenSSHConfiguring and Using StunnelOpenSSLStunnelKnowing how to start StunnelRunning Stunnel in daemon modeRunning with inetdWrapping up
Using pg_ctlStarting PostgreSQL with pg_ctlStopping PostgreSQL with pg_ctlRestarting PostgreSQL with pg_ctlChecking status of PostgreSQL with pg_ctlUsing the SysV ScriptCalling postmaster Directly
Initializing a Database ClusterInitializing a Secondary Database Location
Creating a DatabaseUsing CREATE DATABASEUsing createdbRemoving a DatabaseUsing DROP DATABASEUsing dropdb
Using VACUUMUsing vacuumdbDocumenting a DatabaseUsing COMMENTRetrieving comments
Using pg_dumpUsing pg_dumpallRestoring a DatabaseUsing psql for plain text dumpsUsing pg_restore for tarred and compressed dumpsWhen to Backup and Restore DataWhen to backupWhen to restoreBacking Up the Filesystem
Viewing UsersCreating UsersCreating a user with the CREATE USER SQL commandCreating a user with the createuser scriptAltering UsersRemoving UsersRemoving users with the DROP USER SQL commandRemoving users with the dropuser operating system command
Creating and Removing GroupsCreating a groupRemoving a groupAssociating Users with GroupsAdding a user to a groupRemoving a user from a group
Understanding Access ControlGranting Privileges with GRANTRestricting Rights with REVOKEUsing Views for Access Control
Adding PL/pgSQL to Your DatabaseUsing psql to add PL/pgSQLUsing createlang to add PL/pgSQL
Code BlocksCommentsComment syntaxGood commenting styleStatements and ExpressionsStatementsExpressions
Data TypesDeclarationAssignmentArgument VariablesReturning VariablesAttributesThe %TYPE attributeThe %ROWTYPE attributeConcatenation
Conditional StatementsThe IF/THEN statementThe IF/THEN/ELSE statementThe IF/THEN/ELSE/IF statementLoopsThe basic loopThe WHILE loopThe FOR loopHandling Errors and ExceptionsCalling Functions
Basic JDBC UsageUsing Advanced JDBC FeaturesCallableStatementPreparedStatementResultSetMetaDataDatabaseMetaData
Content Inclusion and ManagementDirect SQL Methods and PostgreSQL ConnectivityFingerless
Installing LXPUsing lxpinstall.shManual installationNuts and Bolts: Configuring lxp.confGeneral settingsDatabase settings
LXP TagsLXP Regions
Naming ConventionsUsing Variables and ObjectsCGI ArgumentsCGI ArraysDirect SQL objectsGlobal LXP objects
Setting CookiesAccessing Cookie Values
Variable SubstitutionObject Variable Value SubstitutionEntity substitutionUsing <varparser>
The <if> and <ifnot> TagsUsing <if>Using <ifnot>Nesting logicUsing <ifcookie> and <ifnotcookie>The <else>, <elseif>, and <elseifnot> Tags
Including LXP FilesIncluding Flat FilesIncluding Token-Delimited FilesIncluding XML, RSS and RDF FilesIncluding External Content TypesIncluding SQL ContentSetting the database sourceAccessing column valuesAccessing SQL meta-dataSetting SQL object variables
SynopsisParametersResultsExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParameters & KeywordsResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionRestrictions and limitationsFile formattingExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionFunction attributesFunction overloadingExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionColumn indexFunctional indexOperators and operator classesExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionTransactionsMultiple signalsExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExamples
SynopsisParametersResultsDescriptionExample
SynopsisExamplesResultsDescriptionExample
SynopsisParametersResultsDescriptionExample
SynopsisParametersResultsDescriptionExample

Content preview from Practical PostgreSQL

Part IV. PostgreSQL Programming

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

PostgreSQL: Up and Running, 2nd Edition

Regina O. Obe, Leo S. Hsu

Learning PostgreSQL 11 - Third Edition

Christopher Travers, Andrey Volkov

Learn PostgreSQL

Luca Ferrari, Enrico Pirozzi

PostgreSQL High Performance Cookbook

Dinesh Kumar, Chitij Chauhan

Publisher Resources

ISBN: 9781449309770Supplemental Content Errata Page