PostgreSQL: Introduction and Concepts

Description

• Copyright 2001
• Edition: 1st

• Book
• ISBN-10: 0-201-70331-9
• ISBN-13: 978-0-201-70331-3

The most advanced, feature-rich SQL database server available, the open-source PostgreSQL system has rapidly become a key Internet technology. PostgreSQL: Introduction and Concepts, written by a founding member of the PostgreSQL Global Development Team, provides a much-needed tutorial and real-world guide to understanding and working with this complex yet essential system.

Assuming no previous knowledge of database systems, the book establishes a firm foundation of basic concepts and commands before turning to PostgreSQL's more advanced and innovative capabilities. It leads you step-by-step from your first database query through the complex queries needed to solve real-world database problems. The author not only presents proper query syntax, he goes beyond the mechanics to explore the value and use of these commands in working database applications.

You will read about such important topics as:

• Basic SQL commands for manipulating and updating a database
• Customizing queries
• SQL aggregates
• Joining tables
• Combining SELECTs and subqueries
• Transactions
• Performance
• Importing and exporting data
• Database query tools, including PSQL and PGACCESS
• PostgreSQL interfaces to C, C++, ODBC, JDBC, Perl, TCL/TK, and more
• Server-side programming and multi-user control
• Extending PostgreSQL with C
• PostgreSQL administration, including backups, troubleshooting, and access configuration
• Features unique to PostgreSQL

Throughout, the author highlights common pitfalls, offers tips to save you time and trouble, and provides many examples--all garnered from his extensive experience and inside knowledge. In addition, this resource-rich guide provides a copy of the official PostgreSQL reference manual. A companion web site, www.postgresql.org/docs/awbook.html, will contain updates, corrections, and links to other material.

0201703319B04062001



Sample Content

Table of Contents

List of Figures.


List of Tables.


Foreword.


Preface.


Acknowledgments.

Steering.

Major Developers.

Non-code Contributors.

History of POSTGRESQL.

Introduction.

University of California at Berkeley.

Development Leaves Berkeley.

POSTGRESQL Global Development Team.

Open Source Software.

Summary.

Issuing Database Commands.

Starting a Database Session.

Choosing an Interface.

Choosing a Database.

Starting a Session.

Controlling a Session.

Typing in the Query Buffer.

Displaying the Query Buffer.

Erasing the Query Buffer.

Getting Help.

Exiting a Session.

Summary.

Basic SQL Commands.

Relational Databases.

Creating Tables.

Adding Data with INSERT.

Viewing Data with SELECT.

Selecting Specific Rows with WHERE.

Removing Data with DELETE.

Modifying Data with UPDATE.

Sorting Data with ORDER BY.

Destroying Tables.

Summary.

Customizing Queries.

Data Types.

Quotes Inside Text.

Using NULL Values.

Controlling DEFAULT Values.

Column Labels.

Comments.

AND/OR Usage.

Range of Values.

LIKE Comparison.

Regular Expressions.

CASE Clause.

Distinct Rows.

Functions and Operators.

SET, SHOW, and RESET.

Summary.

SQL Aggregates.

Aggregates.

Using GROUP BY.

Using HAVING.

Query Tips.

Summary.

Joining Tables.

Table and Column References.

Joined Tables.

Creating Joined Tables.

Performing Joins.

Three- and Four-Table Joins.

Additional Join Possibilities.

Choosing a Join Key.

One-to-Many Joins.

Unjoined Tables.

Table Aliases and Self-joins.

Non-equijoins.

Ordering Multiple Parts.

Primary and Foreign Keys.

Summary.

Numbering Rows.

Object Identification Numbers (OIDs).

Object Identification Number Limitations.

Nonsequential Numbering.

Nonmodifiable.

Not Backed Up by Default.

Sequences.

Creating Sequences.

Using Sequences to Number Rows.

Serial Column Type.

Manually Numbering Rows.

Summary.

Combining SELECTs.

UNION, EXCEPT, and INTERSECT Clauses.

Subqueries.

Subqueries as Constants.

Subqueries as Correlated Values.

Subqueries as Lists of Values.

NOT IN and Subqueries with NULL Values.

Subqueries Returning Multiple Columns.

ANY, ALL, and EXISTS Clauses.

Summary.

Outer Joins.

Subqueries in Non-SELECT Queries.

UPDATE with FROM.

Inserting Data Using SELECT.

Creating Tables Using SELECT.

Summary.

Data Types.

Purpose of Data Types.

Installed Types.

Character String.

Number.

Temporal.

Logical.

Geometric.

Network.

Internal.

Type Conversion Using CAST.

Support Functions.

Support Operators.

Support Variables.

Arrays.

Large Objects (BLOBs).

Summary.

Transactions and Locks.

Transactions.

Multistatement Transactions.

Visibility of Committed Transactions.

Read Committed and Serializable Isolation Levels.

Locking.

Deadlocks.

Summary.

Performance.

Indexes.

Unique Indexes.

CLUSTER.

VACUUM.

VACUUM ANALYZE.

EXPLAIN.

Summary.

Controlling Results.

LIMIT.

Cursors.

Summary.

Table Management.

Temporary Tables.

ALTER TABLE.

GRANT and REVOKE.

Inheritance.

Views.

Rules.

LISTEN and NOTIFY.

Summary.

Constraints.

NOT NULL.

UNIQUE.

PRIMARY KEY.

Foreign Key/REFERENCES.

Modification of Primary Key Row.

Multicolumn Primary Keys.

Handling NULL Values in the Foreign Key.

Frequency of Foreign Key Checking.

CHECK.

Summary.

Importing and Exporting Data.

Using COPY.

COPY File Format.

DELIMITERS.

COPY Without Files.

