PostgreSQL User’s Guide - Temple University

PostgreSQL User's Guide

The PostgreSQL Development Team

Edited by

Thomas Lockhart

PostgreSQL User's Guide by The PostgreSQL Development Team

Edited by Thomas Lockhart

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

Table of Contents

Summary ......................................................................................................................................i 1. Introduction ............................................................................................................................1

What is Postgres?................................................................................................................1 A Short History of Postgres ................................................................................................2

The Berkeley Postgres Project ..................................................................................2 Postgres95 .................................................................................................................2 PostgreSQL ...............................................................................................................3 About This Release.............................................................................................................3 Resources ............................................................................................................................4 Terminology........................................................................................................................5 Notation ..............................................................................................................................6 Y2K Statement....................................................................................................................6 Copyrights and Trademarks ................................................................................................7 2. SQL Syntax .............................................................................................................................8 Key Words ..........................................................................................................................8 Reserved Key Words.................................................................................................8 Non-reserved Keywords..........................................................................................10 Expressions .......................................................................................................................11 3. Data Types.............................................................................................................................12 Numeric Types..................................................................................................................15 Monetary Type..................................................................................................................16 Character Types ................................................................................................................16 Date/Time Types...............................................................................................................17 SQL92 Conventions ................................................................................................18 Date/Time Styles .....................................................................................................19 Calendar ..................................................................................................................19 Time Zones..............................................................................................................20 Date/Time Input ......................................................................................................20 datetime ...................................................................................................................23 timespan ..................................................................................................................24 abstime ....................................................................................................................24 reltime .....................................................................................................................25 timestamp ................................................................................................................25 interval.....................................................................................................................25 tinterval ...................................................................................................................25 Boolean Type....................................................................................................................26 Geometric Types...............................................................................................................26 Point ......................................................................................................................27 Line Segment...........................................................................................................27 Box .......................................................................................................................27 Path .......................................................................................................................27 Polygon....................................................................................................................28 Circle .......................................................................................................................28 IP Version 4 Networks and Host Addresses .....................................................................28

i

CIDR ......................................................................................................................29 inet .......................................................................................................................29 4. Operators ..............................................................................................................................30 Lexical Precedence ...........................................................................................................30 General Operators .............................................................................................................32 Numerical Operators.........................................................................................................33 Geometric Operators.........................................................................................................34 Time Interval Operators....................................................................................................35 IP V4 CIDR Operators......................................................................................................36 IP V4 INET Operators ......................................................................................................37 5. Functions ...............................................................................................................................38 SQL Functions ..................................................................................................................38 Mathematical Functions....................................................................................................38 String Functions ................................................................................................................39 Date/Time Functions.........................................................................................................41 Geometric Functions.........................................................................................................42 IP V4 Functions ................................................................................................................44 6. Type Conversion...................................................................................................................45 Overview...........................................................................................................................45 Guidelines ...............................................................................................................46 Operators...........................................................................................................................47 Conversion Procedure .............................................................................................47 Examples .................................................................................................................47

Exponentiation Operator................................................................................47 String Concatenation......................................................................................48 Factorial .........................................................................................................48 Functions...........................................................................................................................49 Examples .................................................................................................................49 Factorial Function ..........................................................................................49 Substring Function.........................................................................................50 Query Targets ...................................................................................................................51 Examples .................................................................................................................51 varchar Storage ..............................................................................................51 UNION Queries ................................................................................................................51 Examples .................................................................................................................51 Underspecified Types ....................................................................................51 Simple UNION ..............................................................................................52 Transposed UNION .......................................................................................52 7. Indices and Keys...................................................................................................................53 8. Arrays....................................................................................................................................55 9. Inheritance ............................................................................................................................57 10. Multi-Version Concurrency Control ................................................................................59 Introduction.......................................................................................................................59 Transaction Isolation.........................................................................................................59 Read Committed Isolation Level ......................................................................................60 Serializable Isolation Level ..............................................................................................60 Locking and Tables...........................................................................................................61 Table-level locks .....................................................................................................61

ii

Row-level locks.......................................................................................................62 Locking and Indices..........................................................................................................62 Data consistency checks at the application level ..............................................................63

11. Setting Up Your Environment...........................................................................................64

12. Managing a Database .........................................................................................................65 Database Creation .............................................................................................................65 Alternate Database Locations ...........................................................................................66 Accessing a Database........................................................................................................67 Database Privileges .................................................................................................68 Table Privileges.......................................................................................................68 Destroying a Database ......................................................................................................68

13. Disk Storage ........................................................................................................................69

14. SQL Commands .................................................................................................................70 ABORT.............................................................................................................................70 ALTER TABLE................................................................................................................72 ALTER USER ..................................................................................................................75 BEGIN ..............................................................................................................................77 CLOSE..............................................................................................................................78 CLUSTER.........................................................................................................................80 COMMIT ..........................................................................................................................82 COPY................................................................................................................................84 CREATE AGGREGATE..................................................................................................88 CREATE DATABASE.....................................................................................................91 CREATE FUNCTION......................................................................................................93 CREATE INDEX .............................................................................................................96 CREATE LANGUAGE....................................................................................................99 CREATE OPERATOR...................................................................................................103 CREATE RULE .............................................................................................................108 CREATE SEQUENCE ...................................................................................................112 CREATE TABLE ...........................................................................................................116 CREATE TABLE AS.....................................................................................................133 CREATE TRIGGER.......................................................................................................134 CREATE TYPE..............................................................................................................136 CREATE USER..............................................................................................................140 CREATE VIEW .............................................................................................................143 DECLARE ...................................................................................................................... 146 DELETE .........................................................................................................................149 DROP AGGREGATE ....................................................................................................151 DROP DATABASE........................................................................................................153 DROP FUNCTION.........................................................................................................154 DROP INDEX ................................................................................................................156 DROP LANGUAGE.......................................................................................................158 DROP OPERATOR........................................................................................................160 DROP RULE ..................................................................................................................161 DROP SEQUENCE ........................................................................................................163 DROP TABLE................................................................................................................165 DROP TRIGGER ...........................................................................................................167 DROP TYPE...................................................................................................................169 DROP USER...................................................................................................................170

iii

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download