book

PostgreSQL Developer's Handbook

by Ewald Geschwinde, Hans-Jürgen Schönig

December 2001

Intermediate to advanced

768 pages

14h 36m

English

Sams

Read now

Unlock full access

Copyright
Dedication
About the Authors
Ewald GeschwindeHans-Jürgen Schönig
Acknowledgments
Tell Us What You Think!
Introduction
I. PostgreSQL
1. About PostgreSQL
LicenseThe Basic Concepts of PostgreSQLSoftware ArchitectureThe PostmasterThe BackendThe FrontendLimitations of PostgreSQL
2. Preparing
Hardware RequirementsSoftware RequirementsInstalling PostgreSQLInstalling on UNIX SystemsInstalling on RPM-Based SystemsInstalling on Debian-Based SystemsInstalling the Source CodeInstalling on WindowsStarting the Server and Creating DatabasesUsing a Startup ScriptUsing pg_ctl DirectlyCreating DatabasesLoginThe User Interface
3. An Introduction to SQL
Relational Databases and Their ComponentsTables and KeysPrimary KeysForeign KeysDatatypesIndicesB-TreesR-TreesHash Access MethodsSequencesTriggersObjectsClassesInheritanceFunction OverloadingViewsProceduresAggregate Functions and Aggregate ExpressionsAggregate FunctionsAggregate ExpressionsBuilding a DatabaseBuilding Simple Data StructuresCreating TablesUsing Temporary TablesThe DROP TABLE CommandThe ALTER TABLE CommandCreating and Dropping IndicesInserting DataImporting Large Datafiles and Using Pipes on UNIX SystemsRetrieving DataSimple QueriesPerforming Simple CalculationsJoining TablesSelf-Joins and AliasesAggregation FunctionsSubqueriesUsing SELECT and INSERTPattern Matching and Regular Expressions in SQLUsing SELECT INTO StatementsFormatting the Output and CastingGenerating HTMLUpdating and Deleting DataWriting UPDATE QueriesUsing DELETEWriting Advanced SQL CodeMasqueradingWorking with ArraysArrays and the COPY CommandWorking with BLOBsUsing Network-Specific DatatypesWorking with Geo-DatapointlineboxpathpolygoncircleRetrieving Geo-Data and OperatorsCombining QueriesBuilding Complex Data Structures and Modeling TechniquesCreating and Dropping ViewsData Integrity with ConstraintsThe CHECK ConstraintUsing InheritanceModeling TechniquesThe Entity Relationship ModelNormalizationThe Waterfall Model
4. Understanding Transactions
How the Transaction Code Affects Daily WorkRollbacksConcurrent TransactionsTransaction IsolationLocking

