PostgreSQL Reference Manual

Volume 1: SQL Language Reference

for version 8.2.4.

June 2007

The PostgreSQL Global Development Group

Buy the book here! >>>
support free documentation

Publisher's Preface
1 Introduction
- 1.1 What is PostgreSQL?
- 1.2 A Brief History of PostgreSQL
- 1.3 Conventions
The SQL Language
2 SQL Syntax
- 2.1 Lexical Structure
- 2.2 Value Expressions
3 Data Definition
- 3.1 Table Basics
- 3.2 Default Values
- 3.3 Constraints
- 3.4 System Columns
- 3.5 Modifying Tables
- 3.6 Privileges
- 3.7 Schemas
- 3.8 Inheritance
  - 3.8.1 Caveats
- 3.9 Partitioning
- 3.10 Other Database Objects
- 3.11 Dependency Tracking
4 Data Manipulation
- 4.1 Inserting Data
- 4.2 Updating Data
- 4.3 Deleting Data
5 Queries
- 5.1 Overview
- 5.2 Table Expressions
- 5.3 Select Lists
- 5.4 Combining Queries
- 5.5 Sorting Rows
- 5.6 LIMIT and OFFSET
- 5.7 VALUES Lists
6 Data Types
- 6.1 Numeric Types
- 6.2 Monetary Types
- 6.3 Character Types
- 6.4 Binary Data Types
- 6.5 Date/Time Types
- 6.6 Boolean Type
- 6.7 Geometric Types
- 6.8 Network Address Types
- 6.9 Bit String Types
- 6.10 Arrays
- 6.11 Composite Types
- 6.12 Object Identifier Types
- 6.13 Pseudo-Types
- 6.14 XML Document Support
7 Functions and Operators
- 7.1 Logical Operators
- 7.2 Comparison Operators
- 7.3 Mathematical Functions and Operators
- 7.4 String Functions and Operators
- 7.5 Binary String Functions and Operators
- 7.6 Bit String Functions and Operators
- 7.7 Pattern Matching
  - 7.7.1 LIKE
  - 7.7.2 SIMILAR TO Regular Expressions
  - 7.7.3 POSIX Regular Expressions
- 7.8 Data Type Formatting Functions
- 7.9 Date/Time Functions and Operators
  - 7.9.1 EXTRACT, date_part
  - 7.9.2 date_trunc
  - 7.9.3 AT TIME ZONE
  - 7.9.4 Current Date/Time
  - 7.9.5 Delaying Execution
- 7.10 Geometric Functions and Operators
- 7.11 Network Address Functions and Operators
- 7.12 Sequence Manipulation Functions
- 7.13 Conditional Expressions
  - 7.13.1 CASE
  - 7.13.2 COALESCE
  - 7.13.3 NULLIF
  - 7.13.4 GREATEST and LEAST
- 7.14 Array Functions and Operators
- 7.15 Aggregate Functions
- 7.16 Subquery Expressions
  - 7.16.1 EXISTS
  - 7.16.2 IN
  - 7.16.3 NOT IN
  - 7.16.4 ANY/SOME
  - 7.16.5 ALL
  - 7.16.6 Row-wise Comparison
- 7.17 Row and Array Comparisons
  - 7.17.1 IN
  - 7.17.2 NOT IN
  - 7.17.3 ANY/SOME (array)
  - 7.17.4 ALL (array)
  - 7.17.5 Row-wise Comparison
- 7.18 Set Returning Functions
- 7.19 System Information Functions
- 7.20 System Administration Functions
8 Type Conversion
- 8.1 Overview
- 8.2 Operators
- 8.3 Functions
- 8.4 Value Storage
- 8.5 UNION, CASE, and Related Constructs
9 Indexes
- 9.1 Introduction
- 9.2 Index Types
- 9.3 Multicolumn Indexes
- 9.4 Combining Multiple Indexes
- 9.5 Unique Indexes
- 9.6 Indexes on Expressions
- 9.7 Partial Indexes
- 9.8 Operator Classes
- 9.9 Examining Index Usage
10 Concurrency Control
- 10.1 Introduction
- 10.2 Transaction Isolation
  - 10.2.1 Read Committed Isolation Level
  - 10.2.2 Serializable Isolation Level
    - 10.2.2.1 Serializable Isolation versus True Serializability
