PostgreSQL: Introduction and Concepts

Bruce Momjian0201703319, 9780201703313

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.

---

Table of contents :
List of Figures……Page 7
List of Tables……Page 15
Foreword……Page 23
Preface……Page 25
Acknowledgments……Page 27
University of California at Berkeley……Page 29
PostgreSQL Global Development Team……Page 30
Summary……Page 32
Starting a Database Session……Page 33
Controlling a Session……Page 34
Summary……Page 37
Relational Databases……Page 39
Creating Tables……Page 41
Adding Data with Insert……Page 42
Viewing Data with Select……Page 43
Selecting Specific Rows with Where……Page 45
Destroying Tables……Page 47
Summary……Page 50
Data Types……Page 51
Using Null Values……Page 53
Column Labels……Page 54
And/Or Usage……Page 58
Range of Values……Page 61
Like Comparison……Page 63
Regular Expressions……Page 64
Case Clause……Page 65
Distinct Rows……Page 68
Set, Show, and Reset……Page 71
Summary……Page 75
Aggregates……Page 77
Query Tips……Page 79
Summary……Page 83
Joined Tables……Page 85
Creating Joined Tables……Page 88
Performing Joins……Page 90
Three- and Four-Table Joins……Page 93
Additional Join Possibilities……Page 96
Choosing a Join Key……Page 98
One-to-Many Joins……Page 99
Table Aliases and Self-joins……Page 101
Non-equijoins……Page 102
Ordering Multiple Parts……Page 103
Summary……Page 105
Object Identification Numbers (oids)……Page 107
Sequences……Page 109
Using Sequences to Number Rows……Page 110
Manually Numbering Rows……Page 113
Summary……Page 114
Union, Except, and Intersect Clauses……Page 115
Subqueries……Page 119
Update with From……Page 129
Creating Tables Using Select……Page 131
Summary……Page 133
Purpose of Data Types……Page 135
Installed Types……Page 136
Support Operators……Page 139
Support Variables……Page 143
Large Objects (blobs)……Page 144
Summary……Page 147
Transactions……Page 149
Multistatement Transactions……Page 150
Visibility of Committed Transactions……Page 152
Read Committed and Serializable Isolation Levels……Page 153
Deadlocks……Page 156
Summary……Page 158
Indexes……Page 159
Unique Indexes……Page 160
Vacuum……Page 161
Explain……Page 162
Summary……Page 164
Cursors……Page 165
Summary……Page 166
Temporary Tables……Page 169
Grant and Revoke……Page 171
Inheritance……Page 173
Views……Page 176
Rules……Page 177
Summary……Page 182
Unique……Page 183
Foreign Key/References……Page 186
Summary……Page 194
Copy File Format……Page 197
Delimiters……Page 199
Backslashes and Null Values……Page 201
Summary……Page 203
Psql……Page 205
Summary……Page 212
Programming Interfaces……Page 215
C Language Interface (libpq)……Page 217
Compiling Programs……Page 219
Assignment to Program Variables……Page 223
Scripting Languages……Page 224
Perl……Page 226
Python……Page 227
Installing Scripting Languages……Page 228
Summary……Page 229
Functions……Page 231
Sql Functions……Page 232
Pl/pgsql Functions……Page 236
Triggers……Page 238
Summary……Page 244
Write the C Code……Page 247
Register the New Functions……Page 248
Create Operators, Types, and Aggregates……Page 249
Summary……Page 250
Creating Users……Page 251
Access Configuration……Page 253
Backup and Restore……Page 255
Server Start-up and Shutdown……Page 256
Monitoring……Page 257
Performance……Page 258
System Tables……Page 259
Summary……Page 260
Modifying the Source Code……Page 261
Frequently Asked Questions (faqs)……Page 262
Installation……Page 283
PostgreSQL Nonstandard Features by Chapter……Page 285
ABORT……Page 287
ALTER GROUP……Page 288
ALTER TABLE……Page 289
ALTER USER……Page 292
BEGIN……Page 293
CLOSE……Page 295
CLUSTER……Page 296
COMMENT……Page 298
COMMIT……Page 299
COPY……Page 300
CREATE AGGREGATE……Page 304
CREATE CONSTRAINT TRIGGER……Page 306
CREATE DATABASE……Page 307
CREATE FUNCTION……Page 309
CREATE GROUP……Page 313
CREATE INDEX……Page 314
CREATE LANGUAGE……Page 317
CREATE OPERATOR……Page 320
CREATE RULE……Page 324
CREATE SEQUENCE……Page 328
CREATE TABLE……Page 330
CREATE TABLE AS……Page 347
CREATE TRIGGER……Page 348
CREATE TYPE……Page 350
CREATE USER……Page 353
CREATE VIEW……Page 355
createdb……Page 357
createlang……Page 359
createuser……Page 360
DECLARE……Page 361
DELETE……Page 364
DROP AGGREGATE……Page 365
DROP DATABASE……Page 366
DROP FUNCTION……Page 367
DROP GROUP……Page 368
DROP INDEX……Page 369
DROP LANGUAGE……Page 370
DROP OPERATOR……Page 371
DROP RULE……Page 373
DROP SEQUENCE……Page 374
DROP TABLE……Page 375
DROP TRIGGER……Page 376
DROP TYPE……Page 377
DROP USER……Page 378
DROP VIEW……Page 379
dropdb……Page 380
droplang……Page 381
dropuser……Page 383
ecpg……Page 384
EXPLAIN……Page 388
FETCH……Page 390
GRANT……Page 393
initdb……Page 396
initlocation……Page 397
INSERT……Page 398
ipcclean……Page 400
LISTEN……Page 401
LOAD……Page 402
LOCK……Page 404
MOVE……Page 407
NOTIFY……Page 408
pg_ctl……Page 410
pg_dump……Page 413
pg_dumpall……Page 416
pg_passwd……Page 418
pg_upgrade……Page 419
pgaccess……Page 421
pgtclsh……Page 423
postgres……Page 424
postmaster……Page 427
psql……Page 430
REINDEX……Page 450
RESET……Page 451
REVOKE……Page 452
ROLLBACK……Page 454
SELECT……Page 455
SELECT INTO……Page 464
SET……Page 465
SHOW……Page 471
TRUNCATE……Page 472
UNLISTEN……Page 473
UPDATE……Page 474
VACUUM……Page 476
vacuumdb……Page 478
Index……Page 481