Beginning Databases with PostgreSQL: From Novice to Professional, Second Edition (Beginning from Novice to Professional) (Volume 0)

666 Pages·2005·1.34 MB·English

by  Richard Stones

MatthewStones_4789Front.fm Page i Wednesday, March 9, 2005 9:28 AM
This Book Is Distributed By http://pdfstore.tk/ Please
Make Sure That This E-Book Dont Have Any Or Damage This will cause you
Missing Pages And Missing Tutorials.www.pdfstore.tk
will automaticly `check . is this book is ready for read
Attention :- Before You read this Book Please Visit www.pdfstore.tk and check
you can Free Download any kind of Free matirials from www.pdfstore.tk
web site
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 [phone], e-mail [email protected], or visit
http://www.springer-ny.com. Outside the United States: fax [phone], 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 [phone], fax [phone], 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