book

PostgreSQL: Up and Running, 3rd Edition

by Regina O. Obe, Leo S. Hsu

October 2017

Intermediate to advanced

314 pages

8h 13m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

AudienceFor More Information on PostgreSQLCode and Output FormattingConventions Used in This BookUsing Code ExamplesO’Reilly SafariHow to Contact Us
Why PostgreSQL?Why Not PostgreSQL?Where to Get PostgreSQLAdministration ToolspsqlpgAdminphpPgAdminAdminerPostgreSQL Database ObjectsWhat’s New in Latest Versions of PostgreSQL?Why Upgrade?Features Introduced in PostgreSQL 10Features Introduced in PostgreSQL 9.6Features Introduced in PostgreSQL 9.5Features Introduced in PostgreSQL 9.4Database DriversWhere to Get HelpNotable PostgreSQL Forks
Configuration FilesMaking Configurations Take EffectThe postgresql.conf FileThe pg_hba.conf FileManaging ConnectionsCheck for Queries Being BlockedRolesCreating Login RolesCreating Group RolesDatabase CreationTemplate DatabasesUsing SchemasPrivilegesTypes of PrivilegesGetting StartedGRANTDefault PrivilegesPrivilege IdiosyncrasiesExtensionsInstalling ExtensionsCommon ExtensionsBackup and RestoreSelective Backup Using pg_dumpSystemwide Backup Using pg_dumpallRestoring DataManaging Disk Storage with TablespacesCreating TablespacesMoving Objects Among TablespacesVerboten PracticesDon’t Delete PostgreSQL Core System Files and BinariesDon’t Grant Full OS Administrative Privileges to the Postgres System Account (postgres)Don’t Set shared_buffers Too HighDon’t Try to Start PostgreSQL on a Port Already in Use
Environment VariablesInteractive versus Noninteractive psqlpsql CustomizationsCustom PromptsTiming ExecutionsAutocommit CommandsShortcutsRetrieving Prior Commandspsql GemsExecuting Shell CommandsWatching StatementsRetrieving Details of Database ObjectsCrosstabsDynamic SQL ExecutionImporting and Exporting Datapsql Importpsql ExportCopying from or to ProgramBasic Reporting
Getting StartedOverview of FeaturesConnecting to a PostgreSQL ServerNavigating pgAdminpgAdmin FeaturesAutogenerating Queries from Table DefinitionsAccessing psql from pgAdmin3Editing postgresql.conf and pg_hba.conf from pgAdmin3Creating Database Assets and Setting PrivilegesImport and ExportBackup and RestorepgScriptGraphical ExplainJob Scheduling with pgAgentInstalling pgAgentScheduling JobsHelpful pgAgent Queries
NumericsSerialsGenerate Series FunctionTextualsString FunctionsSplitting Strings into Arrays, Tables, or SubstringsRegular Expressions and Pattern MatchingTemporalsTime Zones: What They Are and Are NotDatetime Operators and FunctionsArraysArray ConstructorsUnnesting Arrays to RowsArray Slicing and SplicingReferencing Elements in an ArrayArray Containment ChecksRange TypesDiscrete Versus Continuous RangesBuilt-in Range TypesDefining RangesDefining Tables with RangesRange OperatorsJSONInserting JSON DataQuerying JSONOutputting JSONBinary JSON: jsonbEditing JSONB dataXMLInserting XML DataQuerying XML DataFull Text SearchFTS ConfigurationsTSVectorsTSQueriesUsing Full Text SearchRanking ResultsFull Text StrippingFull Text Support for JSON and JSONBCustom and Composite Data TypesAll Tables Are Custom Data TypesBuilding Custom Data TypesComposites and NULLsBuilding Operators and Functions for Custom Types
TablesBasic Table CreationInherited TablesPartitioned TablesUnlogged TablesTYPE OFConstraintsForeign Key ConstraintsUnique ConstraintsCheck ConstraintsExclusion ConstraintsIndexesPostgreSQL Stock IndexesOperator ClassesFunctional IndexesPartial IndexesMulticolumn Indexes
ViewsSingle Table ViewsUsing Triggers to Update ViewsMaterialized ViewsHandy ConstructionsDISTINCT ONLIMIT and OFFSETShorthand CastingMultirow InsertILIKE for Case-Insensitive SearchANY Array SearchSet-Returning Functions in SELECTRestricting DELETE, UPDATE, and SELECT from Inherited TablesDELETE USINGReturning Affected Records to the UserUPSERTs: INSERT ON CONFLICT UPDATEComposite Types in QueriesDollar QuotingDOFILTER Clause for AggregatesPercentiles and ModeWindow FunctionsPARTITION BYORDER BYCommon Table ExpressionsBasic CTEsWritable CTEsRecursive CTELateral JoinsWITH ORDINALITYGROUPING SETS, CUBE, ROLLUP
Anatomy of PostgreSQL FunctionsFunction BasicsTriggers and Trigger FunctionsAggregatesTrusted and Untrusted LanguagesWriting Functions with SQLBasic SQL FunctionWriting SQL Aggregate FunctionsWriting PL/pgSQL FunctionsBasic PL/pgSQL FunctionWriting Trigger Functions in PL/pgSQLWriting PL/Python FunctionsBasic Python FunctionWriting PL/V8, PL/CoffeeScript, and PL/LiveScript FunctionsBasic FunctionsWriting Aggregate Functions with PL/V8Writing Window Functions in PL/V8
EXPLAINEXPLAIN OptionsSample Runs and OutputGraphical OutputsGathering Statistics on StatementsWriting Better QueriesOverusing Subqueries in SELECTAvoid SELECT *Make Good Use of CASEUsing FILTER Instead of CASEParallelized QueriesWhat Does a Parallel Query Plan Look Like?Parallel ScansParallel JoinsGuiding the Query PlannerStrategy SettingsHow Useful Is Your Index?Table StatisticsRandom Page Cost and Quality of DrivesCaching

