PostgreSQL User's Guide

The PostgreSQL Development Team

Edited by

Thomas Lockhart

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

Table of Contents

Summary

1. Introduction

What is Postgres?

A Short History of Postgres

The Berkeley Postgres Project
Postgres95
PostgreSQL

About This Release

Copyrights and Trademarks

2. SQL Syntax

Key Words

Reserved Key Words
Non-reserved Keywords

3. Data Types

SQL92 Conventions
Date/Time Styles
Calendar
Time Zones
Date/Time Input
datetime
timespan
abstime
reltime
timestamp
interval
tinterval

Boolean Type

Geometric Types

Point
Line Segment
Box
Path
Polygon
Circle

IP Version 4 Networks and Host Addresses

CIDR
inet

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

Overview

Guidelines

Operators

Conversion Procedure
Examples

Functions

Examples

Query Targets

Examples

UNION Queries

Examples

7. Indices and Keys

8. Arrays

9. Inheritance

10. Multi-Version Concurrency Control

Introduction

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
pgaccess — Postgres graphical interactive client
pgadmin — Postgres 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
psql — Postgres interactive client
vacuumdb — Clean and analyze a Postgres database

UG1. Date/Time Support

Time Zones
History

Bibliography

		Next
		Summary