PostgreSQL中文手册：详尽学习指南与实用技巧

需积分: 14 194 浏览量更新于2024-07-25 收藏 2.3MB PDF 举报

PostgreSQL中文手册是一份详尽且全面的文档，旨在帮助用户理解和掌握PostgreSQL数据库管理系统。该手册分为多个章节，涵盖了从基础概念到高级功能的深入学习。以下是主要内容概览： 1. **表的定义**：这部分介绍了如何在PostgreSQL中创建和管理数据表，包括字段定义、数据类型等。 2. **模式Schema**：阐述了数据库模式的概念，即组织和管理数据库对象的方式，如表、视图等。 3. **表的继承和分区**：讲解了如何通过继承和分区来扩展和优化表的设计，提高查询效率。 4. **数据类型**：详细列举了PostgreSQL支持的各种数据类型，如数值类型、数组、位串等，以及它们的使用和特性。 5. **函数和操作符**：分三个部分介绍各种逻辑操作符、字符串和位串处理函数，以及模式匹配、时间/日期处理和序列操作。 6. **索引**：涵盖了索引的基础概念，不同类型索引（如B树、哈希等）的使用，以及唯一索引的设置。 7. **事务隔离**：探讨了事务管理在PostgreSQL中的实现，确保数据的一致性和完整性。 8. **性能提升**：给出了实用的性能优化技巧，如使用`EXPLAIN`分析查询性能。 9. **服务器配置**：涉及服务器的启动、关闭，以及基本的配置设置。 10. **角色和权限**：解释了在PostgreSQL中如何管理和分配不同用户的权限，保证数据安全。 11. **数据库管理**：包括数据库的创建、备份、恢复，以及日常维护任务。 12. **系统表和视图**：列出了重要的系统表，如`pg_class`、`pg_index`等，以及用于查看数据库结构和状态的系统视图。 13. **客户端命令**：介绍了常用的客户端工具，如`pg_dump`用于数据备份，`pg_restore`用于数据恢复。通过这份手册，无论是初学者还是进阶用户，都能找到所需的信息，以便有效地使用和管理PostgreSQL数据库。每章内容都详细且实用，有助于读者逐步掌握PostgreSQL的各项功能和最佳实践。

从上面的查询计划中可以看出，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 日

剩余93页未读，继续阅读

it_cyk

粉丝: 0

PostgreSQL中文手册：详尽学习指南与实用技巧

PostgreSQL中文手册：从基础到高级

PostgreSQL中文手册：chm格式全文检索官方文档

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

postgreSQL中文手册

postgresql 中文手册

PostgreSQL中文手册9.2

postgresql中文手册9.1

PostgreSQL中文手册9.1

postgresql中文手册.zip

PostgreSQL中文手册.zip_justm6y_postgresql

最新资源