Backslashes and NULL Values.

COPY Tips.

Summary.

Database Query Tools.

Psql.

Query Buffer Commands.

General Commands.

Output Format Options.

Output Format Shortcuts.

Variables.

Listing Commands.

Large Object Commands.

Psql Command-line Arguments and Start-up File.

Pgaccess.

Summary.

Programming Interfaces.

C Language Interface (LIBPQ).

Pgeasy (LIBPGEASY).

Embedded C (ECPG).

C++ (LIBPQ++).

Compiling Programs.

Assignment to Program Variables.

ODBC.

Java (JDBC).

Scripting Languages.

Perl.

TCL/TK (PGTCLSH/PGTKSH).

Python.

PHP.

Installing Scripting Languages.

Summary.

Functions and Triggers.

Functions.

SQL Functions.

PL/PGSQL Functions.

Triggers.

Summary.

Extending POSTGRESQL Using C.

Write the C Code.

Compile the C Code.

Register the New Functions.

Create Operators, Types, and Aggregates.

Summary.

Administration.

Files.

Creating Users.

Creating Databases.

Access Configuration.

Local.

Host and Hostssl.

User Mappings.

Examples.

Backup and Restore.

Server Start-up and Shutdown.

Monitoring.

Performance.

System Tables.

Internationalization.

Upgrading.

Summary.

Additional Resources.

Mailing List Support.

Supplied Documentation.

Commercial Support.

Modifying the Source Code.

Frequently Asked Questions (FAQs).

Installation.

Getting POSTGRESQL.

Creating the POSTGRESQL User.

Configuration.

Compilation.

Installation.

Initialization.

Starting the Server.

Creating a Database.

PostgreSQL Nonstandard Features by Chapter.


Reference Manual.

ABORT.

ALTER GROUP.

ALTER TABLE.

ALTER USER.

BEGIN.

CLOSE.

CLUSTER.

COMMENT.

COMMIT.

COPY.

CREATE AGGREGATE.

CREATE CONSTRAINT TRIGGER.

CREATE DATABASE.

CREATE FUNCTION.

CREATE GROUP.

CREATE INDEX.

CREATE LANGUAGE.

CREATE OPERATOR.

CREATE RULE.

CREATE SEQUENCE.

CREATE TABLE.

CREATE TABLE AS.

CREATE TRIGGER.

CREATE TYPE.

CREATE USER.

CREATE VIEW.

createdb.

createlang.

createuser.

DECLARE.

DELETE.

DROP AGGREGATE.

DROP DATABASE.

DROP FUNCTION.

DROP GROUP.

DROP INDEX.

DROP LANGUAGE.

DROP OPERATOR.

DROP RULE.

DROP SEQUENCE.

DROP TABLE.

DROP TRIGGER.

DROP TYPE.

DROP USER.

DROP VIEW.

dropdb.

droplang.

dropuser.

ecpg.

END.

EXPLAIN.

FETCH.

GRANT.

initdb.

initlocation.

INSERT.

ipcclean.

LISTEN.

LOAD.

LOCK.

MOVE.

NOTIFY.

pg_ctl.

pg_dump.

pg_dumpall.

pg_passwd.

pg_upgrade.

pgaccess.

pgtclsh.

pgtksh.

postgres.

postmaster.

psql.

REINDEX.

RESET.

REVOKE.

ROLLBACK.

SELECT.

SELECT INTO.

SET.

SHOW.

TRUNCATE.

UNLISTEN.

UPDATE.

VACUUM.

vacuumdb.

Bibliography.


Index. 0201703319T04062001

Preface

This book is about POSTGRESQL, the most advanced open source database. From its origins in academia, POSTGRESQL has moved to the Internet with explosive growth. It is hard to believe the advances during the past four years under the guidance of a team of worldwide Internet developers. This book is a testament to their vision, and to the success that POSTGRESQL has become.

The book is designed to lead the reader from their first database query through the complex queries needed to solve real-world problems. No knowledge of database theory or practice is required. However, basic knowledge of operating system capabilities is expected, such as the ability to type at an operating system prompt.

Beginning with a short history of POSTGRESQL, the book moves from simple queries to the most important database commands. Common problems are covered early, which should prevent users from getting stuck with queries that fail. The author has seen many bug reports in the past few years and consequently has attempted to warn readers about the common pitfalls.

With a firm foundation established, additional commands are introduced. The later chapters outline complex topics like transactions and performance.

At each step, the purpose of each command is clearly illustrated. The goal is to have readers understand more than query syntax. They should know why each command is valuable, so they can use the proper commands in their real-world database applications.

A database novice should read the entire book, while skimming over the later chapters. The complex nature of database systems should not prevent readers from getting started. Test databases offer a safe way to try queries. As readers gain experience, later chapters will begin to make more sense. Experienced database users can skip the early chapters on basic SQL functionality. The cross-referencing of sections allows you to quickly move from general to more specific information.

Much information has been moved out of the main body of the book into appendices. Appendix A lists sources of additional information about POSTGRESQL.Appendix B provides information about installing POSTGRESQL. Appendix C lists the features of POSTGRESQL not found in other database systems. Appendix D contains a copy of the POSTGRESQL manual pages which should be consulted anytime you have trouble with query syntax. Also, do not overlook the excellent documentation that is part of POSTGRESQL. This documentation covers many complex topics, including much POSTGRESQL-specific functionality that cannot be covered in a book of this length. Sections of the documentation are referenced in this book where appropriate.

This book uses italics for identifiers, SMALLCAPS for SQL keywords, and a monospaced font for SQL queries. The Web site for this book is located at http://www.postgresql.org/docs/awbook.html.

0201703319P04062001



Updates

Submit Errata

