PostgreSQL中文手册：从基础到高级功能详解

5星 · 超过95%的资源需积分: 10 48 浏览量更新于2024-07-20 收藏 1.8MB PDF 举报

PostgreSQL中文手册是一份详尽的指南，专为那些想要深入理解和掌握PostgreSQL数据库系统的人设计。它涵盖了从基础到高级概念的广泛内容，适合各个层次的学习者。首先，手册的结构清晰，从"表的定义"开始，讲解了如何在PostgreSQL中创建和管理数据表，包括表的继承关系和分区，这对于理解数据组织和存储至关重要。接着，章节深入到数据类型，如数值类型、数组的使用，展示了PostgreSQL对不同类型数据的强大支持。函数和操作符部分涵盖了逻辑运算、字符串处理、位串操作，以及模式匹配、时间/日期处理、序列操作和系统信息获取，这些都是构建复杂查询和进行数据操作的基础。索引管理也是关键环节，手册介绍了不同类型的索引，如普通索引、唯一索引，以及它们如何提高查询效率。事务隔离级别是确保数据一致性的重要部分，了解并设置正确的隔离级别对于大规模并发环境至关重要。此外，手册还提供了性能提升技巧，比如使用EXPLAIN分析查询执行计划，帮助用户优化查询性能。在服务器配置方面，手册指导读者如何启动和关闭服务器进程，以及管理服务器的资源。角色和权限管理则确保数据库的安全性，让使用者明白如何正确地赋予用户不同的操作权限。数据库管理章节则是整个手册的核心，它阐述了数据库的创建、备份、恢复和维护等方面，对于数据库生命周期的全程管理具有指导意义。这份PostgreSQL中文手册不仅包含了基础知识，还深入到了高级主题，无论是初学者还是经验丰富的开发者，都能从中找到所需的信息，提升在PostgreSQL上的技能。通过阅读和实践手册中的内容，用户可以逐步成为PostgreSQL数据库的专家。

CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' )

) INHERITS (measurement);

第二种方法的创建步骤相对繁琐，但更为灵活和实用。见以下四步：

创建一个独立的数据表

(measurement_y2008m02)

，该表在创建时以将来的主表

(measurement)

为模板，包含模板表的

缺省值

(DEFAULTS)

和一致性约束

(CONSTRAINTS)

。

CREATE TABLE measurement_y2008m02

(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS);

为该表创建未来作为子表时需要使用的检查约束。

ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02

CHECK (logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01');

导入数据到该表。下面只是给出一种导入数据的方式作为例子。在导入数据之后，如有可能，还可以做进一步的数据处理，如

数据转换、过滤等。

\copy measurement_y2008m02 from 'measurement_y2008m02'

在适当的时候，或者说在需要的时候，让该表继承主表。

ALTER TABLE measurement_y2008m02 INHERIT measurement;

7). 确保 postgresql.conf 里的配置参数 constraint_exclusion 是打开的。没有这个参数，查询不会按照需要进行优化。这里我

们需要做的是确保该选项在配置文件中没有被注释掉。

/> pwd

/opt/PostgreSQL/9.1/data

/> cat postgresql.conf | grep "constraint_exclusion"

constraint_exclusion = partition # on, off, or partition

3. 分区和约束排除：

约束排除(Constraint exclusion)是一种查询优化技巧，它改进了用上面方法定义的表分区的性能。比如：

SET constraint_exclusion = on;

SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

如果没有约束排除，上面的查询会扫描 measurement 表中的每一个分区。打开了约束排除之后，规划器将检查每个分区的约束

然后再视图证明该分区不需要被扫描，因为它不能包含任何符合 WHERE 子句条件的数据行。如果规划器可以证明这个，它就把该分

区从查询规划里排除出去。

你可以使用 EXPLAIN 命令显示一个规划在 constraint_exclusion 打开和关闭情况下的不同。用上面方法设置的表的典型的缺省

规划是：

SET constraint_exclusion = off;

EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

QUERY PLAN

-----------------------------------------------------------------------------------------------

Aggregate (cost=158.66..158.68 rows=1 width=0)

-> Append (cost=0.00..151.88 rows=2715 width=0)

-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

-> Seq Scan on measurement_yy04mm02 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

-> Seq Scan on measurement_yy04mm03 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

...

-> Seq Scan on measurement_yy05mm12 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

-> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