Replication OverviewReplication JargonEvolution of PostgreSQL ReplicationThird-Party Replication OptionsSetting Up Full Server ReplicationConfiguring the MasterConfiguring the Slaves for Full Server Cluster ReplicationInitiating the Streaming Replication ProcessReplicating Only Some Tables or Databases with Logical ReplicationForeign Data WrappersQuerying Flat FilesQuerying Flat Files as Jagged ArraysQuerying Other PostgreSQL ServersQuerying Other Tabular Formats with ogr_fdwQuerying Nonconventional Data Sources
Windows and Desktop LinuxCentOS, Fedora, Red Hat, Scientific LinuxDebian, UbuntuFreeBSDmacOS
Database Backup Using pg_dumpServer Backup: pg_dumpallDatabase Restore: pg_restorepsql Interactive Commandspsql Noninteractive Commands

Content preview from PostgreSQL: Up and Running, 3rd Edition

Chapter 1. The Basics

PostgreSQL is an extremely powerful piece of software that introduces features you may not have seen before. Some of the features are also present in other well-known database engines, but under different names. This chapter lays out the main concepts you should know when starting to attack PostgreSQL documentation, and mentions some related terms in other databases.

We begin by pointing you to resources for downloading and installing PostgreSQL. Next, we provide an overview of indispensable administration tools followed by a review of PostgreSQL nomenclature. PostgreSQL 10 was recently released. We’ll highlight some of the new features therein. We close with resources to turn to when you need additional guidance and to submit bug reports.

Why PostgreSQL?

PostgreSQL is an enterprise-class relational database management system, on par with the very best proprietary database systems: Oracle, Microsoft SQL Server, and IBM DB2, just to name a few. PostgreSQL is special because it’s not just a database: it’s also an application platform, and an impressive one at that.

PostgreSQL is fast. In benchmarks, PostgreSQL either exceeds or matches the performance of many other databases, both open source and proprietary.

PostgreSQL invites you to write stored procedures and functions in numerous programming languages. In addition to the prepackaged languages of C, SQL, and PL/pgSQL, you can easily enable support for additional languages such as PL/Perl, PL/Python, PL/V8 (aka ...