PostgreSQL

Korry Douglas is the Director of Research and Development for Appx Software. Over the last two decades he has worked on the design and implementation of an umber of high-level languages and development environments, and his work with a variety of database products (Oracle, Sybase, SQL Server, PostgreSQL, MySQL and mSQL) given him a unique understanding of the commonalities of, and differences between, databases.

• Introduction.PostgreSQL FeaturesWhat Versions Does This Book Cover?Who Is This Book For?What Topics Does This Book Cover?What's New in the Second Edition?I. GENERAL POSTGRESQL USE.1. Introduction to PostgreSQL and SQL.A Sample DatabaseBasic Database TerminologyPrerequisitesInstalling PostgreSQL Using an RPMConnecting to a DatabaseA (Very) Simple QueryCreating TablesViewing Table DescriptionsAdding New Records to a TableUsing the INSERT CommandUsing the COPY CommandInstalling the Sample DatabaseRetrieving Data from the Sample DatabaseSELECT ExpressionSELECT  FROM TableSELECT Single-Column FROM TableSELECT Column-List FROM TableSELECT Expression-List FROM TableSelecting Specific RowsThe CASE ExpressionFormatting Column ResultsMatching PatternsAggregatesCOUNT()SUM()AVG()MIN() and MAX()Other Aggregate FunctionsGrouping ResultsMulti-Table JoinsJoin TypesUPDATEDELETEA (Very) Short Introduction to Transaction ProcessingCreating New Tables Using CREATE TABLE...ASUsing VIEWSummary2. Working with Data in PostgreSQL.NULL ValuesCharacter ValuesSyntax for Literal ValuesSupported OperatorsNumeric ValuesSize, Precision, and Range-of-ValuesSyntax for Literal ValuesSupported OperatorsDate/Time ValuesSyntax for Literal ValuesSupported OperatorsBoolean (Logical) ValuesSize and Valid ValuesSyntax for Literal ValuesSupported OperatorsGeometric Data TypesSyntax for Literal ValuesSizes and Valid ValuesSupported OperatorsObject IDs (OID)Syntax for Literal ValuesSize and Valid ValuesSupported OperatorsBLOBsSyntax for Literal ValuesSupported OperatorsLarge-ObjectsNetwork Address Data TypesMACADDRCIDRINETSyntax for Literal ValuesSupported OperatorsSequencesArraysColumn ConstraintsNULL/NOT NULLUNIQUEPRIMARY KEYREFERENCESCHECK()Expression Evaluation and Type ConversionCreating Your Own Data TypesRefining Data Types with CREATE DOMAINCreating and Using Composite TypesSummary3. PostgreSQL SQL Syntax and Use.PostgreSQL Naming RulesThe Importance of the COMMENT CommandCreating, Destroying, and Viewing DatabasesTablespacesCreating New DatabasesDropping a DatabaseViewing DatabasesCreating New TablesTemporary TablesTable ConstraintsDropping TablesInheritanceALTER TABLEAdding Indexes to a TableTradeoffsCreating an IndexFunctional Indexes and Partial IndexesCreating Indexes on Array ValuesIndexes and TablespacesGetting Information About Databases and TablesTransaction ProcessingPersistenceTransaction IsolationMulti-Versioning and LockingSummary4. Performance.How PostgreSQL Organizes DataPage CachingSummaryGathering Performance InformationDead TuplesIndex PerformanceUnderstanding How PostgreSQL Executes a QueryEXPLAINSeq ScanIndex ScanSortUniqueLIMITAggregateAppendResultNested LoopMerge JoinHash and Hash JoinGroupSubquery Scan and SubplanTid ScanMaterializeSetop (Intersect, Intersect All, Except, Except All)Execution Plans Generated by the PlannerThe ARC Buffer ManagerTable StatisticsPerformance TipsII. PROGRAMMING WITH POSTGRESQL.5. Introduction to PostgreSQL Programming.Server-Side ProgrammingPL/pgSQLOther Procedural Languages Supported by PostgreSQLExtending PostgreSQL Using External LanguagesClient-Side APIsGeneral Structure of Client ApplicationsConnection PropertiesLISTEN/NOTIFYChoosing an Application EnvironmentServer-Side CodeClient-Side CodeMixing Server-Side and Client-Side CodeSummary6. Extending PostgreSQL.Extending the PostgreSQL Server with Custom FunctionsReturning Multiple Values from an Extension FunctionThe PostgreSQL SRF InterfaceReturning Complete Rows from an Extension FunctionExtending the PostgreSQL Server with Custom Data TypesInternal and External FormsDefining a Simple Data Type in PostgreSQLDefining the Data Type in CDefining the Input and Output Functions in CDefining the Input and Output Functions in PostgreSQLDefining the Data Type in PostgreSQLIndexing Custom Data TypesSummary7. PL/pgSQL.Installing PL/pgSQLLanguage StructureQuoting Embedded StringsCREATE FUNCTIONDROP FUNCTIONFunction BodyCommentsVariablesPL/pgSQL Statement TypesCursorsFETCHParameterized CursorsCursor ReferencesTriggersTRIGGER Return ValuesTRIGGER Function ArgumentsPolymorphic FunctionsPL/pgSQL and SecuritySummary8. The PostgreSQL C API-libpq.PrerequisitesClient 1-Connecting to the ServerCompiling the ClientIdentifying the ServerClient 2-Adding Error CheckingViewing Connection AttributesClient 3-Simple Processing-PQexec() and PQprint()Results Returned by PQexec()The Prepare/Execute ModelClient 4-An Interactive Query ProcessorProcessing Multiple Result SetsAsynchronous ProcessingSummary9. A Simpler C API-libpgeasy.PrerequisitesClient 1-Connecting to the ServerClient 2-Adding Error CheckingClient 3-Processing QueriesWorking with Binary CursorsByte Ordering and NULL ValuesClient 4-An Interactive Query ProcessorSummary10. The New PostgreSQL C++ API-libpqxx.PrerequisitesClient 1-Connecting to the ServerUsing pqxx-config to Create a Simple Makefileconnection Member FunctionsClient 2-Adding Error CheckingOther Exceptions Thrown by libpqxxHandling Informational/Warning Messages with Notice Processor ObjectsClient 3-Processing QueriesWorking with TransactionsWorking with Result SetsWorking with Large-ObjectsLISTEN/NOTIFYset_variable() and get_variable()Client 4-Working with transactorsSummary11. Embedding SQL Commands in C Programs-ecpg.PrerequisitesClient 1-Connecting to the ServerThe ecpg PreprocessorConnection StringsClient 2-Adding Error CheckingThe sqlca StructureClient 3-Processing SQL Commandsecpg Data TypesClient 4-An Interactive Query ProcessorSummary12. Using PostgreSQL from an ODBC Client Application.ODBC Architecture OverviewThe ODBC Client ApplicationThe ODBC Driver ManagerThe ODBC DriverThe ODBC-Compliant DatabaseThe Data SourceSetting Up a Data Source on Unix SystemsSetting Up a Data Source in WindowsDatasource Connection PropertiesPrerequisitesClient 1-Connecting to the ServerClient 2-Adding Error CheckingClient 3-Processing QueriesClient 4-An Interactive Query ProcessorSummaryResources13. Using PostgreSQL from a Java Client Application.JDBC Architecture OverviewThe JDBC DriverManagerThe JDBC DriverThe JDBC-Compliant DatabasePrerequisitesClient 1-Connecting to the ServerJDBC URLsClient 2-Adding Error CheckingJNDI and the DataSource ClassClient 3-Processing QueriesStatement ClassesMetadataClient 4-An Interactive Query ProcessorSummary14. Using PostgreSQL with Perl.DBI Architecture OverviewThe DBIThe DBD DriverThe DBI-Compliant DatabasePrerequisitesClient 1-Connecting to the ServerDBI URLsClient 2-Adding Error CheckingClient 3-Processing QueriesThe Prepare/Execute ModelMetadata and Result Set ProcessingOther Statement and Database Handle AttributesClient 4-An Interactive Query ProcessorSummary15. Using PostgreSQL with PHP.PHP Architecture OverviewPrerequisitesClient 1-Connecting to the ServerClient 2-Adding Error CheckingClient 3-Query ProcessingOther Ways to Retrieve Result Set ValuesMetadata AccessClient 4-An Interactive Query ProcessorOther FeaturesSummary16. Using PostgreSQL with Tcl and Tcl/Tk.PrerequisitesClient 1-Connecting to the ServerMaking the Connection Dialog ReusableClient 2-Query ProcessingResult Set ProcessingClient 3-An Interactive Query ProcessorThe libpgtcl Large-Object APISummary17. Using PostgreSQL with Python.Python/PostgreSQL Interface ArchitecturePrerequisitesClient 1-Connecting to the ServerClient 2-Adding Error CheckingClient 3-Query ProcessingClient 4-An Interactive Command ProcessorSummary18. Npgsql: The .NET Data Provider.PrerequisitesPreparing Visual StudioUnderstanding the ADO.NET Class HierarchyCreating an Npgsql-enabled VB ProjectClient 1-Connecting to the ServerClient 2-An Interactive Query ProcessorClient 3-Updating the Database with a DataSetClient 4-A More Robust Query ProcessorClient 5-Using a Typed DataSetCreating a Typed DataSetSummary19. Other Useful Programming Tools.PL/Java-Writing Stored Procedures in JavaInstalling PL/JavaWriting a Simple PL/Java FunctionAccessing the Database from a PL/Java FunctionReturning Multiple Results from a PL/Java FunctionWriting PL/Java Trigger FunctionsAdding Install/Uninstall Commands to a Jar Filepgcurl-Web-enabling Your PostgreSQL Serverpgbash-Writing PostgreSQL-enabled Shell ScriptsIII. POSTGRESQL ADMINISTRATION.20. Introduction to PostgreSQL Administration.SecurityUser AccountsBackup and RestoreServer Startup and ShutdownRunning PostgreSQL on a Windows HostTuningInstalling UpdatesLocalizationSummary21. PostgreSQL Administration.Roadmap (Where's All My Stuff?)Installing PostgreSQLUnix/LinuxWindowsManaging DatabasesCreating a New ClusterCreating a New DatabaseRoutine MaintenanceLogfile RotationThe PostgreSQL BGWRITER ProcessManaging User AccountsCREATE USERManaging GroupsConfiguring Your PostgreSQL Runtime EnvironmentFile LocationsSecurity-Related ParametersConnection-Related ParametersOperational ParametersWrite-Ahead Log ParametersOptimizer ParametersDebugging/Logging ParametersPerformance StatisticsPer-session ParametersMiscellaneous ParametersRead-only ParametersArranging for PostgreSQL Startup and ShutdownUsing pg_ctlShutdown ModesConfiguring PostgreSQL Startup on Unix/Linux HostsBacking Up and Copying DatabasesUsing pg_dumpUsing pg_dumpallUsing pg_restorePoint-in-time RecoverySummary22. Internationalization and Localization.Locale SupportEnabling Locale SupportEffects of Locale SupportPostgreSQL Locale SummaryMulti-Byte Character SetsEncodings Supported by PostgreSQLEnabling Multi-Byte SupportSelecting an EncodingClient/Server TranslationSummary23. Security.Securing the PostgreSQL Data FilesSecuring PostgreSQL Data Files in WindowsSecuring Network Accesslocal Connectionshost, hostssl, and hostnossl ConnectionsThe trust Authentication MethodThe ident Authentication MethodThe password Authentication MethodThe crypt Authentication MethodThe md5 Authentication MethodThe pam Authentication MethodThe krb4 and krb5 Authentication MethodsThe reject Authentication MethodSecuring TablesSecuring FunctionsSummary24. Replicating PostgreSQLData with Slony.OverviewRequirementsCreating a Replication ClusterStarting the Replication DaemonsCreating a Replication SetSubscribing to a Replication SetCopying Table and Sequence DefinitionsCreating a SubscriberChanging the Cluster Topology (Re-mastering and Failover)Summary25. Contributed Modules.Exchanging PostgreSQL Data with XMLXPath QueriesConverting XML Data with XSLTUsing Full-text SearchSearching Multiple ColumnsSimplifying tsearch2 with Customized FunctionsSearching for PhrasesConfiguring tsearch2Index.