从上面的查询计划中可以看出，PostgreSQL 扫描了所有分区。下面我们再看一下打开约束排除之后的查询计划：

SET constraint_exclusion = on;

EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-01-01';

QUERY PLAN

-----------------------------------------------------------------------------------------------

Aggregate (cost=63.47..63.48 rows=1 width=0)

-> Append (cost=0.00..60.75 rows=1086 width=0)

-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

-> Seq Scan on measurement_yy06mm01 measurement (cost=0.00..30.38 rows=543 width=0)

Filter: (logdate >= '2006-01-01'::date)

请注意，约束排除只由 CHECK 约束驱动，而不会由索引驱动。

目前版本的 PostgreSQL 中该配置的缺省值是 partition，该值是介于 on 和 off 之间的一种行为方式，即规划器只会将约束排除

应用于基于分区表的查询，而 on 设置则会为所有查询都进行约束排除，那么对于普通数据表而言，也将不得不承担由该机制而产生

的额外开销。

约束排除在使用时有以下几点注意事项：

1). 约束排除只是在查询的 WHERE 子句包含约束的时候才生效。一个参数化的查询不会被优化，因为在运行时规划器不知道该

参数会选择哪个分区。因此像 CURRENT_DATE 这样的函数必须避免。把分区键值和另外一个表的字段连接起来也不会得到优化。

2). 在 CHECK 约束里面要避免跨数据类型的比较，因为目前规划器会无法证明这样的条件为假。比如，下面的约束会在 x 是整数

字段的时候可用，但是在 x 是一个 bigint 的时候不能用：

CHECK (x = 1)

对于 bigint 字段，我们必须使用类似下面这样的约束：

CHECK (x = 1::bigint)

这个问题并不仅仅局限于 bigint 数据类型，它可能会发生在任何约束的缺省数据类型与其比较的字段的数据类型不匹配的场合。

在提交的查询里的跨数据类型的比较通常是 OK 的，只是不能在 CHECK 条件里。

3). 在主表上的 UPDATE 和 DELETE 命令并不执行约束排除。

4). 在规划器进行约束排除时，主表上的所有分区的所有约束都将会被检查，因此，大量的分区会显著增加查询规划的时间。

5). 在执行 ANALYZE 语句时，要为每一个分区都执行该命令，而不是仅仅对主表执行该命令。

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

一、数值类型：

下面是 PostgreSQL 所支持的数值类型的列表和简单说明：

名字

存储空间

描述

范围

smallint

2 字节

小范围整数

-32768 到 +32767

integer

4 字节

常用的整数

-2147483648 到 +2147483647

bigint

8 字节

大范围的整数

-9223372036854775808 到 9223372036854775807

decimal

变长

用户声明精度，精确

无限制

numeric

变长

用户声明精度，精确

无限制

real

4 字节

变精度，不精确

6 位十进制数字精度

double

8 字节

变精度，不精确

15 位十进制数字精度

serial

4 字节

自增整数

1 到 +2147483647

bigserial

8 字节

大范围的自增整数

1 到 9223372036854775807

1. 整数类型：

类型 smallint、integer 和 bigint 存储各种范围的全部是数字的数，也就是没有小数部分的数字。试图存储超出范围以外的数值

将导致一个错误。常用的类型是 integer，因为它提供了在范围、存储空间和性能之间的最佳平衡。一般只有在磁盘空间紧张的时候

才使用 smallint。而只有在 integer 的范围不够的时候才使用 bigint，因为前者(integer)绝对快得多。

2. 任意精度数值：

类型 numeric 可以存储最多 1000 位精度的数字并且准确地进行计算。因此非常适合用于货币金额和其它要求计算准确的数量。

不过，numeric 类型上的算术运算比整数类型或者浮点数类型要慢很多。

numeric 字段的最大精度和最大比例都是可以配置的。要声明一个类型为 numeric 的字段，你可以用下面的语法：

NUMERIC(precision,scale)

比如数字 23.5141 的精度为 6，而刻度为 4。

在目前的 PostgreSQL 版本中，decimal 和 numeric 是等效的。

3. 浮点数类型：

数据类型 real 和 double 是不准确的、牺牲精度的数字类型。不准确意味着一些数值不能准确地转换成内部格式并且是以近似的

形式存储的，因此存储后再把数据打印出来可能显示一些缺失。

4. Serial(序号)类型：

serial 和 bigserial 类型不是真正的类型，只是为在表中设置唯一标识做的概念上的便利。