- 10.3 Explicit Locking
- 10.4 Data Consistency Checks at the Application Level
- 10.5 Locking and Indexes
11 Performance Tips
- 11.1 Using EXPLAIN
- 11.2 Statistics Used by the Planner
- 11.3 Controlling the Planner with Explicit JOIN Clauses
- 11.4 Populating a Database
Reference
12 SQL Commands
- 12.1 ABORT
- 12.2 ALTER AGGREGATE
- 12.3 ALTER CONVERSION
- 12.4 ALTER DATABASE
- 12.5 ALTER DOMAIN
- 12.6 ALTER FUNCTION
- 12.7 ALTER GROUP
- 12.8 ALTER INDEX
- 12.9 ALTER LANGUAGE
- 12.10 ALTER OPERATOR
- 12.11 ALTER OPERATOR CLASS
- 12.12 ALTER ROLE
- 12.13 ALTER SCHEMA
- 12.14 ALTER SEQUENCE
- 12.15 ALTER TABLE
- 12.16 ALTER TABLESPACE
- 12.17 ALTER TRIGGER
- 12.18 ALTER TYPE
- 12.19 ALTER USER
- 12.20 ANALYZE
- 12.21 BEGIN
- 12.22 CHECKPOINT
- 12.23 CLOSE
- 12.24 CLUSTER
- 12.25 COMMENT
- 12.26 COMMIT
- 12.27 COMMIT PREPARED
- 12.28 COPY
- 12.29 CREATE AGGREGATE
- 12.30 CREATE CAST
- 12.31 CREATE CONSTRAINT TRIGGER
- 12.32 CREATE CONVERSION
- 12.33 CREATE DATABASE
- 12.34 CREATE DOMAIN
- 12.35 CREATE FUNCTION
- 12.36 CREATE GROUP
- 12.37 CREATE INDEX
- 12.38 CREATE LANGUAGE
- 12.39 CREATE OPERATOR
- 12.40 CREATE OPERATOR CLASS
- 12.41 CREATE ROLE
- 12.42 CREATE RULE
- 12.43 CREATE SCHEMA
- 12.44 CREATE SEQUENCE
- 12.45 CREATE TABLE
- 12.46 CREATE TABLE AS
- 12.47 CREATE TABLESPACE
- 12.48 CREATE TRIGGER
- 12.49 CREATE TYPE
- 12.50 CREATE USER
- 12.51 CREATE VIEW
- 12.52 DEALLOCATE
- 12.53 DECLARE
- 12.54 DELETE
- 12.55 DROP AGGREGATE
- 12.56 DROP CAST
- 12.57 DROP CONVERSION
- 12.58 DROP DATABASE
- 12.59 DROP DOMAIN
- 12.60 DROP FUNCTION
- 12.61 DROP GROUP
- 12.62 DROP INDEX
- 12.63 DROP LANGUAGE
- 12.64 DROP OPERATOR
- 12.65 DROP OPERATOR CLASS
- 12.66 DROP OWNED
- 12.67 DROP ROLE
- 12.68 DROP RULE
- 12.69 DROP SCHEMA
- 12.70 DROP SEQUENCE
- 12.71 DROP TABLE
- 12.72 DROP TABLESPACE
- 12.73 DROP TRIGGER
- 12.74 DROP TYPE
- 12.75 DROP USER
- 12.76 DROP VIEW
- 12.77 END
- 12.78 EXECUTE
- 12.79 EXPLAIN
- 12.80 FETCH
- 12.81 GRANT
- 12.82 INSERT
- 12.83 LISTEN
- 12.84 LOAD
- 12.85 LOCK
- 12.86 MOVE
- 12.87 NOTIFY
- 12.88 PREPARE
- 12.89 PREPARE TRANSACTION
- 12.90 REASSIGN OWNED
- 12.91 REINDEX
- 12.92 RELEASE SAVEPOINT
- 12.93 RESET
- 12.94 REVOKE
- 12.95 ROLLBACK
- 12.96 ROLLBACK PREPARED
- 12.97 ROLLBACK TO SAVEPOINT
- 12.98 SAVEPOINT
- 12.99 SELECT
- 12.100 SELECT INTO
- 12.101 SET
- 12.102 SET CONSTRAINTS
- 12.103 SET ROLE
- 12.104 SET SESSION AUTHORIZATION
- 12.105 SET TRANSACTION
- 12.106 SHOW
- 12.107 START TRANSACTION
- 12.108 TRUNCATE
- 12.109 UNLISTEN
- 12.110 UPDATE
- 12.111 VACUUM
- 12.112 VALUES
13 PostgreSQL Client Applications
- 13.1 clusterdb
- 13.2 createdb
- 13.3 createlang
- 13.4 createuser
- 13.5 dropdb
- 13.6 droplang
- 13.7 dropuser
- 13.8 ecpg
- 13.9 pg_config
- 13.10 pg_dump
- 13.11 pg_dumpall
- 13.12 pg_restore
- 13.13 psql
- 13.14 reindexdb
- 13.15 vacuumdb
14 PostgreSQL Server Applications
- 14.1 initdb
- 14.2 ipcclean
- 14.3 pg_controldata
- 14.4 pg_ctl
- 14.5 pg_resetxlog
- 14.6 postgres
- 14.7 postmaster
Appendixes
A PostgreSQL Error Codes
B Date/Time Support
- 14.8 Date/Time Input Interpretation
- 14.9 Date/Time Key Words
- 14.10 Date/Time Configuration Files
- 14.11 History of Units
C SQL Key Words
D SQL Conformance
- 14.12 Supported Features
- 14.13 Unsupported Features
E Bibliography
- 14.14 SQL Reference Books
- 14.15 PostgreSQL-Specific Documentation
- 14.16 Proceedings and Articles
Books from the publisher
Index

Modifications Copyright © 2007 Network Theory Ltd, distributed under the terms of the license below.
Copyright © 1996--2006, The PostgreSQL Global Development Group
Legal Notice
PostgreSQL is Copyright © 1996--2006 by the PostgreSQL Global Development Group and is distributed under the terms of the license of the University of California below.
Postgres95 is Copyright © 1994--5 by the Regents of the University of California.
Permission to use, copy, modify, and distribute this software and its documentation for any purpose, without fee, and without a written agreement is hereby granted, provided that the above copyright notice and this paragraph and the following two paragraphs appear in all copies.
IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN ``AS-IS'' BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

ISBN 0954612027

PostgreSQL Reference Manual - Volume 1 - SQL Language Reference

See the print edition