PostgreSQL中文学习手册全览

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

"这是《postgresql的中文学习手册》，涵盖了PostgreSQL数据库的多个核心概念和技术，包括数据表、模式、表的继承和分区、数据类型、函数和操作符、索引、事务隔离、性能优化、服务器配置、角色与权限、数据库管理、数据库维护、系统表和系统视图以及客户端命令的使用等。" 在PostgreSQL学习手册中，读者可以深入理解以下几个关键知识点： 1. **数据表**: 表是数据库中的基本元素，手册详细介绍了如何定义表，包括字段、数据类型和约束。 2. **模式Schema**: 模式是组织数据库对象的一种方式，它帮助管理和隔离不同用户的对象。 3. **表的继承和分区**: 继承允许创建一个父表和多个子表，共享相同的结构；分区则是将大表分解成更小、更易管理的部分，以优化查询性能。 4. **数据类型**: 包括数值类型、字符串、数组等，理解每种类型的应用场景对于有效存储和处理数据至关重要。 5. **函数和操作符**: 手册详细列举了逻辑操作符、字符串、位串、模式匹配、时间/日期相关的函数和操作符，这些都是SQL编程中常用的工具。 6. **索引**: 索引能显著提高查询速度，手册介绍了多种索引类型，如B树、哈希和唯一索引，并讨论了它们的适用情况。 7. **事务隔离**: 事务管理是数据库一致性的重要保障，手册解释了不同级别的事务隔离及其对并发操作的影响。 8. **性能提升技巧**: 如使用EXPLAIN分析查询计划，有助于优化查询性能。 9. **服务器配置**: 学习如何启动和关闭服务器进程，以及调整配置参数以满足特定需求。 10. **角色和权限**: 掌握权限控制，确保数据安全，了解如何创建和管理用户角色。 11. **数据库管理**: 涵盖了数据库的创建、删除和备份恢复等基础操作。 12. **数据库维护**: 包括释放磁盘空间、更新统计信息和定期重建索引等日常维护任务。 13. **系统表**: 系统表记录了数据库内部的信息，比如pg_class表示所有表的信息，pg_authid管理用户身份。 14. **系统视图**: 系统视图提供了易于查询的数据库元数据，如pg_tables、pg_indexes等，方便管理员监控和诊断。 15. **客户端命令**: 客户端工具如psql、pg_dump和pg_restore，用于交互式查询、数据库备份和恢复。通过这份中文学习手册，无论是初学者还是经验丰富的DBA，都能找到所需的信息，全面掌握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页未读，继续阅读

闪电侠风行

粉丝: 74
资源: 57

PostgreSQL中文学习手册全览

postgresql 中文学习手册

PostgreSQL中文手册9.2

POSTGRESQL中文学习手册

PostgreSQL中文学习手册：从基础到进阶

Postgresql中文参考手册

postgresql8.3中文手册

postgresql 8.3 中文手册

PostgreSQL9.4.4中文手册

PostgreSQL 12.2 中文手册

postgresql9.1中文学习手册PDF格式最新版本

最新资源