MatthewStones_4789Front.fm Page i Wednesday, March 9, 2005 9:28 AM Beginning Databases with PostgreSQL From Novice to Professional, Second Edition NEIL MATTHEW AND RICHARD STONES MatthewStones_4789Front.fm Page ii Wednesday, March 9, 2005 9:28 AM Beginning Databases with PostgreSQL: From Novice to Professional, Second Edition Copyright © 2005 by Neil Matthew and Richard Stones All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN (pbk): 1-59059-478-9 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. Lead Editor: Jason Gilmore Contributing Author: Jon Parise Technical Reviewer: Robert Treat Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser Assistant Publisher: Grace Wong Project Manager: Sofia Marchant Copy Manager: Nicole LeClerc Copy Editor: Marilyn Smith Production Manager: Kari Brooks-Copony Production Editor: Katie Stence Compositor: Susan Glinert Proofreader: Elizabeth Berry Indexer: John Collin Artist: Kinetic Publishing Services, LLC Cover Designer: Kurt Krames Manufacturing Manager: Tom Debolski Distributed to the book trade in the United States by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013, and outside the United States by Springer-Verlag GmbH & Co. KG, Tiergar- tenstr. 17, 69112 Heidelberg, Germany. In the United States: phone 1-800-SPRINGER, fax 201-348-4505, e-mail [email protected], or visit http://www.springer-ny.com. Outside the United States: fax +49 6221 345229, e-mail [email protected], or visit http://www.springer.de. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com in the Downloads section. MatthewStones_4789Front.fm Page iii Wednesday, March 9, 2005 9:28 AM Contents at a Glance About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii CHAPTER 1 Introduction to PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 CHAPTER 2 Relational Database Principles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 CHAPTER 3 Getting Started with PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43 CHAPTER 4 Accessing Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 CHAPTER 5 PostgreSQL Command-Line and Graphical Tools . . . . . . . . . . . . . . 113 CHAPTER 6 Data Interfacing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 CHAPTER 7 Advanced Data Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 CHAPTER 8 Data Definition and Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 CHAPTER 9 Transactions and Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 CHAPTER 10 Functions, Stored Procedures, and Triggers . . . . . . . . . . . . . . . . . . 267 CHAPTER 11 PostgreSQL Administration . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309 CHAPTER 12 Database Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 CHAPTER 13 Accessing PostgreSQL from C Using libpq . . . . . . . . . . . . . . . . . . . . 385 CHAPTER 14 Accessing PostgreSQL from C Using Embedded SQL . . . . . . . . . . 419 CHAPTER 15 Accessing PostgreSQL from PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445 CHAPTER 16 Accessing PostgreSQL from Perl . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465 CHAPTER 17 Accessing PostgreSQL from Java . . . . . . . . . . . . . . . . . . . . . . . . . . . . 491 CHAPTER 18 Accessing PostgreSQL from C# . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 517 APPENDIX A PostgreSQL Database Limits . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543 APPENDIX B PostgreSQL Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545 iii MatthewStones_4789Front.fm Page iv Wednesday, March 9, 2005 9:28 AM iv ■ CONTENTS AT A GLANCE APPENDIX C PostgreSQL SQL Syntax Reference . . . . . . . . . . . . . . . . . . . . . . . . . . 551 APPENDIX D psql Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573 APPENDIX E Database Schema and Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 577 APPENDIX F Large Objects Support in PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . 581 INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589 MatthewStones_4789Front.fm Page v Wednesday, March 9, 2005 9:28 AM CCoonntteennttss About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii ■ CHAPTER 1 Introduction to PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Programming with Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Constant Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Flat Files for Data Storage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Repeating Groups and Other Problems . . . . . . . . . . . . . . . . . . . . . . . . 3 What Is a Database Management System? . . . . . . . . . . . . . . . . . . . . . . . . . 4 Database Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Query Languages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Database Management System Responsibilities . . . . . . . . . . . . . . . 10 What Is PostgreSQL? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 A Short History of PostgreSQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 The PostgreSQL Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13 Data Access with PostgreSQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 What Is Open Source? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 ■ CHAPTER 2 Relational Database Principles . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Limitations of Spreadsheets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Storing Data in a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Choosing Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Choosing a Data Type for Each Column. . . . . . . . . . . . . . . . . . . . . . . 21 Identifying Rows Uniquely . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Accessing Data in a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Accessing Data Across a Network . . . . . . . . . . . . . . . . . . . . . . . . . . . 24 Handling Multiuser Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Slicing and Dicing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 v MatthewStones_4789Front.fm Page vi Wednesday, March 9, 2005 9:28 AM vi ■ CONTENTS Adding Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Using Multiple Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Relating a Table with a Join Operation. . . . . . . . . . . . . . . . . . . . . . . . 29 Designing Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 Understanding Some Basic Rules of Thumb. . . . . . . . . . . . . . . . . . . 33 Creating a Simple Database Design. . . . . . . . . . . . . . . . . . . . . . . . . . 34 Extending Beyond Two Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Completing the Initial Design. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37 Basic Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Dealing with the Unknown: NULLs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 Reviewing the Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 ■ CHAPTER 3 Getting Started with PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . 43 Installing PostgreSQL on Linux and UNIX Systems . . . . . . . . . . . . . . . . . . 43 Installing PostgreSQL from Linux Binaries. . . . . . . . . . . . . . . . . . . . . 44 Anatomy of a PostgreSQL Installation . . . . . . . . . . . . . . . . . . . . . . . . 47 Installing PostgreSQL from the Source Code. . . . . . . . . . . . . . . . . . . 49 Setting Up PostgreSQL on Linux and UNIX . . . . . . . . . . . . . . . . . . . . 53 Installing PostgreSQL on Windows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Using the Windows Installer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Configuring Client Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Creating the Sample Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64 Creating User Records. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Creating the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Creating the Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Removing the Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Populating the Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 ■ CHAPTER 4 Accessing Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Using psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Starting Up on Linux Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Starting Up on Windows Systems. . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Resolving Startup Problems . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Using Some Basic psql Commands . . . . . . . . . . . . . . . . . . . . . . . . . . 78 MatthewStones_4789Front.fm Page vii Wednesday, March 9, 2005 9:28 AM ■ CONTENTS vii Using Simple SELECT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Overriding Column Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Controlling the Order of Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Suppressing Duplicates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Performing Calculations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86 Choosing the Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87 Using More Complex Conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89 Pattern Matching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Limiting the Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92 Checking for NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Checking Dates and Times . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Setting the Time and Date Style. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94 Using Date and Time Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Working with Multiple Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Relating Two Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100 Aliasing Table Names. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105 Relating Three or More Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 The SQL92 SELECT Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 ■ CHAPTER 5 PostgreSQL Command-Line and Graphical Tools . . . . . . . 113 psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Starting psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Issuing Commands in psql. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Working with the Command History. . . . . . . . . . . . . . . . . . . . . . . . . 115 Scripting psql . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Examining the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 psql Command-Line Quick Reference . . . . . . . . . . . . . . . . . . . . . . . 118 psql Internal Commands Quick Reference. . . . . . . . . . . . . . . . . . . . 119 ODBC Setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Installing the ODBC Driver. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 Creating a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123 pgAdmin III . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Installing pgAdmin III . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 125 Using pgAdmin III . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 phpPgAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Installing phpPgAdmin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Using phpPgAdmin. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 MatthewStones_4789Front.fm Page viii Wednesday, March 9, 2005 9:28 AM viii ■ CONTENTS Rekall . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133 Connecting to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Creating Forms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Building Queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136 Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Using Linked Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Entering Data and Creating Reports. . . . . . . . . . . . . . . . . . . . . . . . . 141 Microsoft Excel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Resources for PostgreSQL Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 ■ CHAPTER 6 Data Interfacing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Adding Data to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Using Basic INSERT Statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Using Safer INSERT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Inserting Data into Serial Columns. . . . . . . . . . . . . . . . . . . . . . . . . . 154 Inserting NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Using the \copy Command. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 159 Loading Data Directly from Another Application. . . . . . . . . . . . . . . 162 Updating Data in the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Using the UPDATE Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Updating from Another Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 Deleting Data from the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Using the DELETE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Using the TRUNCATE Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171 ■ CHAPTER 7 Advanced Data Selection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 The Count Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 The Min Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 The Max Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183 The Sum Function. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 The Avg Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184 The Subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185 Subqueries That Return Multiple Rows . . . . . . . . . . . . . . . . . . . . . . 187 Correlated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 188 Existence Subqueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 MatthewStones_4789Front.fm Page ix Wednesday, March 9, 2005 9:28 AM ■ CONTENTS ix The UNION Join . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 192 Self Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194 Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200 ■ CHAPTER 8 Data Definition and Manipulation . . . . . . . . . . . . . . . . . . . . . . . 201 Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201 The Boolean Data Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202 Character Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 204 Number Data Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206 Temporal Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Special Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Arrays. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212 Converting Between Data Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . 212 Functions for Data Manipulation . . . . . . . . . . . . . . . . . . . . . . . . . . . 214 Magic Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215 The OID Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Table Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Creating Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 217 Using Column Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 218 Using Table Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222 Altering Table Structures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Deleting Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227 Using Temporary Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 227 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228 Creating Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228 Deleting and Replacing Views. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231 Foreign Key Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 232 Foreign Key As a Column Constraint . . . . . . . . . . . . . . . . . . . . . . . . 233 Foreign Key As a Table Constraint . . . . . . . . . . . . . . . . . . . . . . . . . . 234 Foreign Key Constraint Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242 ■ CHAPTER 9 Transactions and Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 What Are Transactions? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 243 Grouping Data Changes into Logical Units. . . . . . . . . . . . . . . . . . . . 244 Concurrent Multiuser Access to Data. . . . . . . . . . . . . . . . . . . . . . . . 244 ACID Rules. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Transaction Logs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 247