PostgreSQL User's Guide

The PostgreSQL Development Team

Edited by

Thomas Lockhart

is Copyright © 1996-9 by the Postgres Global Development Group.

Table of Contents
1. Introduction
What is Postgres?
A Short History of Postgres
The Berkeley Postgres Project
About This Release
Y2K Statement
Copyrights and Trademarks
2. SQL Syntax
Key Words
Reserved Key Words
Non-reserved Keywords
3. Data Types
Numeric Types
Monetary Type
Character Types
Date/Time Types
SQL92 Conventions
Date/Time Styles
Time Zones
Date/Time Input
Boolean Type
Geometric Types
Line Segment
IP Version 4 Networks and Host Addresses
4. Operators
Lexical Precedence
General Operators
Numerical Operators
Geometric Operators
Time Interval Operators
IP V4 CIDR Operators
IP V4 INET Operators
5. Functions
SQL Functions
Mathematical Functions
String Functions
Date/Time Functions
Geometric Functions
IP V4 Functions
6. Type Conversion
Conversion Procedure
Query Targets
UNION Queries
7. Indices and Keys
8. Arrays
9. Inheritance
10. Multi-Version Concurrency Control
Transaction Isolation
Read Committed Isolation Level
Serializable Isolation Level
Locking and Tables
Table-level locks
Row-level locks
Locking and Indices
Data consistency checks at the application level
11. Setting Up Your Environment
12. Managing a Database
Database Creation
Alternate Database Locations
Accessing a Database
Database Privileges
Table Privileges
Destroying a Database
13. Disk Storage
14. SQL Commands
ABORT — Aborts the current transaction
ALTER TABLE — Modifies table properties
ALTER USER — Modifies user account information
BEGIN — Begins a transaction in chained mode
CLOSE — Close a cursor
CLUSTER — Gives storage clustering advice to the backend
COMMIT — Commits the current transaction
COPY — Copies data between files and tables
CREATE AGGREGATE — Defines a new aggregate function
CREATE DATABASE — Creates a new database
CREATE FUNCTION — Defines a new function
CREATE INDEX — Constructs a secondary index
CREATE LANGUAGE — Defines a new language for functions
CREATE OPERATOR — Defines a new user operator
CREATE RULE — Defines a new rule
CREATE SEQUENCE — Creates a new sequence number generator
CREATE TABLE — Creates a new table
CREATE TABLE AS — Creates a new table
CREATE TRIGGER — Creates a new trigger
CREATE TYPE — Defines a new base data type
CREATE USER — Creates account information for a new user
CREATE VIEW — Constructs a virtual table
DECLARE — Defines a cursor for table access
DELETE — Deletes rows from a table
DROP AGGREGATE — Removes the definition of an aggregate function
DROP DATABASE — Destroys an existing database
DROP FUNCTION — Removes a user-defined C function
DROP INDEX — Removes an index from a database
DROP LANGUAGE — Removes a user-defined procedural language
DROP OPERATOR — Removes an operator from the database
DROP RULE — Removes an existing rule from the database
DROP SEQUENCE — Removes an existing sequence
DROP TABLE — Removes existing tables from a database
DROP TRIGGER — Removes the definition of a trigger
DROP TYPE — Removes a user-defined type from the system catalogs
DROP USER — Removes an user account information
DROP VIEW — Removes an existing view from a database
EXPLAIN — Shows statement execution details
FETCH — Gets rows using a cursor
GRANT — Grants access privilege to a user, a group or all users
INSERT — Inserts new rows into a table
LISTEN — Listen for notification on a notify condition
LOAD — Dynamically loads an object file
LOCK — Explicit lock of a table inside a transaction
MOVE — Moves cursor position
NOTIFY — Signals all frontends and backends listening on a notify condition
RESET — Restores run-time parameters for session to default values
REVOKE — Revokes access privilege from a user, a group or all users.
ROLLBACK — Aborts the current transaction
SELECT — Retrieve rows from a table or view.
SELECT INTO — Create a new table from an existing table or view
SET — Set run-time parameters for session
SHOW — Shows run-time parameters for session
UNLISTEN — Stop listening for notification
UPDATE — Replaces values of columns in a table
VACUUM — Clean and analyze a Postgres database
15. Applications
createdb — Create a new Postgres database
createuser — Create a new Postgres user
destroydb — Remove an existing Postgres database
destroyuser — Destroy a Postgres user and associated databases
initdb — Create a new Postgres database installation
initlocation — Create a secondary Postgres database storage area
pgaccessPostgres graphical interactive client
pgadminPostgres graphical interactive client
pg_dump — Extract a Postgres database into a script file
pg_dumpall — Extract all Postgres databases into a script file
postgres — Run a Postgres single-user backend
postmaster — Run the Postgres multi-user backend
psqlPostgres interactive client
vacuumdb — Clean and analyze a Postgres database
UG1. Date/Time Support
Time Zones