CREATE TABLE tablename (

colname SERIAL

);

等价于

CREATE SEQUENCE tablename_colname_seq;

CREATE TABLE tablename(

colname integer DEFAULT nextval('tablename_colname_seq') NOT NULL

);

这样，我们就创建了一个整数字段并且把它的缺省数值安排为从一个序列发生器取值。应用了一个 NOT NULL 约束以确保空值不

会被插入。在大多数情况下你可能还希望附加一个 UNIQUE 或者 PRIMARY KEY 约束避免意外地插入重复的数值，但这个不是自动

发生的。因此，如果你希望一个序列字段有一个唯一约束或者一个主键，那么你现在必须声明，就像其它数据类型一样。

还需要另外说明的是，一个 serial 类型创建的序列在其所属字段被删除时，该序列也将被自动删除，但是其它情况下是不会被删

除的。因此，如果你想用同一个序列发生器同时给几个字段提供数据，那么就应该以独立对象的方式创建该序列发生器。

二、字符类型：

下面是 PostgreSQL 所支持的字符类型的列表和简单说明：

名字

描述

varchar(n)

变长，有长度限制

char(n)

定长,不足补空白

text

变长，无长度限制

SQL 定义了两种基本的字符类型，varchar(n)和 char(n)，这里的 n 是一个正整数。两种类型都可以存储最多 n 个字符长的字

串，试图存储更长的字串到这些类型的字段里会产生一个错误，除非超出长度的字符都是空白，这种情况下该字串将被截断为最大长

度。如果没有长度声明，char 等于 char(1)，而 varchar 则可以接受任何长度的字串。

MyTest=> CREATE TABLE testtable(first_col varchar(2));

CREATE TABLE

MyTest=> INSERT INTO testtable VALUES('333'); --

插入字符串的长度，超过其字段定义的长度，因此报错。

ERROR: value too long for type character varying(2)

插入字符串中，超出字段定义长度的部分是空格，因此可以插入，但是空白符被截断。

MyTest=> INSERT INTO testtable VALUES('33 ');

INSERT 0 1

MyTest=> SELECT * FROM testtable;

first_col

-----------

(1 row)

这里需要注意的是，如果是将数值转换成 char(n)或者 varchar(n)，那么超长的数值将被截断成 n 个字符，而不会抛出错误。

MyTest=> select 1234::varchar(2);

varchar

---------

(1 row)

最后需要提示的是，这三种类型之间没有性能差别，只不过是在使用 char 类型时增加了存储尺寸。虽然在某些其它的数据库系统

里，char(n)有一定的性能优势，但在 PostgreSQL 里没有。在大多数情况下，应该使用 text 或者 varchar。

三、日期/时间类型：

下面是 PostgreSQL 所支持的日期/时间类型的列表和简单说明：

名字

存储空间

描述

最低值

最高值

分辨率

timestamp[无时区]

8 字节

包括日期和时间

4713 BC

5874897AD

1 毫秒/14 位

timestamp[含时区]

8 字节

日期和时间，带时区

4713 BC

5874897AD

1 毫秒/14 位

interval

12 字节

时间间隔

-178000000 年

178000000 年

1 毫秒/14 位

date

4 字节

只用于日期

4713 BC

32767AD

1 天

time[无时区]

8 字节

只用于一日内时间

00:00:00

24:00:00

1 毫秒/14 位

1. 日期/时间输入:

任何日期或者时间的文本输入均需要由单引号包围，就象一个文本字符串一样。

1). 日期:

以下为合法的日期格式列表：

例子

描述

January 8,

1999

在任何 datestyle 输入模式下都无歧义

1999-01-08

ISO-8601 格式，任何方式下都是 1999 年 1 月 8 号，(建议格式)

1/8/1999

歧义，在 MDY 下是 1 月 8 号；在 DMY 模式下读做 8 月 1 日

剩余95页未读，继续阅读

子牛

粉丝: 3
资源: 20

PostgreSQL中文手册：从基础到高级功能详解

PostgreSQL 中文手册（带书签和目录）

PostgreSQL 9.6 中文手册 带书签 高清

PostgreSQL中文手册9.1

postgresql中文手册

postgresql 11.2中文手册下载

postgresql 12.2 版本手册下载

postgresql 手册chm

postgresql設爲中文

postgresql9 admin cookbook

postgresql 10中文版

最新资源

PostgreSQL 9.6 中文手册带书签高清