book

PostgreSQL

by Korry Douglas, Susan Douglas

February 2003

Intermediate to advanced

816 pages

18h 38m

English

Sams

Read now

Unlock full access

Copyright
About the Authors
About the Technical Reviewers
About the Development Editor
Acknowledgments
We Want to Hear from You
Reader Services
Preface
Introduction
PostgreSQL FeaturesWhat Versions Does This Book Cover?Who Is This Book For?What Topics Does This Book Cover?
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 RowsSELECT [ALL | DISTINCT | DISTINCT ON]The WHERE ClauseNULL ValuesThe ORDER BY ClauseLIMIT and OFFSETFormatting Column ResultsWorking with Date ValuesMatching PatternsLIKE and NOT LIKEPattern Matching with Regular ExpressionsAggregatesCOUNT()SUM()AVG()MIN() and MAX()Other Aggregate FunctionsGrouping ResultsMulti-Table JoinsJoin TypesUPDATEDELETEA (Very) Short Introduction to Transaction ProcessingCreating New Tables Using CREATE TABLE...ASUsing VIEWSummary
2. 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 TypesSummary
3. PostgreSQL SQL Syntax and Use
PostgreSQL Naming RulesCreating, Destroying, and Viewing DatabasesCreating New DatabasescreatedbDropping a DatabaseViewing DatabasesCreating New TablesTemporary TablesTable ConstraintsDropping TablesInheritanceALTER TABLEAdding Indexes to a TableTradeoffsCreating an IndexFunctional Indexes and Partial IndexesGetting Information About Databases and TablesTransaction ProcessingPersistenceTransaction IsolationMulti-Versioning and LockingSummary
4. 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)Table StatisticsPerformance Tips
II. 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 ApplicationsChoosing an Application EnvironmentServer-Side CodeClient-Side CodeMixing Server-Side and Client-Side CodeSummary
6. Extending PostgreSQL
Extending the PostgreSQL Server with Custom FunctionsReturning Multiple Values 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 PostgreSQLSummary
7. PL/pgSQL
Installing PL/pgSQLLanguage StructureQuoting Embedded StringsCREATE FUNCTIONDROP FUNCTIONFunction BodyCommentsVariablesFunction ParametersDECLAREPseudo Data Types—%TYPE, %ROWTYPE, OPAQUE, and RECORDALIAS and RENAMEFOR Loop IteratorPL/pgSQL Statement TypesAssignmentSELECT INTOConditional ExecutionLoop ConstructsEXITLabels—EXIT Targets and Name QualifiersRETURNPERFORMEXECUTEGET DIAGNOSTICSError HandlingRAISECursorsFETCHParameterized CursorsCursor ReferencesTriggersSummary
8. 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()Binary CursorsClient 4—An Interactive Query ProcessorProcessing Multiple Result SetsAsynchronous ProcessingSummary
9. 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 ProcessorSummary
10. The PostgreSQL C++ API—libpq++
PrerequisitesClient 1—Connecting to the ServerClient 2—Adding Error CheckingClient 3—Processing QueriesWorking with TransactionsWorking with CursorsWorking with Large-ObjectsClient 4—An Interactive Query ProcessorSummary
11. 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 ProcessorSummary
12. 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 SystemsInstalling unixODBC and the PostgreSQL ODBC DriverSetting Up a Data Source in WindowsPrerequisitesClient 1—Connecting to the ServerClient 2—Adding Error CheckingClient 3—Processing QueriesClient 4—An Interactive Query ProcessorSummaryResources
13. 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 CheckingClient 3—Processing QueriesStatement ClassesPreparedStatementCallableStatementMetadataClient 4—An Interactive Query ProcessorSummary
14. 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 ProcessorSummary
15. 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 FeaturesSummary
16. Using PostgreSQL with Tcl and Tcl/Tk
PrerequisitesPostgreSQL-Related Tcl/Tk ComponentsClient 1—Connecting to the ServerMaking the Connection Dialog ReusableClient 2—Query ProcessingResult Set ProcessingClient 3—An Interactive Query ProcessorThe libpgtcl Large-Object APISummary
17. Using PostgreSQL with Python
Python/PostgreSQL Interface ArchitecturePrerequisitesClient 1—Connecting to the ServerClient 2—Adding Error CheckingClient 3—Query ProcessingClient 4—An Interactive Command ProcessorSummary
III. PostgreSQL Administration
18. Introduction to PostgreSQL Administration
SecurityUser AccountsBackup and RestoreServer Startup and ShutdownTuningInstalling UpdatesLocalizationSummary
19. PostgreSQL Administration
Roadmap (Where's All My Stuff?)Installing PostgreSQLUnix/LinuxFrom BinariesFrom SourceDownloading and Unpacking the Source CodeConfiguring the Source CodeCompiling the Source CodeInstalling the Compiled CodeCompleting the Installation ProcessWindowsFrom BinariesFrom SourceCompleting the Installation ProcessManaging DatabasesCreating a New ClusterCreating a New DatabaseRoutine MaintenanceManaging Tables (cluster and vacuum)Managing IndexesManaging User AccountsCREATE USERSYSIDPrivileges (CREATEDB and CREATEUSER)Group Membership (IN GROUP)PASSWORD and Password ExpirationcreateuserALTER USERDROP USERGRANT and REVOKEManaging GroupsCREATE GROUPALTER GROUPDROP GROUPConfiguring Your PostgreSQL Runtime EnvironmentConnection-Related ParametersOperational ParametersOptimizer ParametersDebugging/Logging ParametersPerformance StatisticsMiscellaneous ParametersArranging for PostgreSQL Startup and ShutdownUsing pg_ctlShutdown ModesConfiguring PostgreSQL Startup on Unix/Linux HostsConfiguring PostgreSQL as a Windows ServiceBacking Up and Copying DatabasesUsing pg_dumpUsing pg_dumpallUsing pg_restoreSummary
20. Internationalization and Localization
Locale SupportEnabling Locale SupportEffects of Locale SupportMultibyte Character SetsEncodings Supported by PostgreSQLEnabling Multibyte SupportSelecting an EncodingClient/Server TranslationSummary
21. Security
Securing the PostgreSQL Data FilesSecuring Network Accesslocal Connectionshost and hostssl ConnectionsThe trust Authentication MethodThe ident Authentication MethodThe password Authentication MethodDefining pg_shadow PasswordsDefining Passwords for Flat Password FilesThe crypt Authentication MethodThe md5 Authentication MethodThe pam Authentication MethodThe krb4 and krb5 Authentication MethodsThe reject Authentication MethodSecuring TablesSummary

Content preview from PostgreSQL

Chapter 12. Using PostgreSQL from an ODBC Client Application

ODBC (open database connectivity) is an API (application programming interface) that provides an application with a consistent database interface. To understand the architecture of ODBC, it helps to understand the problem that ODBC was designed to solve.

Let's say that you are an independent software vendor and you have just finished developing an accounting package that you intend to sell to as many users as possible. Your accounting application was designed to store its data in Sybase. Your original application uses the Sybase OpenClient interface to interact with the database. One day, a potential customer tells you that he is very interested in buying your application, but his corporate ...

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: 0735712573Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

PostgreSQL

by Korry Douglas, Susan Douglas

Chapter 12. Using PostgreSQL from an ODBC Client Application

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.