PostgreeSQL数据库中文学习手册

目 录

PostgreSQL 学习手册(数据表) .............................................................................................................................................. 4

一、表的定义： ............................................................................................................................................................... 4

PostgreSQL 学习手册(模式 Schema) ..................................................................................................................................... 9

PostgreSQL 学习手册(表的继承和分区) ............................................................................................................................ 10

一、表的继承： ............................................................................................................................................................. 10

PostgreSQL 学习手册(常用数据类型) ................................................................................................................................ 16

PostgreSQL 学习手册(函数和操作符<二>) ........................................................................................................................ 30

六、模式匹配： ............................................................................................................................................................. 30

八、时间/日期函数和操作符： .................................................................................................................................... 33

PostgreSQL 学习手册(函数和操作符<三>) ........................................................................................................................ 35

九、序列操作函数： ..................................................................................................................................................... 35

十二、系统信息函数： ................................................................................................................................................. 38

PostgreSQL 学习手册(索引) ................................................................................................................................................ 42

一、索引的类型： ......................................................................................................................................................... 42

四、唯一索引： ............................................................................................................................................................. 43

PostgreSQL 学习手册(事物隔离) ...................................................................................................................................... 45

一、概述： ..................................................................................................................................................................... 54

PostgreSQL 学习手册(数据库维护) .................................................................................................................................. 56

一、恢复磁盘空间： ..................................................................................................................................................... 56

二、更新规划器统计： ................................................................................................................................................. 57

四、定期重建索引： ..................................................................................................................................................... 59

PostgreSQL 学习手册(系统表) ............................................................................................................................................ 61

一、pg_class: .................................................................................................................................................................. 61

三、pg_attrdef: ............................................................................................................................................................... 63

四、pg_authid: ................................................................................................................................................................ 64

五、pg_auth_members: ................................................................................................................................................. 64

四、pg_user: ................................................................................................................................................................... 69

五、pg_roles: .................................................................................................................................................................. 69

六、pg_rules: .................................................................................................................................................................. 69

七、pg_settings: ............................................................................................................................................................. 70

PostgreSQL 学习手册(客户端命令<一>) ............................................................................................................................ 70

零、口令文件： ............................................................................................................................................................. 70

PostgreSQL 学习手册(客户端命令<二>) ............................................................................................................................ 75

七、pg_dump:................................................................................................................................................................. 75

八、pg_restore: .............................................................................................................................................................. 77

PostgreSQL 学习手册(SQL 语言函数) ................................................................................................................................. 83

一、基本概念： ............................................................................................................................................................. 83

对于任何一种关系型数据库而言，表都是数据存储的最核心、最基础的对象单元。现在就让我们从这里起步吧。

1. 创建表：

CREATE TABLE products (

product_no integer,

name text,

price numeric

);

2. 删除表：

price numeric DEFAULT 9.99 --DEFAULT

9.99

price

);

CREATE TABLE products (

product_no SERIAL, --SERIAL

name text,

price numeric DEFAULT 9.99

);

输出为：

NOTICE: CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column

price numeric CONSTRAINT positive_price CHECK (price > 0)

);

CREATE TABLE products (

product_no integer NOT NULL,

name text NOT NULL,

price numeric

);

CREATE TABLE products (

product_no integer UNIQUE,

name text,

price numeric

);

CREATE TABLE products (

product_no integer,

name text,

price numeric,

c integer,

UNIQUE (a, c)

);

CREATE TABLE products (

product_no integer CONSTRAINT must_be_different UNIQUE,

name text,

price numeric

);

name text,