Transcription
PostgreSQLIntroductionandConcepts
PostgreSQLIntroductionandConceptsBruce MomjianADDISON–WESLEYBoston San Francisco New York Toronto Montreal London MunichParis Madrid Cape Town Sidney Tokyo Singapore Mexico City
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks.Where those designations appear in this book, and we were aware of a trademark claim, the designations havebeen printed in initial capital letters or in all capitals.The author and publisher have taken care in the preparation of this book, but make no expressed or impliedwarranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidentalor consequential damages in connection with or arising out of the use of the information or programs containedherein.The publisher offers discounts on this book when ordered in quantity for special sales. For more information,please contact:Pearson Education Corporate Sales DivisionOne Lake StreetUpper Saddle River, NJ 07458(800) 382-3419corpsales@pearsontechgroup.comVisit AW on the Web: www.awl.com/cseng/Copyright 2001 by Addison–Wesley.All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or transmitted,in any form or by any means, electronic, mechanical, photocopying, recording, or otherwise, without the priorconsent of the publisher. Printed in the United States of America. Published simultaneously in Canada.Library of Congress Cataloging-in-Publication DataMomjian, Bruce.PostgreSQL : introduction and concepts / Momjian,Bruce.p. cm.ISBN 0-201-70331-91. Database management. 2. PostgreSQL. I. Title.QA76.9.D3 M647 2000005.75’85–dc2100-045367CIPThis book was prepared with LYX and LATEX and reproduced by Addison–Wesley from files supplied by the author.Text printed on recycled and acid-free paper1 2 3 4 5 6 7 8 9-MA-0403020100First Printing, November 2000
To my wonderful wife, Christine,and my fine boys, Matthew, Luke, and Peter
ContentsList of FiguresxvList of ii1 History of POSTGRESQL1.1Introduction . . . . . . . . . . . . . . .1.2University of California at Berkeley . .1.3Development Leaves Berkeley . . . . .1.4POSTGRESQL Global Development Team1.5Open Source Software . . . . . . . . . .1.6Summary . . . . . . . . . . . . . . . .11122442 Issuing Database Commands2.1Starting a Database Session2.2Controlling a Session . . .2.3Getting Help . . . . . . . .2.4Exiting a Session . . . . .2.5Summary . . . . . . . . .556999.111113141517.3 Basic SQL Commands3.1Relational Databases . . . . . . . .3.2Creating Tables . . . . . . . . . . .3.3Adding Data with INSERT . . . . . .3.4Viewing Data with SELECT . . . . .3.5Selecting Specific Rows with WHERE.vii.
CONTENTSviii3.63.73.83.93.10Removing Data with DELETEModifying Data with UPDATESorting Data with ORDER BYDestroying Tables . . . . . .Summary . . . . . . . . . .19191919224 Customizing Queries4.1Data Types . . . . . . . . .4.2Quotes Inside Text . . . .4.3Using NULL Values . . . .4.4Controlling DEFAULT Values4.5Column Labels . . . . . .4.6Comments . . . . . . . . .4.7AND/OR Usage . . . . . . .4.8Range of Values . . . . . .4.9LIKE Comparison . . . . .4.10 Regular Expressions . . .4.11 CASE Clause . . . . . . . .4.12 Distinct Rows . . . . . . .4.13 Functions and Operators .4.14 SET, SHOW, and RESET . . .4.15 Summary . . . . . . . . .232325252626303033353637404343475 SQL Aggregates5.1Aggregates . . .5.2Using GROUP BY5.3Using HAVING .5.4Query Tips . . .5.5Summary . . .494951515155.5757576062656870717373.6 Joining Tables6.1Table and Column References .6.2Joined Tables . . . . . . . . .6.3Creating Joined Tables . . . .6.4Performing Joins . . . . . . . .6.5Three- and Four-Table Joins . .6.6Additional Join Possibilities . .6.7Choosing a Join Key . . . . . .6.8One-to-Many Joins . . . . . .6.9Unjoined Tables . . . . . . . .6.10 Table Aliases and Self-joins . .
CONTENTS6.116.126.136.14Non-equijoins . . . . . . .Ordering Multiple Parts . .Primary and Foreign Keys .Summary . . . . . . . . .ix.747577777 Numbering Rows7.1Object Identification Numbers (OIDs) . . .7.2Object Identification Number Limitations7.3Sequences . . . . . . . . . . . . . . . . .7.4Creating Sequences . . . . . . . . . . . .7.5Using Sequences to Number Rows . . . .7.6Serial Column Type . . . . . . . . . . . .7.7Manually Numbering Rows . . . . . . . .7.8Summary . . . . . . . . . . . . . . . . .7979818182828585868 Combining SELECTs8.1UNION, EXCEPT, and INTERSECT Clauses8.2Subqueries . . . . . . . . . . . . . . . .8.3Outer Joins . . . . . . . . . . . . . . .8.4Subqueries in Non-SELECT Queries . . .8.5UPDATE with FROM . . . . . . . . . . .8.6Inserting Data Using SELECT . . . . . .8.7Creating Tables Using SELECT . . . . .8.8Summary . . . . . . . . . . . . . . . .8787911011011011031031059 Data Types9.1Purpose of Data Types . . .9.2Installed Types . . . . . . .9.3Type Conversion Using CAST9.4Support Functions . . . . . .9.5Support Operators . . . . . .9.6Support Variables . . . . . .9.7Arrays . . . . . . . . . . . .9.8Large Objects (BLOBs) . . . .9.9Summary . . . . . . . . . .10710710811111111111511611611910 Transactions and Locks10.1 Transactions . . . . . . . . . . . . . . . . . . . .10.2 Multistatement Transactions . . . . . . . . . . .10.3 Visibility of Committed Transactions . . . . . . .10.4 Read Committed and Serializable Isolation Levels.121121122124125.
CONTENTSx10.510.610.7Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Deadlocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13011 Performance11.1 Indexes . . . . .11.2 Unique Indexes .11.3 CLUSTER . . . . .11.4 VACUUM . . . . .11.5 VACUUM ANALYZE11.6 EXPLAIN . . . . .11.7 Summary . . . .13113113213313313413413612 Controlling Results12.1 LIMIT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12.2 Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .12.3 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .13713713713813 Table Management13.1 Temporary Tables .13.2 ALTER TABLE . . .13.3 GRANT and REVOKE13.4 Inheritance . . . .13.5 Views . . . . . . .13.6 Rules . . . . . . . .13.7 LISTEN and NOTIFY13.8 Summary . . . . .14114114314314514814915415414 Constraints14.1 NOT NULL . . . . . . . .14.2 UNIQUE . . . . . . . . .14.3 PRIMARY KEY . . . . . .14.4 Foreign Key/REFERENCES14.5 CHECK . . . . . . . . . .14.6 Summary . . . . . . . .15515515515815816616615 Importing and Exporting Data15.1 Using COPY . . . . . . . .15.2 COPY File Format . . . . .15.3 DELIMITERS . . . . . . . .15.4 COPY Without Files . . . .169169169171173.
CONTENTS15.515.615.7xiBackslashes and NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173COPY Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17516 Database Query Tools17716.1 Psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17716.2 Pgaccess . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18416.3 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18417 Programming Interfaces17.1 C Language Interface (LIBPQ) . .17.2 Pgeasy (LIBPGEASY) . . . . . . .17.3 Embedded C (ECPG) . . . . . . .17.4 C (LIBPQ ) . . . . . . . .17.5 Compiling Programs . . . . . . .17.6 Assignment to Program Variables17.7 ODBC . . . . . . . . . . . . . . .17.8 Java (JDBC) . . . . . . . . . . . .17.9 Scripting Languages . . . . . . .17.10 Perl . . . . . . . . . . . . . . .17.11 TCL/TK (PGTCLSH/PGTKSH) . . .17.12 Python . . . . . . . . . . . . . .17.13 PHP . . . . . . . . . . . . . . .17.14 Installing Scripting Languages .17.15 Summary . . . . . . . . . . . 8 Functions and Triggers18.1 Functions . . . . .18.2 SQL Functions . . .18.3 PL/PGSQL Functions18.4 Triggers . . . . . .18.5 Summary . . . . .20320320420821021619 Extending POSTGRESQL Using C19.1 Write the C Code . . . . . . . . . . . . .19.2 Compile the C Code . . . . . . . . . . . .19.3 Register the New Functions . . . . . . .19.4 Create Operators, Types, and Aggregates19.5 Summary . . . . . . . . . . . . . . . . .219219220220221222.
CONTENTSxii20 Administration20.1 Files . . . . . . . . . . . . . .20.2 Creating Users . . . . . . . .20.3 Creating Databases . . . . . .20.4 Access Configuration . . . . .20.5 Backup and Restore . . . . . .20.6 Server Start-up and Shutdown20.7 Monitoring . . . . . . . . . . .20.8 Performance . . . . . . . . . .20.9 System Tables . . . . . . . . .20.10 Internationalization . . . . . .20.11 Upgrading . . . . . . . . . . .20.12 Summary . . . . . . . . . . .223223223225225227228229230231232232232A Additional ResourcesA.1Mailing List Support . . . . . . . . .A.2Supplied Documentation . . . . . .A.3Commercial Support . . . . . . . . .A.4Modifying the Source Code . . . . .A.5Frequently Asked Questions (FAQs) .233233233233233234.B Installation255C PostgreSQL Nonstandard Features by Chapter257D Reference ManualD.1ABORT . . . . . . . . . . . . . . .D.2ALTER GROUP . . . . . . . . . . .D.3ALTER TABLE . . . . . . . . . . .D.4ALTER USER . . . . . . . . . . . .D.5BEGIN . . . . . . . . . . . . . . . .D.6CLOSE . . . . . . . . . . . . . . . .D.7CLUSTER . . . . . . . . . . . . . .D.8COMMENT . . . . . . . . . . . . .D.9COMMIT . . . . . . . . . . . . . .D.10 COPY . . . . . . . . . . . . . . . .D.11 CREATE AGGREGATE . . . . . .D.12 CREATE CONSTRAINT TRIGGERD.13 CREATE DATABASE . . . . . . . .D.14 CREATE FUNCTION . . . . . . . .D.15 CREATE GROUP . . . . . . . . . .259259260261264265267268270271272276278279281285
51D.52D.53D.54D.55CREATE INDEX . . .CREATE LANGUAGECREATE OPERATORCREATE RULE . . . .CREATE SEQUENCECREATE 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 . . . . . . 53355356360360362365368369
.78D.79D.80D.81D.82D.83D.84D.85D.86INSERT . . . .ipcclean . . . .LISTEN . . . .LOAD . . . . .LOCK . . . . .MOVE . . . . .NOTIFY . . . .pgaccess . . . .pgtclsh . . . . .pgtksh . . . . .pg ctl . . . . .pg dump . . . .pg dumpall . .pg passwd . . .pg upgrade . .postgres . . . .postmaster . . .psql . . . . . .REINDEX . . .RESET . . . . .REVOKE . . .ROLLBACK . .SELECT . . . .SELECT INTOSET . . . . . .SHOW . . . . .TRUNCATE . .UNLISTEN . .UPDATE . . . .VACUUM . . .vacuumdb . . graphy453Index455
List of Figures2.12.22.32.4psql session start-up.My first SQL query . . .Multiline query . . . .Backslash-p demo . . 143.153.16Databases . . . . . . . . . . . . .Create table friend . . . . . . . . .Example of backslash-d . . . . . .INSERT into friend . . . . . . . . .Additional friend INSERT commandsMy first SELECT . . . . . . . . . .My first WHERE . . . . . . . . . .More complex WHERE clause . . .A single cell . . . . . . . . . . . .A block of cells . . . . . . . . . .Comparing string fields . . . . . .DELETE example . . . . . . . . .My first UPDATE . . . . . . . . . .Use of ORDER BY . . . . . . . . . .Reverse ORDER BY . . . . . . . . .Use of ORDER BY and WHERE . . 64.74.84.9Example of common data types . .Insertion of specific columns . . .NULL handling . . . . . . . . . . .Comparison of NULL fields . . . .NULL values and blank strings . .Using DEFAULT values . . . . . . .Controlling column labels . . . . .Computation using a column labelComment styles . . . . . . . . . .242527282829293030xv
LIST OF 4.204.214.224.234.24New friends . . . . . . . . . . . . . . .for Sandy Gleason . . . . . .Friends in New Jersey and PennsylvaniaIncorrectly mixing AND and OR clauses .Correctly mixing AND and OR clauses . .Selecting a range of values . . . . . . .Firstname begins with D . . . . . . . . .Regular expression sample queries . . .Complex regular expression queries . .CASE example . . . . . . . . . . . . . .Complex CASE example . . . . . . . . .DISTINCT prevents duplicates . . . . . .Function examples . . . . . . . . . . .Operator examples . . . . . . . . . . .SHOW and RESET examples . . . . . . ples of Aggregates . . .Aggregates and NULL valuesAggregate with GROUP BY . .GROUP BY with two columnsHAVING . . . . . . . . . . . 6.136.146.156.166.176.186.19Qualified column names . . . . . . . . . . . . . .Joining tables . . . . . . . . . . . . . . . . . . .Creation of company tables . . . . . . . . . . . .Insertion into company tables . . . . . . . . . . .Finding a customer name using two queries . . .Finding a customer name using one query . . . .Finding an order number for a customer name . .Three-table join . . . . . . . . . . . . . . . . . .Four-table join . . . . . . . . . . . . . . . . . . .Employees who have taken orders for customersJoining customer and employee . . . . . . . . . . .Joining part and employee . . . . . . . . . . . . .The statename table . . . . . . . . . . . . . . . .Using a customer code . . . . . . . . . . . . . .A one-to-many join . . . . . . . . . . . . . . . .Unjoined tables . . . . . . . . . . . . . . . . . .Using table aliases . . . . . . . . . . . . . . . . .Examples of self-joins using table aliases . . . . .Non-equijoins . . . . . . . . . . . . . . . . . . .58596163646465666667686969717273737475WHERE test.
LIST OF FIGURESxvii6.206.216.22New salesorder table for multiple parts per order . . . . . . . . . . . . . . . . . .The orderpart table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .Queries involving the orderpart table . . . . . . . . . . . . . . . . . . . . . . . .7676787.17.27.37.47.5OID test . . . . . . . . . . . . . . . . . . .Columns with OIDs . . . . . . . . . . . . .Examples of sequence function use . . . . .Numbering customer rows using a sequenceThe customer table using SERIAL . . . . . 8.138.148.158.168.178.18Combining two columns with UNION . . . . .Combining two tables with UNION . . . . . . .UNION with duplicates . . . . . . . . . . . . .UNION ALL with duplicates . . . . . . . . . .EXCEPT restricts output from the first SELECTINTERSECT returns only duplicated rows . . .Friends not in Dick Gleason’s state . . . . . .Subqueries can replace some joins . . . . . .Correlated subquery . . . . . . . . . . . . . .Employees who took orders . . . . . . . . .Customers who have no orders . . . . . . . .IN query rewritten using ANY and EXISTS . . .NOT IN query rewritten using ALL and EXISTSSimulating outer joins . . . . . . . . . . . . .Subqueries with UPDATE and DELETE . . . . .UPDATE the order date . . . . . . . . . . . .Using SELECT with INSERT . . . . . . . . . .Table creation with SELECT . . . . . . . . . .39.49.59.6Example of a function call . . . . . . . . . . . . . . . . .Error generated by undefined function/type combination.Error generated by undefined operator/type combinationCreation of array columns . . . . . . . . . . . . . . . . .Using arrays . . . . . . . . . . . . . . . . . . . . . . . .Using large images . . . . . . . . . . . . . . . . . . . .11211211511611711810.110.210.310.410.5INSERT with no explicit transaction .INSERT using an explicit transactionTwo INSERTs in a single transaction .Multistatement transaction . . . . .Transaction rollback . . . . . . . . .122122123123124.
LIST OF FIGURESxviii10.610.710.810.9Read-committed isolation levelSerializable isolation level . . .SELECT with no locking . . . .SELECT FOR UPDATE . . . . .12612712913011.111.211.311.411.5Example of CREATE INDEX . . . .Example of a unique index . . .Using EXPLAIN . . . . . . . . . .More complex EXPLAIN examplesEXPLAIN example using joins . .13213313413513612.112.2Examples of LIMIT and LIMIT/OFFSET . . . . . . . . . . . . . . . . . . . . . . . . 138Cursor usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3.1213.1313.14Temporary table auto-destruction . . . . . . . .Example of temporary table use . . . . . . . .ALTER TABLE examples . . . . . . . . . . . . .Examples of the GRANT command . . . . . . . .Creation of inherited tables . . . . . . . . . . .Accessing inherited tables . . . . . . . . . . .Inheritance in layers . . . . . . . . . . . . . . .Examples of views . . . . . . . . . . . . . . . .Rule to prevent an INSERT . . . . . . . . . . . .Rules to log table changes . . . . . . . . . . . .Use of rules to log table changes . . . . . . . .Views ignore table modifications . . . . . . . .Rules to handle view modifications . . . . . . .Example of rules that handle view 1114.12NOT NULL constraint . . . . . . . . . . . . . . . . . . . .NOT NULL with DEFAULT constraint . . . . . . . . . . . . .UNIQUE column constraint . . . . . . . . . . . . . . . . .Multicolumn UNIQUE constraint . . . . . . . . . . . . . . .Creation of a PRIMARY KEY column . . . . . . . . . . . . .Example of a multicolumn PRIMARY KEY . . . . . . . . . .Foreign key creation . . . . . . . . . . . . . . . . . . . . .Foreign key constraints . . . . . . . . . . . . . . . . . . .Creation of company tables using primary and foreign keysCustomer table with foreign key actions . . . . . . . . . .Foreign key actions . . . . . . . . . . . . . . . . . . . . .Example of a multicolumn foreign key . . . . . . . . . . .156156157157158159160160161162163164.
LIST OF FIGURESxix14.13 MATCH FULL foreign key . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16514.14 DEFERRABLE foreign key constraint . . . . . . . . . . . . . . . . . . . . . . . . . 16714.15 CHECK constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16815.115.215.315.415.515.6Example of COPY TO and COPY FROM . . .Example of COPY FROM . . . . . . . . . . .Example of COPY TO USING DELIMITERS . .Example of COPY FROM USING DELIMITERSCOPY using stdin and stdout . . . . . . . . . .COPY backslash handling . . . . . . . . . . .17017117217217317416.116.216.316.4psql variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181Pgaccess’s opening window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186Pgaccess’s table window . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7.1217.13Sample application being runStatename table . . . . . . .LIBPQ data flow . . . . . . .LIBPQ sample program . . .LIBPGEASY sample program .ECPG sample program . . . .LIBPQ sample program .Java sample program . . . .Perl sample program . . . .TCL sample program . . . .Python sample program . . .PHP sample program—inputPHP sample program—output.
PostgreSQL : introduction and concepts / Momjian, Bruce. p. cm. ISBN -201-70331-9 1. Database management. 2. PostgreSQL. I. Title. QA76.9.D3 M647 2000 CIP This book was prepared with LYX and LATEX and reproduced by AddisonŒWesley from les supplied by the author.