5. PL/PGSQL
Why Use PL/pgSQL?Enabling a Database with PL/pgSQLThe Structure of PL/pgSQLWriting FunctionsInserting PL/pgSQL Functions into a DatabaseA Simple ExampleUsing Typecast OperationsSimple Flow of Control StructuresUsing IF/ELSEUsing LOOP/EXIT, WHILE, and FOR LoopsPL/pgSQL also supports FOR loops:Using Functions in PL/pgSQLFunction OverloadingSELECT Statements and PL/pgSQL FunctionsWorking with SELECT and LoopsException HandlingMaking Functions More Independent from DatatypesWriting TriggersAutomatically Created VariablesOracle's PL/SQL and PL/pgSQL—Comparing Two WorldsThe Main DifferencesQuotingFunction OverloadingPostgreSQL and Database CursorsCreating and Replacing FunctionsMaking Life Easier with instrLocking TablesFunctions and Transactions
6. Database Administration
How to Configure PostgreSQLConfiguring the PostmasterPostgreSQL 7.1—The pg_options FilePostgreSQL 7.1—postgreSQL.confAdding Users and Managing User RightsA General OverviewAdding and Deleting UsersChanging User and Group AttributesUsing GRANT and REVOKEModifying DatabasesA General OverviewUsing ALTERA Short OverviewChanging NamesHow Changes Affect Objects Related to other ObjectsDropping ColumnsSystem Tablespg_attributepg_descriptionpg_grouppg_index and pg_indexespg_tablespg_triggerSecurity and Access RestrictionsUser AuthentificationOverviewSummary
7. Backup and Recovery
Common ErrorsFull Backup Versus Incremental BackupStarting Backups Using cronBackup HardwareBackup Strategies and Tools for PostgreSQLpg_dump and pg_dumploBackup ServerJukeboxes and Basic Tape OperationsSaving Large Amounts of Data on Multiple TapesJournalingSummary
8. Performance Tuning
Measuring PerformanceIndexes and PerformanceUsing EXPLAIN and Understanding the OptimizerUnderstanding Execution PlansVACUUMTuning SQLRewriting QueriesInfluencing the OptimizerCaching the Result of FunctionsSystem PerformanceThe Impact of I/O on System Performance and CPUsCLUSTERTuning the File SystemfsyncPostgreSQL on SMP MachinesThe Impact of Memory on System Performance and Restricted Access to System ResourcesWorking with Large Amounts of DataRegular Expressions and PerformanceSummary
9. Programming Interfaces
C/C++An Overview of C/C++ConnectingSimple Selects—An ExampleBinary Cursors and COPYEnvironment VariablesA Reference of the C InterfaceDatabase Connection FunctionsQuery Execution FunctionsAsynchronous Query ProcessingFastpathAsynchronous NotificationFunctions Related to the COPY CommandTracing FunctionsControl FunctionsWorking with Large ObjectsA Reference of the C++ InterfaceThe PgConnection ObjectThe PgDatabase ObjectAsynchronous NotificationEmbedded SQL C Preprocessor (ECPG)The Basic Concepts of ECPGA First ExampleError HandlingConnectingSimple Queries and Retrieving DataA Final ExamplePerlOverviewThe Pg ModuleOverviewConnectingCollecting Information about the BackendRunning QueriesRetrieving Information about the ResultWorking with BLOBsFinal WordThe DBI Interface and DBD for PostgreSQLOverviewInstallationConnectingWorking with DBIWorking with ArraysTransactionsErrors and Exception HandlingDebugging and MonitoringNotation and ConventionsDBI ProxiesPHPA Short History of PHPConnectingWorking with PHPErrors and Exception HandlingWorking with BLOBs and File UploadsDebuggingA Final ExampleJavaA short history of JavaJDBCWhat is JDBC?Components of a JDBC-Enabled SystemA Few Words about Java, SQL, and JDBC DriverInstalling JDBC under Linux RedHatConnectingSimple Queries and Retrieving DataAn Overview of the JDBC-APIJDBC and TransactionsException Handling with java.sql.SQLWarningPrepared CommandsStored ProceduresPostgreSQL Extensions to the JDBC APIA Few Words about JDBC ExtensionsfastpathGeometric ObjectsLarge ObjectsObject SerializationA Final ExamplePythonA Short Overview of PythonConnectingThe Database Wrapper Class DBTcL/TKThe History of TcLConnectingSimple ExamplesUsing PL/TclAn Overview of PL/TclSimple ExamplesFunction OverloadingAccessing a Database with PL/TclWriting Triggers with PL/TclBuilding a Simple Tcl/Tk Frontend for PostgreSQLODBCAn Overview of ODBCUsing ODBC with PostgreSQLODBC and Security
10. Extended PostgreSQL—Software for PostgreSQL
Contributed Softwarecube—A Self-Defined DatatypeInstalling cubeWorking with cubescube and IndicesWorking with fulltext IndicesThe Problem with Indices and Substring SearchingWorking with the fulltextindex PackageWorking with ISBN and ISSNPostgreSQL Large Object DumperBackupRecoveryBenchmarking with pgbenchThe seg moduleThe soundex CodeRemoving Lost Binary Objects from a Databasepg_controldataAdministration ToolsphpPgAdminInstalling phpPgAdminWorking with phpPgAdmin
II. Practical PostgreSQL Solutions
11. Importing Data and Working with Binary Data
Importing ASCII and Binary Data Using COPYWorking with ASCII and EBCDICAn Overview and a Short History of EBCDICAn Overview and a Short History of ASCIIUnderstanding BCD CodingTaking Care of Byte OrderThe ASCII—EBCDIC TablePerforming Simple EBCDIC—ASCII ConversionsSummary
12. Generating Multidimensional Results
Generating Multidimensional Results on the Database LevelCreating Multidimensional Arrays on the Application LevelSummary
13. Classification of Data—Real-World Examples
Working with SQLEFEU—The Perfect Tool for Data WarehousingAbout EFEUUsing PostgreSQL and Data MatricesSummary
14. Generating Flash with PostgreSQL and PHP
PHP's Flash InterfacesSimple Flash ExamplesAdding Data to FlashWorking with Geometric DatatypesUsing Action Script for Interactive ApplicationsA Final ExampleSummary
15. PostgreSQL Regression Tests
The Idea Behind Regression TestsRunning TestsEvaluating Regression TestsError MessagesLocal DifferencesDate and Time DifferencesFloating-Point DifferencesPolygon DifferencesOrdering Differences”Random” TestingSummary
16. Extending PostgreSQL's Core Features
Adding Functions and an Overview of Programming ConventionsWriting Simple SQL FunctionsWriting C FunctionsPostgreSQL's C DatatypesCalling Conventions for Functions Written in CRules for Writing C CodeAdding DatatypesAdding OperatorsAdding AggregatesChanging PostgreSQL's Rule SystemUsing Rules for Changing PostgreSQL's BehaviorUsing Rules in Combination with ViewsSummary
17. Date Calculations
SQL and DatesSQL, Dates, and Time Zonestimestamp with time zoneintervaldatetime [ without time zone ]Special Values and AbbreviationsPerforming Simple Date and Time Operations with SQLUsing Perl—The DateCalc ModuleAn OverviewThe Current CalendarBasic Operations Using DateCalcUsing DateCalc with PostgreSQLAnalyzing a Time SeriesSummary
18. Persistent Database Connections with PHP
Speed TestsDangerous Side EffectsTesting Persistent ConnectionsOpen Connections and Backend ProcessesPersistent Connections and TransactionsSummary
19. Using PostgreSQL and Microsoft Software over the Network
Connecting to MS OfficeSetting Up the ODBC DriverUsing Visual Basic to Connect to PostgreSQLThe First ContactA Simple ExampleUsing PostgreSQL with Microsoft OfficeAn Example DatabaseWorking with MS AccessWorking with MS ExcelWorking with MS WordUsing PostgreSQL and MS Office with Huge Amounts of DataSummary
20. Drawing Graphs on Unix Systems Using gnuplot and PostgreSQL
About gnuplotUsing PostgreSQL and gnuplotA Simple LaTex DocumentGeometric Datatypes and gnuplot—A Simple ExampleSummary

Overview

PostgreSQL is an object-relational database server that is widely considered to be the world¿s most advanced open-source database system. It is ANSI SQL-compatible, and it offers powerful features to enable more complex software design than would be possible with relational databases that are not object-oriented. PostgreSQL is extremely modular, it supports a large number of datatypes, and programming interfaces for PostgreSQL are available for all important programming languages, including C, Perl, Python, Tcl, Java, and PHP.

PostgreSQL Developer¿s Handbook provides a complete overview of the PostgreSQL database server and extensive coverage of its core features, including object orientation, PL/pgSQL, and the most important programming interfaces. The book introduces the reader to the language and syntax of PostgreSQL and then moves quickly into advanced programming topics.

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.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0672322609Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills