PostgreSQL.官方文档

PostgreSQL 8.1.0 Documentation

The PostgreSQL Global Development Group

PostgreSQL 8.1.0 Documentation

by The PostgreSQL Global Development Group

Copyright © 1996-2005 The PostgreSQL Global Development Group

Legal Notice

PostgreSQL is Copyright © 1996-2005 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.

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 PRO-

VIDED HEREUNDER IS ON AN “AS-IS” BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE

MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

Table of Contents

Preface ..........................................................................................................................................xxxiii

1. What is PostgreSQL? .......................................................................................................xxxiii

2. A Brief History of PostgreSQL........................................................................................ xxxiv

2.1. The Berkeley POSTGRES Project ...................................................................... xxxiv

5.2. What to report.....................................................................................................xxxvii

5.3. Where to report bugs ........................................................................................... xxxix

I. Tutorial ............................................................................................................................................. 1

1. Getting Started ........................................................................................................................1

1.1. Installation .................................................................................................................. 1

1.2. Architectural Fundamentals........................................................................................ 1

1.3. Creating a Database.................................................................................................... 2

1.4. Accessing a Database ................................................................................................. 3

2. The SQL Language ................................................................................................................. 6

2.1. Introduction ................................................................................................................6

2.8. Updates ..................................................................................................................... 13

2.9. Deletions................................................................................................................... 14

3. Advanced Features ................................................................................................................ 15

3.1. Introduction ..............................................................................................................15

3.2. Views ........................................................................................................................ 15

3.3. Foreign Keys............................................................................................................. 15

3.4. Transactions.............................................................................................................. 16

3.5. Inheritance ................................................................................................................ 18

3.6. Conclusion................................................................................................................ 19

II. The SQL Language...................................................................................................................... 21

4.1.2.3. Bit-String Constants ........................................................................ 26

4.1.2.4. Numeric Constants .......................................................................... 26

4.1.2.5. Constants of Other Types ................................................................ 27

4.1.3. Operators...................................................................................................... 28

4.1.4. Special Characters........................................................................................ 28

4.1.5. Comments .................................................................................................... 29

iii

4.1.6. Lexical Precedence ......................................................................................29

4.2. Value Expressions..................................................................................................... 30

4.2.1. Column References...................................................................................... 31

4.2.2. Positional Parameters................................................................................... 31

4.2.3. Subscripts..................................................................................................... 32

4.2.4. Field Selection .............................................................................................32

4.2.11. Row Constructors.......................................................................................36

4.2.12. Expression Evaluation Rules ..................................................................... 37

5. Data Definition...................................................................................................................... 39

5.1. Table Basics.............................................................................................................. 39

5.2. Default Values .......................................................................................................... 40

5.3. Constraints................................................................................................................ 41

5.3.1. Check Constraints ........................................................................................ 41

5.3.2. Not-Null Constraints.................................................................................... 43

5.3.3. Unique Constraints....................................................................................... 44

5.3.4. Primary Keys................................................................................................ 44

5.5.4. Removing a Constraint ................................................................................ 50

5.5.5. Changing a Column’s Default Value............................................................ 51

5.5.6. Changing a Column’s Data Type ................................................................. 51

5.5.7. Renaming a Column .................................................................................... 51

5.5.8. Renaming a Table ........................................................................................ 51

5.6. Privileges .................................................................................................................. 52

5.7. Schemas.................................................................................................................... 52

5.7.1. Creating a Schema ....................................................................................... 53

5.7.2. The Public Schema ...................................................................................... 54

5.7.3. The Schema Search Path.............................................................................. 54

5.9. Partitioning ............................................................................................................... 60

5.9.1. Overview...................................................................................................... 60

5.9.2. Implementing Partitioning ........................................................................... 61

5.9.3. Partitioning and Constraint Exclusion ......................................................... 64

5.10. Other Database Objects .......................................................................................... 65

5.11. Dependency Tracking............................................................................................. 66

6. Data Manipulation................................................................................................................. 67

6.1. Inserting Data ........................................................................................................... 67

6.2. Updating Data........................................................................................................... 68

6.3. Deleting Data............................................................................................................ 68

7. Queries .................................................................................................................................. 70

7.1. Overview .................................................................................................................. 70

7.2. Table Expressions ..................................................................................................... 70

7.2.1. The FROM Clause.......................................................................................... 71

7.3. Select Lists................................................................................................................ 79

7.3.1. Select-List Items .......................................................................................... 79

7.3.2. Column Labels............................................................................................. 80

7.3.3. DISTINCT .................................................................................................... 80

7.4. Combining Queries................................................................................................... 81

7.5. Sorting Rows ............................................................................................................ 81

7.6. LIMIT and OFFSET................................................................................................... 82

8. Data Types............................................................................................................................. 84

8.1. Numeric Types.......................................................................................................... 85

8.1.1. Integer Types................................................................................................ 86

8.5. Date/Time Types....................................................................................................... 93

8.5.1. Date/Time Input ........................................................................................... 94

8.5.1.1. Dates................................................................................................ 94

8.5.1.2. Times............................................................................................... 95

8.5.1.3. Time Stamps.................................................................................... 96

8.5.1.4. Intervals........................................................................................... 97

8.5.1.5. Special Values ................................................................................. 97

8.5.2. Date/Time Output ........................................................................................98

8.5.3. Time Zones ..................................................................................................98

8.5.4. Internals........................................................................................................ 99

8.7.5. Polygons..................................................................................................... 102

8.7.6. Circles ........................................................................................................ 102

8.8. Network Address Types.......................................................................................... 103

8.8.1. inet ........................................................................................................... 103

8.8.2. cidr ........................................................................................................... 103

8.8.3. inet vs. cidr............................................................................................ 104

8.8.4. macaddr .................................................................................................... 104

8.9. Bit String Types ...................................................................................................... 105