PostgreSQL 11数据库管理员指南第五章：数据表及数据

本书目录请见：PostgreSQL 11数据库管理员指南

本章包含一列有关数据表和使用其中所含数据的小节。很多小节包含通用的建议，但带有具体的PostgreSQL示例。

我遇到的有些系统管理员仅使用数据库服务端的外部功能。而数据库内部的则是由其他人复杂。

善待数据，数据才会善待你。保持数据整洁，你的查询就会运行的更快速并且产生更少的应用错误。还会在业务中获取很多的朋友。在深更半夜被叫起来处理数据问题就一点也不酷了。

本章中，我们将讲解如下小节：

为数据库对象选择好名称
处理带有引号名称的对象
强制对列使用相同名称和定义
识别并删除重复数据
避免重复行
为一组数据查找唯一键
生成测试数据
随机数据取样
从电子表格加载数据
从普通文件加载数据
使用服务端事务存储过程进行批量数据修改

为数据库对象选择好名称

帮助其他人理解一个数据库最简单的方式是确保所有对象的名称有意义。

怎样让名称有意义呢？

准备工作

花一点时间来反思你的数据库，以确保对其目标和主要用例有清晰的看法。这是因为本节中的所有项描述某种你需要根据具体状况下的看法来仔细考虑命名选择。

如何实现…

以下是一些在对数据库对象命名时应当考虑的点：

命名遵循已有标准和实践。发明新标准并没有什么帮助，强化现有标准则会有帮助。
命名清晰地描述角色或数据表内容。
对于主要数据表，使用简短有力的名称。
按照所关联的数据表对查询数据表命名，如account_status。
对于连接或关联数据表，使用所关联所有主表的名称，如customer_account。
确保命名对其它相似名称可进行明确的区分。
使用一致的简写。
使用下划线。默认不保留大写，因此像Java中那样使用驼峰命名，如customerAccount会让它们无法阅读。参见处理带有引号名称的对象一节。
一致使用复数，或者一点都不使用。
使用后缀来标识内容类型或对象的域。PostgreSQL已对自动生成的对象使用后缀。
提前考虑。不要选择引用对象当前角色或地点的名称。因此不要因服务器在伦敦而使用名为London的数据表。这个服务器很有可能会迁移到洛杉矶。
提前考虑。不要选择暗示仅表示某一类实例的名称，如名为TEST或BACKUP_DATA的数据表。另一方面，这种信息可以放到数据库名里，通常在数据库中不常使用。
在出现长表名时避免使用缩略词。例如money_allocation_decision比MAD就好得多。在PostgreSQL将名称转换为小写时这尤其重要，这样缩略词会很不明确。
表名常用作创建其它对象的根，因不要添加表格后缀或相似的内容。

扩展知识…

PostgreSQL中索引的标准名称如下：

{tablename}_{columnname(s)}_{suffix}

1	{tablename}_{columnname(s)}_{suffix}

这里的suffix是以下的一种：

pkey: 用于主键约束
key: 用于唯一约束
excl: 用于排它约束
idx: 用于任意其它类型的索引

所有序列的标准后缀是seq。

数据表可以有在每个事件启动的多个触发器。触发器按字母顺序执行，因此触发器名称应该有某种动作名来进行区别并用于指定顺序。在触发器名称前添加INSERT、UPDATE或DELETE看起来是不错的想法，但在触发器兼有UPDATE和DELETE作用时就会产生混淆，所有这些可能会导致混乱。

ℹ️触发器名称的字母排序总是遵循C语言的 locale 库，而不论实际的locale设置是什么。如果你的触发器名称使用非ASCII字符，那么实际排序可能会和所预期的不同。

以下示例显示在C的 locale 库中如何对字符è和é进行排序。可以修改locale及字符串列表来研究locale如何影响排序。

WITH a(x) AS (
 VALUES ('è'),('é')
) SELECT *
FROM a
ORDER BY x
COLLATE "C";

WITH a(x) AS (

VALUES ('è'),('é')

) SELECT *

FROM a

ORDER BY x

COLLATE "C";

对于触发器一个有用的命令惯例如下：

{tablename}_{actionname}_{after|before}_trig

1	{tablename}_{actionname}_{after\|before}_trig

如果你发现自己有奇怪或不规则的对象名称，使用RENAME子命令来保持名称整齐是一个不错的方法。示例如下：

ALTER INDEX badly_named_index RENAME TO tablename_status_idx;

1	ALTER INDEX badly_named_index RENAME TO tablename_status_idx;

处理带有引号名称的对象

如果我们将数据表名包裹在双引号中的话PostgreSQL对象名称可以包含空格和大小写混合的字符。这会带来一些困难，因此本节旨在当你遇到某些问题时帮助到你。

大小写敏感的问题通常对于习惯于其它数据库系统的人会是一个问题，如MySQL，或者对于那些面临想要从MySQL迁移出去的挑战的人。

准备工作

首先，我们来创建一个使用引号和混合大小写名称的数据表，如下：

CREATE TABLE "MyCust"
AS
SELECT * FROM cust;

CREATE TABLE "MyCust"

SELECT * FROM cust;

如何实现…

如果我们尝试不使用正确的大小写访问这些表，会得到如下错误：

postgres=# SELECT count(*) FROM mycust;
ERROR: relation "mycust" does not exist 
LINE 1: SELECT * FROM mycust;

postgres=# SELECT count(*) FROM mycust;

ERROR: relation "mycust" does not exist

LINE 1: SELECT * FROM mycust;

所以我们以正确的大小写进行书写：

postgres=# SELECT count(*) FROM MyCust;
ERROR: relation "mycust" does not exist
LINE 1: SELECT * FROM mycust;

postgres=# SELECT count(*) FROM MyCust;

ERROR: relation "mycust" does not exist

LINE 1: SELECT * FROM mycust;

但依然失败，事实报错相同。

如果想要访问通过引号名称创建的数据表，那么也必须使用引号名称来访问，如下：

postgres=# SELECT count(*) FROM "MyCust";

1	postgres=# SELECT count(*) FROM "MyCust";

输出如下：

 count
-------
 5
(1 row)

count

-------

(1 row)

使用规则是，如果使用引号名称创建数据表，那么需要使用引号名称来书写SQL语句。相应地，如果使用引号名称来编写SQL，那么也应当使用引号名称来创建数据表。

运行原理…

PostgreSQL对SQL中使用的所有名称转为小写。思考如下命令：

SELECT * FROM mycust;

1	SELECT * FROM mycust;

它与下面的命令完全相同：

SELECT * FROM MYCUST;

1	SELECT * FROM MYCUST;

也与下面的命令完全一致：

SELECT * FROM MyCust;

1	SELECT * FROM MyCust;

但是，与下面的命令则不是同一回事：

SELECT * FROM "MyCust";

1	SELECT * FROM "MyCust";

扩展知识…

如果通过用于创建对象名的数据表提取值，那么可能需要使用一个名为quote_ident()的便捷函数。该函数在需要用到对象名时把双引号放在值的两边，如下所示：

postgres=# SELECT quote_ident('MyCust');
 quote_ident
-------------
 "MyCust"
(1 row)
postgres=# SELECT quote_ident('mycust');
 quote_ident
-------------
 mycust
(1 row)

postgres=# SELECT quote_ident('MyCust');

quote_ident

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

"MyCust"

(1 row)

postgres=# SELECT quote_ident('mycust');

quote_ident

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

mycust

(1 row)

quote_ident()函数在通过基于PL/pgSQL函数中的变量名创建数据表时尤其有用，如下：

EXECUTE 'CREATE TEMP TABLE ' || quote_ident(tablename) ||
 '(col1 INTEGER);'

1 2	EXECUTE 'CREATE TEMP TABLE ' \|\| quote_ident(tablename) \|\| '(col1 INTEGER);'

强制对列使用相同名称和定义

良好设计的数据库有平滑、易于理解的定义。这允许所有用户理解每张表中数据的含义。这也是解决数据质量问题的重要方法。

准备工作

如果你想要在本节中运行查询作为测试，那么使用下面的示例。或者可以直接在你自己的数据库中查看问题：

CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE TABLE s1.X(col1 smallint,col2 TEXT);
CREATE TABLE s2.X(col1 integer,col3 NUMERIC);

CREATE SCHEMA s1;

CREATE SCHEMA s2;

CREATE TABLE s1.X(col1 smallint,col2 TEXT);

CREATE TABLE s2.X(col1 integer,col3 NUMERIC);

如何实现…

首先，我们将展示如何对目录执行查询来识别在不同表中以不同方法定义的列。我们使用如下的information_schema查询：

SELECT
 table_schema
,table_name
,column_name
,data_type
 ||coalesce(' ' || text(character_maximum_length), '')
 ||coalesce(' ' || text(numeric_precision), '')
 ||coalesce(',' || text(numeric_scale), '')
 as data_type
FROM information_schema.columns
WHERE column_name IN
(SELECT
 column_name
FROM
(SELECT
 column_name
 ,data_type
 ,character_maximum_length
 ,numeric_precision
 ,numeric_scale
 FROM information_schema.columns
 WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
 GROUP BY
 column_name
 ,data_type
 ,character_maximum_length
 ,numeric_precision
 ,numeric_scale
) derived
GROUP BY column_name
HAVING count(*) > 1
)
AND table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY column_name
;

SELECT

table_schema

,table_name

,column_name

,data_type

||coalesce(' ' || text(character_maximum_length), '')

||coalesce(' ' || text(numeric_precision), '')

||coalesce(',' || text(numeric_scale), '')

as data_type

FROM information_schema.columns

WHERE column_name IN

(SELECT

column_name

FROM

(SELECT

column_name

,data_type

,character_maximum_length

,numeric_precision

,numeric_scale

FROM information_schema.columns

WHERE table_schema NOT IN ('information_schema', 'pg_catalog')

GROUP BY

column_name

,data_type

,character_maximum_length

,numeric_precision

,numeric_scale

) derived

GROUP BY column_name

HAVING count(*) > 1

)

AND table_schema NOT IN ('information_schema', 'pg_catalog')

ORDER BY column_name

;

查询给出如下的输出：

 table_schema | table_name | column_name | data_type
--------------+------------+-------------+---------------
 s1 | x | col1 | smallint 16,0
 s2 | x | col1 | integer 32,0
(2 rows)

table_schema | table_name | column_name | data_type

--------------+------------+-------------+---------------

s1 | x | col1 | smallint 16,0

s2 | x | col1 | integer 32,0

(2 rows)

对比两个给定的表更为复杂，因为两张表可能会很多相似的地方而仅有少量的不同。以下查询查找所有具有不同定义的同名数据表（因此使用不同的模式）：

WITH table_definition as
( SELECT table_schema
 , table_name
 , string_agg( column_name || ' ' || data_type
 , ',' ORDER BY column_name
 ) AS def
 FROM information_schema.columns
 WHERE table_schema NOT IN ( 'information_schema'
 , 'pg_catalog')
 GROUP BY table_schema
 , table_name
)
 , unique_definition as
( SELECT DISTINCT table_name
 , def
 FROM table_definition
)
 , multiple_definition as
( SELECT table_name
 FROM unique_definition
 GROUP BY table_name
 HAVING count( * ) > 1
)
SELECT table_schema
 , table_name
 , column_name
 , data_type
 FROM information_schema.columns
 WHERE table_name
 IN ( SELECT table_name
 FROM multiple_definition )
 ORDER BY table_name
 , table_schema
 , column_name
;

WITH table_definition as

( SELECT table_schema

, table_name

, string_agg( column_name || ' ' || data_type

, ',' ORDER BY column_name

) AS def

FROM information_schema.columns

WHERE table_schema NOT IN ( 'information_schema'

, 'pg_catalog')

GROUP BY table_schema

, table_name

)

, unique_definition as

( SELECT DISTINCT table_name

, def

FROM table_definition

)

, multiple_definition as

( SELECT table_name

FROM unique_definition

GROUP BY table_name

HAVING count( * ) > 1

)

SELECT table_schema

, table_name

, column_name

, data_type

FROM information_schema.columns

WHERE table_name

IN ( SELECT table_name

FROM multiple_definition )

ORDER BY table_name

, table_schema

, column_name

;

输出如下：

 table_schema | table_name | column_name | data_type
--------------+------------+-------------+-----------
 s1 | x | col1 | smallint
 s1 | x | col2 | text
 s2 | x | col1 | integer
 s2 | x | col3 | numeric
(4 rows)

table_schema | table_name | column_name | data_type

--------------+------------+-------------+-----------

s1 | x | col1 | smallint

s1 | x | col2 | text

s2 | x | col1 | integer

s2 | x | col3 | numeric

(4 rows)

运行原理…

数据表的定义位于PostgreSQL中，并使用 Information Schema目录视图来进行访问。

定义不同可能有具体的原因。我们排除了PostgreSQL自己的内部表，因为在两个目录中有很类似的名称：PostgreSQL对SQL标准信息模式的实现以及PostgreSQL自己内部的pg_catalog模式。

这些查询非常的复杂。事实上，我们还可以对这些查询添加更多的复杂度来对比默认值或约束等内容。这一基本想法可朝着不同方向进行扩展。

扩展知识…

我们可以使用如下函数对比任意两张表的定义：

CREATE OR REPLACE FUNCTION diff_table_definition
(t1_schemaname text
,t1_tablename text
,t2_schemaname text
,t2_tablename text)
RETURNS TABLE
(t1_column_name text
,t1_data_type text
,t2_column_name text
,t2_data_type text
)
LANGUAGE SQL
as
$$
SELECT
 t1.column_name
,t1.data_type
,t2.column_name
,t2.data_type
FROM
 (SELECT column_name, data_type
 FROM information_schema.columns
 WHERE table_schema = $1
 AND table_name = $2
 ) t1
 FULL OUTER JOIN
 (SELECT column_name, data_type
 FROM information_schema.columns
 WHERE table_schema = $3
 AND table_name = $4
 ) t2
 ON t1.column_name = t2.column_name
 AND t1.data_type = t2.data_type
WHERE t1.column_name IS NULL OR t2.column_name IS NULL
;
$$;

CREATE OR REPLACE FUNCTION diff_table_definition

(t1_schemaname text

,t1_tablename text

,t2_schemaname text

,t2_tablename text)

RETURNS TABLE

(t1_column_name text

,t1_data_type text

,t2_column_name text

,t2_data_type text

)

LANGUAGE SQL

SELECT

t1.column_name

,t1.data_type

,t2.column_name

,t2.data_type

FROM

(SELECT column_name, data_type

FROM information_schema.columns

WHERE table_schema = $1

AND table_name = $2

) t1

FULL OUTER JOIN

(SELECT column_name, data_type

FROM information_schema.columns

WHERE table_schema = $3

AND table_name = $4

) t2

ON t1.column_name = t2.column_name

AND t1.data_type = t2.data_type

WHERE t1.column_name IS NULL OR t2.column_name IS NULL

;

$$;

以下是对其使用的输出：

# select diff_table_definition('s1','x','s2','x');
 diff_table_definition
-----------------------
 (col1,smallint,,)
 (col2,text,,)
 (,,col3,numeric)
 (,,col1,integer)
(4 rows)

# select diff_table_definition('s1','x','s2','x');

diff_table_definition

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

(col1,smallint,,)

(col2,text,,)

(,,col3,numeric)

(,,col1,integer)

(4 rows)

识别并删除重复数据

关系型数据库采用的想法是数据项可以独立标识。不管我们有多努力，总会在某处存在不良数据。本节展示如何进行诊断以及清理这一问题。

准备工作

我们先通过查看示例数据表cust。它在customerid中存在重复值：

postgres=# SELECT * FROM cust;
 customerid | firstname | lastname | age
------------+-----------+----------+-----
 1 | Philip | Marlowe | 38
 2 | Richard | Hannay | 42
 3 | Holly | Martins | 25
 4 | Harry | Palmer | 36
 4 | Mark | Hall | 47
(5 rows)

postgres=# SELECT * FROM cust;

customerid | firstname | lastname | age

------------+-----------+----------+-----

1 | Philip | Marlowe | 38

2 | Richard | Hannay | 42

3 | Holly | Martins | 25

4 | Harry | Palmer | 36

4 | Mark | Hall | 47

(5 rows)

在删除重复数据之前，记住有时不是数据错了，而是你对它的理解错了。在这种情况下，可能是你还没有恰当地规范数据库模型，需要包含其它表来处理数据的形式。可能还会发现重复行由之前你在数据加载过程的某处决定排除某列而造成的。反复查看，一次必删除。

如何实现…

首先，使用如下查询定位到重复内容：

CREATE UNLOGGED TABLE dup_cust AS
SELECT *
FROM cust
WHERE customerid IN
 (SELECT customerid
 FROM cust
 GROUP BY customerid
 HAVING count(*) > 1);

CREATE UNLOGGED TABLE dup_cust AS

SELECT *

FROM cust

WHERE customerid IN

(SELECT customerid

FROM cust

GROUP BY customerid

HAVING count(*) > 1);

我们在单独数据表中保存重复列表，因为在数据表很大的时候查询会很慢，因此不希望运行多次。

ℹ️一个UNLOGGED表可由更少的I/O创建，因为它不写入WAL（预写日志系统）。它比临时表要好，因为它不会在断开重连时消失。硬币的另一面是在崩溃时会丢失所有内容，但并不是很糟糕，因为你在使用一个无日志表，然后告诉PostgreSQL你能够在出现崩溃事件（机率不大）时重建数据表中的所有内容。

结果可用于手动识别不良数据，并且可通过执行如下步骤来解决问题：

在需要时合并这两行来提供数据的更好展现。可能会使用一行中的值来更新你决定保留的那一行的值，如下所示：

UPDATE cust SET age = 47 WHERE customerid = 4 AND lastname = 'Palmer';

1
2
3
4

UPDATE cust
SET age = 47
WHERE customerid = 4
AND lastname = 'Palmer';
删除剩下不需要的行：

DELETE FROM cust WHERE customerid = 4 AND lastname = 'Hall';

1
2
3

DELETE FROM cust
WHERE customerid = 4
AND lastname = 'Hall';

在某些情况下，数据行可能完全相同，比如在new_cust数据表中，像下面这样：

postgres=# SELECT * FROM new_cust;
 customerid
------------
 1
 2
 3
 4
 4
(5 rows)

postgres=# SELECT * FROM new_cust;

customerid

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

(5 rows)

不同于前面示例，完全无法分离数据，因此我们不在没有任何手动操作的情况下删除行。SQL是基于数据集的语言，因此从数据集中仅挑出一行比大部人所希望的要困难得一些。

在这类情况下，我们应使用稍有不同的流程来监测重复内容。我们将使用一个名为ctid的隐藏列。它表示你所观察的行的物理位置，例如，重复行会有不同的ctid值。步骤如下：

首先开启一个事务：

BEGIN;

1

BEGIN;
然后我们锁定数据表来防止任何INSERT, UPDATE或DELETE操作，它们会改变重复列表以及/或改变它们的ctid值：

LOCK TABLE new_cust IN SHARE ROW EXCLUSIVE MODE;

1

LOCK TABLE new_cust IN SHARE ROW EXCLUSIVE MODE;
现在我们定位到所有的重复内容，记录最小ctid值，这样我们不会删除该值：

CREATE TEMPORARY TABLE dups_cust AS SELECT customerid, min(ctid) AS min_ctid FROM new_cust GROUP BY customerid HAVING count(*) > 1;

1
2
3
4
5

CREATE TEMPORARY TABLE dups_cust AS
SELECT customerid, min(ctid) AS min_ctid
FROM new_cust
GROUP BY customerid
HAVING count(*) > 1;
然后我们可以删除每个重复内容，而保留重复值中最小ctid值的那条内容：

DELETE FROM new_cust USING dups_cust WHERE new_cust.customerid = dups_cust.customerid AND new_cust.ctid != dups_cust.min_ctid;

1
2
3
4

DELETE FROM new_cust
USING dups_cust
WHERE new_cust.customerid = dups_cust.customerid
AND new_cust.ctid != dups_cust.min_ctid;
我们执行事务，也会释放掉我们此前所添加的锁：

COMMIT;

1

COMMIT;
最后在进行所有删除后清理数据表：

VACUUM new_cust;

1

VACUUM new_cust;

运行原理…

第一条查询通过对唯一字段进行分组并对行计数来实现。大于一行的都是由重复值所产生。如果在查找一个字段以上（甚至是所有字段）的重复值，那么我们应使用以下形式的SQL：

SELECT *
FROM mytable
WHERE (col1, col2, ... ,colN) IN
(SELECT col1, col2, ... ,colN
 FROM mytable
 GROUP BY col1, col2, ... ,colN
 HAVING count(*) > 1);

SELECT *

FROM mytable

WHERE (col1, col2, ... ,colN) IN

(SELECT col1, col2, ... ,colN

FROM mytable

GROUP BY col1, col2, ... ,colN

HAVING count(*) > 1);

这里，col1、col2等直到colN都是键的字段：

注意这种类型的查询可能需要对所有键字段进行完整表格排序。这要求排序空间等于表格的大小，因此在对超大表进行SQL运行时最好先进行考量。对于这各路查询需要有一个比较大的work_mem配置，可能要128 MB或更多。

我们所展示的DELETE FROM … USING查询，仅在PostgreSQL中起作用，因为它使用了ctid值，这是数据表中每行的标识符。如果想要本章前面那样对多个字段运行该查询，需要将第3步中的查询扩展如下：

SELECT customerid, customer_name, ..., min(ctid) AS min_ctid
FROM ...
GROUP BY customerid, customer_name, ...
...;

SELECT customerid, customer_name, ..., min(ctid) AS min_ctid

FROM ...

GROUP BY customerid, customer_name, ...

...;

然后，扩展第4步中的查询如下：

DELETE FROM new_cust
...
WHERE new_cust.customerid = dups_cust.customerid
AND new_cust.customer_name = dups_cust.customer_name
AND ...
AND new_cust.ctid != dups_cust.min_ctid;

DELETE FROM new_cust

...

WHERE new_cust.customerid = dups_cust.customerid

AND new_cust.customer_name = dups_cust.customer_name

AND ...

AND new_cust.ctid != dups_cust.min_ctid;

前面的查询通过对相似值的所有行进行分组，然后查找ctid值最低的行。最低值与更接近于表的起始处，因此会从数据表的后部删除重复项。在运行VACUUM时，可能会发现数据表变小，因为我们从最远端删除了行。

BEGIN和COMMIT命令将LOCK和DELETE命令封装为单个事务，这是所要求的。否则会在获取到锁后会立即释放。

使用单事务的另一个原因是在出错时我们总是要进行回滚，在从线上表中删除数据时这总是有益的。

扩展知识…

在删除重复行时长期进行修改的锁表不大可能。这会给大表带有很大的问题。此时，我们需要稍作调整：

标识出要删除的行，将它们保存到一个副表中。
对请表构建索引在加速对行的访问（可以使用CONCURRENTLY关键字，在第九章：常规维护中的维护索引一节会进行讲解）。
编写循环程序从副表中读取各行，执行一系列更小的事务。
开启新的事务。
从副表中读取一组匹配的行。
从主表中选取这些行来进行更新，依赖于索引来让这些访问快速发生。
删除相应的行。
提交执行然后再次循环。

前述的程序不能写成一个数据库函数，因为在同一个函数中不能有多个事务。我们需要有多个事务来确保对每行的锁仅保持一个非常短的时间。

避免重复行

避免重复行是保证任意数据库质量最重要的一个方面。PostgreSQL在这个领域比大多数关系型数据所多进行了扩展来提供一些有用的功能。

准备工作

选取一组你希望变成唯的字段。这是适用于所有行还是其中的一段？

我们先使用示例表：

postgres=# SELECT * FROM new_cust;
 customerid
------------
 1
 2
 3
 4
(4 rows)

postgres=# SELECT * FROM new_cust;

customerid

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

(4 rows)

如何实现…

要避免重复行，我们需要创建一个唯一索引让数据库服务器用于强制具体字段集合的唯一性。可以对基本数据类型采取如下3种相似的方式：

对一组字段创建主键约束。每张表只允许有一个主键。数据行的值不得为NULL，因为我们要对该字段作NOT NULL的限制。

ALTER TABLE new_cust ADD PRIMARY KEY(customerid);

1

ALTER TABLE new_cust ADD PRIMARY KEY(customerid);
这会新建一个名为new_cust_pkey的索引。
对一组字段创建一个唯一约束。我们可以使用它们代替（或直接使用）主键。每张表中对此的数量没有限制。在这些字段中允许有NULL值：

ALTER TABLE new_cust ADD UNIQUE(customerid);

1

ALTER TABLE new_cust ADD UNIQUE(customerid);
这会新建一个名为new_cust_customerid_key的索引。
对这组字段创建一个唯一索引：

CREATE UNIQUE INDEX ON new_cust (customerid);

1

CREATE UNIQUE INDEX ON new_cust (customerid);
这会新建一个名为new_cust_customerid_idx的索引。

所有这些技巧通过一些稍有不同的语法排除了重复值。它们共同创建一个索引，但只有前两者会创建正式的约束。每种技巧在我们有主键或使用多字段的唯一约束时可以进行使用。

最后一个方法很重要，因为它允许对索引指定一个WHERE语句。这在某种情况下知道字段值是唯一会有益。所产生的索引称为部分索引。

假设我们的数据像这样：

postgres=# SELECT * FROM partial_unique;

1	postgres=# SELECT * FROM partial_unique;

它会产生如下的输出：

customerid | status | close_date
-----------+--------+------------
 1 | OPEN |
 2 | OPEN |
 3 | OPEN |
 3 | CLOSED | 2010-03-22
(4 rows)

customerid | status | close_date

-----------+--------+------------

1 | OPEN |

2 | OPEN |

3 | OPEN |

3 | CLOSED | 2010-03-22

(4 rows)

然后我们可以对数据表添加部分索引来仅对status = ‘OPEN’强制customerid的唯一性，如下：

CREATE UNIQUE INDEX ON partial_unique (customerid)
 WHERE status = 'OPEN';

1 2	CREATE UNIQUE INDEX ON partial_unique (customerid) WHERE status = 'OPEN';

如果你的唯一性约束需要对更多复杂的数据类型来进行施加，那么可能需要使用更为高级的语法。这里有一些有助的示例。

我们先使用一个最简单的示例：创建一个盒子的表格并在其中放置示例数据。它可能是你第一次看到PostgreSQL的数据类型语法：

postgres=# CREATE TABLE boxes (name text, position box);
CREATE TABLE
postgres=# INSERT INTO boxes VALUES
 ('First', box '((0,0), (1,1))');
INSERT 0 1
postgres=# INSERT INTO boxes VALUES
 ('Second', box '((2,0), (2,1))');
INSERT 0 1
postgres=# SELECT * FROM boxes;
 name | position
--------+-------------
 First | (1,1),(0,0)
 Second | (2,1),(2,0)
(2 rows)

postgres=# CREATE TABLE boxes (name text, position box);

CREATE TABLE

postgres=# INSERT INTO boxes VALUES

('First', box '((0,0), (1,1))');

INSERT 0 1

postgres=# INSERT INTO boxes VALUES

('Second', box '((2,0), (2,1))');

INSERT 0 1

postgres=# SELECT * FROM boxes;

name | position

--------+-------------

First | (1,1),(0,0)

Second | (2,1),(2,0)

(2 rows)

我们可以看到根据它们的 x 和 y 坐标既不接触也不重叠的两个盒子。

要在这里强制唯一性，我们希望创建一个对添加与已有盒子位置重叠的约束。对 box 数据类型重叠运算符定义为&&,，因此我们可以使用下面的语法来添加约束：

ALTER TABLE boxes ADD EXCLUDE USING gist (position WITH &&);

1	ALTER TABLE boxes ADD EXCLUDE USING gist (position WITH &&);

这新建一个名为boxes_position_excl的索引：

 #\d boxes_position_excl
 Index "public.boxes_position_excl"
 Column | Type | Key? | Definition
----------+------+------+------------
 position | box | yes | "position"
gist, for table "public.boxes"

#\d boxes_position_excl

Index "public.boxes_position_excl"

Column | Type | Key? | Definition

----------+------+------+------------

position | box | yes | "position"

gist, for table "public.boxes"

我们甚至可以对基本数据类型使用相同的语法。因此第一个示例的每4种执行方式如下：

ALTER TABLE new_cust ADD EXCLUDE (customerid WITH =);

1	ALTER TABLE new_cust ADD EXCLUDE (customerid WITH =);

这会新建一个名为new_cust_customerid_excl的索引并且会排除掉重复项：

# insert into new_cust VALUES (4);
ERROR: conflicting key value violates exclusion constraint
"new_cust_customerid_excl"
DETAIL: Key (customerid)=(4) conflicts with existing key (customerid)=(4).

# insert into new_cust VALUES (4);

ERROR: conflicting key value violates exclusion constraint

"new_cust_customerid_excl"

DETAIL: Key (customerid)=(4) conflicts with existing key (customerid)=(4).

运行原理…

对索引总会强制唯一性。

每个索引会通过一个数据类型运算符进行定义。在插件新行或数据列值的集合被更新时，我们使用运算符来搜索与新数据相冲突的已有值。

因此，要强制唯一性，我们需要对列的数据类型定义索引和搜索运算符。在我们定义常规UNIQUE约束时，只是假定对数据类型使用了等号运算符（=）。EXCLUDE语句提供了更丰富的语法，来让我们通过不同的数据类型和运算符表示相同的问题。

扩展知识…

唯一性和排除性约束标记为可延时，这表示用户可以选择推迟检查到事务的结束处，这是一种不降低数据完整度减轻约束的很好的方式。

重复索引

注意PostgreSQL允许我们有相同的定义拥有多个索引。这在某些情况下会非常有用，但如果误创建了多个索引的话也会带来麻烦，因为每个索引在写方面有其自身的开销。也可以使用前述的每个不同方式来定义约束。各种方式中可能会基本强制相同的约束，因此要当心。

无索引唯一性

可以在不创建索引的情况下对一组列建立唯一性。这在我们只是要保证唯一性而无需进行索引查询时会非常有用。

要实现这个，可以采用如下的一种方式：

使用序列数据类型
手动修改一个序列nextval() 函数的默认值

每种方式都会提供一个唯一值来作为数据行的键。并不会强制唯一性，并且也不会定义唯一性约束。因此是有可能被人重置序列为此前的值，并最终导致重复值。

同时还应考虑这个方法提供唯一性值作为默认值，在用户指定显示值的时候不进行使用。如下例所示：

CREATE TABLE t(id serial, descr text);
INSERT INTO t(descr) VALUES ('First value');
INSERT INTO t(id,descr) VALUES (1,'Cheating!');

CREATE TABLE t(id serial, descr text);

INSERT INTO t(descr) VALUES ('First value');

INSERT INTO t(id,descr) VALUES (1,'Cheating!');

最终，你可能还会需要使用最唯一的值，如使用clock_timestamp()函数来提供对微秒值的升序位数。

真实案例 – IP 地址段分配

问题是有关分配 IP 地址段的，这里同时确保我们不会分配（或有潜在可能分配）相同的地址给不同的人或不同的用途。如果我们记录每个 IP 地址的话这非常简单，而如果只是要处理 IP 地址段的话则会更为困难。

初始可以设计该数据库如下：

CREATE TABLE iprange
    (iprange_start inet
    ,iprange_stop inet
    ,owner text);
INSERT INTO iprange VALUES ('192.168.0.1','192.168.0.16', 'Simon');
INSERT INTO iprange VALUES ('192.168.0.17','192.168.0.24', 'Gianni');
INSERT INTO iprange VALUES ('192.168.0.32','192.168.0.64', 'Gabriele');

CREATE TABLE iprange

(iprange_start inet

,iprange_stop inet

,owner text);

INSERT INTO iprange VALUES ('192.168.0.1','192.168.0.16', 'Simon');

INSERT INTO iprange VALUES ('192.168.0.17','192.168.0.24', 'Gianni');

INSERT INTO iprange VALUES ('192.168.0.32','192.168.0.64', 'Gabriele');

但是，你会意识到根据无法创建唯一性约束来强制模型约束以避免重叠的 IP 段。可以创建一个后置触发器来对已有的值进行检查，但这样会非常混乱。

根据数据段类型，PostgreSQL提供一种更好的解决方案。事实上，每个支持btree运算符类（即一种用于排序任意两个给定值的方式）的数据类型都可用于创建一个范围类型。在本例中，SQL如下：

CREATE TYPE inetrange AS RANGE (SUBTYPE = inet);

1	CREATE TYPE inetrange AS RANGE (SUBTYPE = inet);

这条命令用于创建一个新的表示inet值（即 IP 地址）范围的数据类型。现在我们可以在创建表时使用新类型：

CREATE TABLE iprange2
(iprange inetrange
,owner text);

CREATE TABLE iprange2

(iprange inetrange

,owner text);

这张新表可以像平常一样添加值。我们只需要将每个范围的极值分组为单个值即可，如下：

INSERT INTO iprange2
VALUES ('[192.168.0.1,192.168.0.16]', 'Simon'); 
INSERT INTO iprange2
VALUES ('[192.168.0.17,192.168.0.24]', 'Gianni'); 
INSERT INTO iprange2
VALUES ('[192.168.0.32,192.168.0.64]', 'Gabriele');

INSERT INTO iprange2

VALUES ('[192.168.0.1,192.168.0.16]', 'Simon');

INSERT INTO iprange2

VALUES ('[192.168.0.17,192.168.0.24]', 'Gianni');

INSERT INTO iprange2

VALUES ('[192.168.0.32,192.168.0.64]', 'Gabriele');

此时我们可以使用如下语句对表创建一个唯一排除约束：

ALTER TABLE iprange2
  ADD EXCLUDE USING GIST (iprange WITH &&);

1 2	ALTER TABLE iprange2 ADD EXCLUDE USING GIST (iprange WITH &&);

如果我们尝试插入一个与任意已有范围相重叠的范围时，PostgreSQL会阻止我们：

INSERT INTO iprange2
VALUES ('[192.168.0.10,192.168.0.20]', 'Somebody else');
ERROR: conflicting key value violates exclusion constraint "iprange2_iprange_excl"
DETAIL: Key (iprange)=([192.168.0.10,192.168.0.20]) conflicts with existing key (iprange)=([192.168.0.1,192.168.0.16]).

INSERT INTO iprange2

VALUES ('[192.168.0.10,192.168.0.20]', 'Somebody else');

ERROR: conflicting key value violates exclusion constraint "iprange2_iprange_excl"

DETAIL: Key (iprange)=([192.168.0.10,192.168.0.20]) conflicts with existing key (iprange)=([192.168.0.1,192.168.0.16]).

真实案例 – 时间段

在很多数据库中，会有一个带有START_DATE值及START_DATE值或类似数据的历史数据表。如前例中所示，我们可以通过range类型优雅地解决这一问题。实际上这个示例甚至更简短 – 我们无需创建一个范围类型，因为在大多数情况下已经进行了内置，确切的说，包含整型、数值、日期，带时区和不带时区的时间戮。

真实案例 – 前缀范围

另一个常见的问题涉及信用卡号或电话号码的分配。例如，假定对每个机构分配了给定的范围的话，对于信用卡号我们可以需要对某些金融机构执行额外的检查。在这种情况下，我们必须有效地检查给定的信用卡号属于一个某一范围之内。

前缀范围数据类型就是为解决这一类问题而设计的。可通过http://github.com/dimitri/prefix来获取这一PostgreSQL的插件。

ℹ️一个警告：虽然名称很相近，前缀范围无法通过范围类型来实现。

为一组数据查找唯一键

有时，对描述数据查找唯一键列集会非常困难。

准备工作

我们通过一个小表来开始，这样答案会非常明显：

postgres=# select * from ord;

1	postgres=# select * from ord;

假设输出如下：

orderid | customerid | amt 
--------+------------+--------
  10677 |          2 | 5.50 
   5019 |          3 | 277.44 
   9748 |          3 | 77.17
(3 rows)

orderid | customerid | amt

--------+------------+--------

10677 | 2 | 5.50

5019 | 3 | 277.44

9748 | 3 | 77.17

(3 rows)

如何实现…

首先，无需通过暴力的方式来实现。检查所有列的组合来了解哪个唯一性会花费最长时间。

我们先通过使用PostgreSQL自己的优化器统计。对我们的表运行如下命令来获取一个新统计数据示例：

postgres=# analyze ord;
ANALYZE

1 2	postgres=# analyze ord; ANALYZE

这一运行非常快速，因此我们无需等待很长时间。现在我们可以查看相关列的统计数据：

postgres=# SELECT attname, n_distinct
                           FROM pg_stats
                           WHERE schemaname = 'public'
                           AND tablename = 'ord';
  attname   | n_distinct 
------------+------------
 orderid    | -1 
 customerid | -0.666667 
 amt        | -1
(3 rows)

postgres=# SELECT attname, n_distinct

FROM pg_stats

WHERE schemaname = 'public'

AND tablename = 'ord';

attname | n_distinct

------------+------------

orderid | -1

customerid | -0.666667

amt | -1

(3 rows)

选择前例是因为存在两个潜在的答案。若n_distinct的值为-1，那么该列在所检查行的样本中被视作唯一的。

然后我们需要靠自己的判断来决定其中的一列或两列有可能是唯一的，或者在创建它们的数据库作为设计的一部分。

有可能没有单列来唯一标识行。多列的键也非常普遍。如果没有列是唯一的，那么我们应当开始查找唯一性最高的列的组合来实现唯一键。以下查询显示一张表的频率分布，一个值会在同一种情况下发生两次，而另一值仅发生一次：

postgres=# SELECT num_of_values, count(*)
            FROM (SELECT customerid, count(*) AS num_of_values
                         FROM ord
                         GROUP BY customerid) s
            GROUP BY num_of_values
            ORDER BY count(*);
 num_of_values | count
---------------+-------
             2 |     1
             1 |     1
(2 rows)

postgres=# SELECT num_of_values, count(*)

FROM (SELECT customerid, count(*) AS num_of_values

FROM ord

GROUP BY customerid) s

GROUP BY num_of_values

ORDER BY count(*);

num_of_values | count

---------------+-------

2 | 1

1 | 1

(2 rows)

我们可以改变查询来包含多列，如下：

SELECT num_of_values, count(*)
FROM (SELECT   customerid, orderid, amt
              ,count(*) AS num_of_values
               FROM ord
               GROUP BY customerid, orderid, amt
               ) s
GROUP BY num_of_values
ORDER BY count(*);

SELECT num_of_values, count(*)

FROM (SELECT customerid, orderid, amt

,count(*) AS num_of_values

FROM ord

GROUP BY customerid, orderid, amt

) s

GROUP BY num_of_values

ORDER BY count(*);

这人查询在我们查看到列的组合唯一时结果仅为一行。

在我们接近发现键时，会看到分布变得越来越密。

过程如下：

先选择一列。
计算相应的频率分布。
如果结果为多行，那么添加多列并重复第2步。否则，表示你已经发现了一组满足唯一性约束的列。

现在必须验证该组列为最小化的：例如，检查是否有可能在不违反唯一性约束的情况下删除一列或多列。这可以通过使用频率分布作为测试来实现。确切的说，执行如下操作：

通过对所有其它列计算频率分布来测试每列
如果频率分布有一行，那么在唯一性约束中不需要该列。从列集中删除它并重复第1步。否则，你已经找到了最小化的列集，也称之为该表的键。

运行原理…

可以用程序查找唯一键，但在大多数情况下，人类可以通过看列名、外键或理解防止暴力攻击所需要的减少搜索数量等方面来更快速的查找到。

ANALYZE命令运行的原理是接收表数据的样本，然后对结果执行数据分析。n_distinct值有两个不同的含义，取决于其正负号：如为正，它是该列独立值数量的计算，若为负，它是这种独特值修改了符号的密度的运算。例如， n_distinct = -0.2表示一百万行的表预期有200,000个独立值，而n_distinct = 5表示预期仅有5个独立值。

生成测试数据

DBA们出于各种原因经常需要生成测试数据，可能是为了设置测试数据库，也可能是为SQL性能问题而生成的测试用例。

如何实现…

要创建一张测试数据表，我们需要如下内容：

一些行
一些列
一些排序

步骤如下;

首先生成大量数据行。我们返回集函数。你可以自己编写，但PostgreSQL中已包含了一些非常有用的函数。

可以使用如下查询生成行的序列：

postgres=# SELECT * FROM generate_series(1,5);
 generate_series
-----------------
               1
               2
               3
               4
               5
(5 rows)

postgres=# SELECT * FROM generate_series(1,5);

generate_series

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

(5 rows)

我们也可以像下面这样生成一个日期列表：

postgres=# SELECT date(t)
FROM generate_series(now(),
now() + '1 week', '1 day') AS f(t);
date
------------
2018-04-24
2018-04-25
2018-04-26
2018-04-27
2018-04-28
2018-04-29
2018-04-30
2018-05-01
(8 rows)

postgres=# SELECT date(t)

FROM generate_series(now(),

now() + '1 week', '1 day') AS f(t);

date

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

2018-04-24

2018-04-25

2018-04-26

2018-04-27

2018-04-28

2018-04-29

2018-04-30

2018-05-01

(8 rows)

然后，我们要在测试表中为每列生成一个值。我们可以将其分解为一个函数序列，使用如下示例来作为指导：
- 这些函数均可用于生成行或通过它们推导出主键
- 对于随机的整型值，函数如下：
  
  (random()*(2*10^9))::integer
  
  1
  
  (random()*(2*10^9))::integer
- 对于随机的 bigint 值，函数如下：
  
  (random()*(9*10^18))::bigint
  
  1
  
  (random()*(9*10^18))::bigint
对于随机的numeric数据，函数如下：

(random()*100.)::numeric(5,2)

1

(random()*100.)::numeric(5,2)
- 对于直到最大值的随机长度的字符串，函数如下：
  
  repeat('1',(random()*40)::integer)
  
  1
  
  repeat('1',(random()*40)::integer)
- 对于随机长度的子字符串，函数如下：
  
  substr('abcdefghijklmnopqrstuvwxyz',1, (random()*25)::integer)
  
  1
  2
  
  substr('abcdefghijklmnopqrstuvwxyz',1,
  (random()*25)::integer)
- 以下是通过字符串列表的一组随机字符串函数：
  
  (ARRAY['one','two','three'])[0.5+random()*3]
  
  1
  
  (ARRAY['one','two','three'])[0.5+random()*3]

最后我们将两种技术配合使用来生成我们的表：

postgres=# SELECT key
                        ,(random()*100.)::numeric(4,2)
                        ,repeat('1',(random()*25)::integer)
 FROM generate_series(1,10) AS f(key);
 key | numeric |         repeat        
-----+---------+------------------------
   1 |   83.05 | 1111
   2 |    5.28 | 11111111111111
   3 |   41.85 | 1111111111111111111111
   4 |   41.70 | 11111111111111111
   5 |   53.31 | 1
   6 |   10.09 | 1111111111111111
   7 |   68.08 | 111
   8 |   19.42 | 1111111111111111
   9 |   87.03 | 11111111111111111111
  10 |   70.64 | 111111111111111
(10 rows)

postgres=# SELECT key

,(random()*100.)::numeric(4,2)

,repeat('1',(random()*25)::integer)

FROM generate_series(1,10) AS f(key);

key | numeric | repeat

-----+---------+------------------------

1 | 83.05 | 1111

2 | 5.28 | 11111111111111

3 | 41.85 | 1111111111111111111111

4 | 41.70 | 11111111111111111

5 | 53.31 | 1

6 | 10.09 | 1111111111111111

7 | 68.08 | 111

8 | 19.42 | 1111111111111111

9 | 87.03 | 11111111111111111111

10 | 70.64 | 111111111111111

(10 rows)

我们也可以使用随机排序：

postgres=# SELECT key
                         ,(random()*100.)::numeric(4,2)
                         ,repeat('1',(random()*25)::integer)
                         FROM generate_series(1,10) AS f(key)
                         ORDER BY random() * 1.0;
 key | numeric |         repeat         
-----+---------+-------------------------
   4 |   86.09 | 1111
  10 |   28.30 | 11111111
   2 |   64.09 | 111111
   8 |   91.59 | 111111111111111
   5 |   64.05 | 11111111
   3 |   75.22 | 11111111111111111
   6 |   39.02 | 1111
   7 |   20.43 | 1111111
   1 |   42.91 | 11111111111111111111
   9 |   88.64 | 1111111111111111111111
(10 rows)

postgres=# SELECT key

,(random()*100.)::numeric(4,2)

,repeat('1',(random()*25)::integer)

FROM generate_series(1,10) AS f(key)

ORDER BY random() * 1.0;

key | numeric | repeat

-----+---------+-------------------------

4 | 86.09 | 1111

10 | 28.30 | 11111111

2 | 64.09 | 111111

8 | 91.59 | 111111111111111

5 | 64.05 | 11111111

3 | 75.22 | 11111111111111111

6 | 39.02 | 1111

7 | 20.43 | 1111111

1 | 42.91 | 11111111111111111111

9 | 88.64 | 1111111111111111111111

(10 rows)

运行原理…

要设置返回函数，实际返回的一组数据行。这允许把它们放在FROM语句中像一张表一样使用，或是放在SELECT语句中。generate_series()函数集返回日期或整型，这取决于你使用的输入参数的数据类型。

:: 运算符用于在数据类型之间进行投射。字符串示例列表中所取的随机字符串使用PostgreSQL数组。可以使用ARRAY构造器语法创建一个数组，然后使用一个整数来从数组中引用元素。在本例中，我们使用了随机下标。

扩展知识…

还有一些商业工具可用于为PostgreSQL生成针对应用的测试数据。这工具可通过http://www.sqlmanager.net/products/postgresql/datagenerator 和 http://www.datanamic.com/datagenerator/index.html进行获取。

数据生成器的关键特征如下：

能够对自定义数据类型以正确的格式生成数据
能够添加数据到多张表中，而在数据表间有对应的外键约束
能够添加数据为非均匀分布

这里展示的工具和技巧非常酷炫智能，但其中还是隐藏着一些问题。真实数据中有很多奇怪的内容，非常难以模拟。其中最困难的一点是生成遵循真实分布的数据。例如，如果我们要生成身高数据，那么我会希望生成遵循正态分布的数据。如果生成客户银行余额数据，需要使用ZIP分布，或者是生成报告保险申报的数据，可能会（也可能不会）使用泊松（Poisson）分布。复制数据的真实怪状会花费大量时间。

最后，注意将浮点弄投射到整型中时会将其四舍五入为最接近的整数，因此整数的分布并不是在各端间均匀分布的。例如，(random()*10)::int 为0及为10的概率都仅为5%，而整数1到9发生的概率为10%。这也是我们在上例中代入0.5 的原因，这样比使用floor()函数更为简单。

其它内容

可以使用已有数据来生成样式的测试数据库。这是下一节随机数据采样的主题。

随机数据采样

DBA们可能会被要求设置测试服务器并使用测试数据来填充。通常服务器会是老硬件，带有的磁盘较小。因此，数据采取的话题就浮出水面了。

取样的目的在于降低数据库的大小及提升稍后分析的速度。一些统计师对采样的概念根深蒂固，他们甚至不会质疑其使用是否有效或者会不会导致进一步的复杂情况。

执行采样的标准SQL方式是在SELECT语句中添加TABLESAMPLE从句。

如何实现…

在这一部分中，我们将使用一个给定数据集（如指定数据表）的随机样本。首先，你应当意识到没有简单的工具来对数据来行样本切片。有的话自然很清爽，但是没有。你需要阅读如下内容来了解原因：

我们首先考虑使用SQL来获取样本。随机样本实际上非常简单，因为我们可以使用TABLESAMPLE从句。思考如下示例：

postgres=# SELECT count(*) FROM mybigtable;
 count
-------
 10000
(1 row)
postgres=# SELECT count(*) FROM mybigtable
                          TABLESAMPLE BERNOULLI(1);
 count
-------
   106
(1 row)
postgres=# SELECT count(*) FROM mybigtable 
                          TABLESAMPLE BERNOULLI(1);
 count
-------
   99
(1 row)

postgres=# SELECT count(*) FROM mybigtable;

count

-------

10000

(1 row)

postgres=# SELECT count(*) FROM mybigtable

TABLESAMPLE BERNOULLI(1);

count

-------

106

(1 row)

postgres=# SELECT count(*) FROM mybigtable

TABLESAMPLE BERNOULLI(1);

count

-------

(1 row)

此处TABLESAMPLE从句应用于mybigtable表，告知SELECT仅使用随机样本，而BERNOULLI关键字表示使用的样本方法，括号间的1表示我们在样本中要使用的百分比，即1%。非常简单！
现在我们需要从数据库中获取样本数据，出于几个原因这有点麻烦。首先，没有为pg_dump指定WHERE从句的选项。其次，如果要创建一个包含WHERE从句的视图，pg_dump仅导出视图的定义，而不包含视图本身。

可以使用pg_dump导出随数据表集外的所有数据库，因此像下面这样生成导出样本：

pg_dump –-exclude-table=mybigtable > db.dmp
pg_dump –-table=mybigtable –-schema-only > mybigtable.schema
psql -c '\copy (SELECT * FROM mybigtable 
                    TABLESAMPLE BERNOULLI (1)) to mybigtable.dat'

pg_dump –-exclude-table=mybigtable > db.dmp

pg_dump –-table=mybigtable –-schema-only > mybigtable.schema

psql -c '\copy (SELECT * FROM mybigtable

TABLESAMPLE BERNOULLI (1)) to mybigtable.dat'

然后使用如下命令重新载入到一个单独的数据库中：

psql -f db.dmp psql -f mybigtable.schema psql -c '\copy mybigtable from mybigtable.dat'

1
2
3

psql -f db.dmp
psql -f mybigtable.schema
psql -c '\copy mybigtable from mybigtable.dat'

总的来说，我们建议是谨慎使用样本。一般考虑围绕样本设计的数学问题及提取数据的复杂度而言，仅将其应用到少数几个大表会非常容易。

运行原理…

提取机制显示PostgreSQL命令行工具psql 和 pg_dump的能力，因为pg_dump允许我们包含或排除对象并导出整张表（或仅导出其schema），而psql允许我们将任意查询的结果导出到文件中。

BERNOULLI从句指定样本方法，即PostgreSQL通过执行全表扫描来接收随机样本，然后选取带有所要求概率的每行，本处概率为1%。

另一个内置样本方法为SYSTEM，它读取表格页的样本数据，然后在这些页面中包含所有行；中要样本比原始数据少通常这会更快，但选项的随机性受到磁盘上行的物理排列的影响，这让其仅适用于某些应用。

这里有一个显示出问题的示例。假定接收一个字典，从一些页面是分割出来，然后从其中选择所有单词，会获取到一个由一些连续词集组成的随机样本。这在想要估算平均词长度时已足够，但不足以对每个首字母分析平均单词数量。原因是单词的首字母与单词在页面中如何排列有强关联，而单词长度则不然。

我们没有讨论TABLESAMPLE从句到底有多随机。这是不针对这些细节的好例子；但是在使用其它函数或样本方法扩展PostgreSQL时则极为简单，因此如果你更喜欢另一咱机制，可以找到一个外部随机数生成器，并创建针对TABLESAMPLE从句的新取样方法。PostgreSQL包含两个额外的取样方法，tsm_system_rows 和 tsm_system_time,作为contrib扩展：它们极好地开始学习的示例。

tsm_system_rows方法不接收百分比，而是将数值参数解释为返回的行数。类似地，tsm_system_time方法将其参数看作获取随机样本所花费的毫秒数。

这两个方法名称中包含单词system，因为它们像内置system采样方法一样使用块级样本；因此其随机性受前述同样的集群（clustering）限制。

前面所示的采样方法是一种简单带有等概率选择（EPS）设计的采样技术。

EPS样本被视为有用，因为其对样本分配的变化幅度类似于原始数据集。但是，请记住这仅在考虑变化幅度是有用。

简单随机样本可以让最终样本偏向于更常发生的数据。例如，如果你有1%的样本数据，其中某些数据仅发生0.001%，可能会获取得一些不包含边界数据的数据集。

你可能需要对数据进行预分组并从每组中获取不同的样本来确保拥有一个带有更多边界属性的采样数据集。一个简单的方法可能会像下面这样：

包含所有普通数据的1%
包含边界数据的25%

注意如果你这么做，就不再是一个EPS样本设计了。

无疑很多统计人员读到这里后会非常生气。欢迎使用SQL语言工作来创建更精确的样本。只要确保你知道在做的事及/或查看一个好统计文献、网站或教科书。

从电子表格加载数据

电子表格用最显而易见的用于存储数据的工具。一商务范围内的研究持续表明50%的小型数据存储使用电子表格或更小的桌面数据库。从这里来源加载数据对很多DBA是频繁而重要的任务。

准备工作

电子表格将数据、PPT 和程序合并到一个文件中。对于驱动想要快速工作的人们非常完善。像其它关系性数据库一样，PostgreSQL主要关心底层的数据，因此从这些电子表格中提取数据会构成一些挑战。

如果电子表格按照特定格式布局我们可以轻松处理它的数据，如下：

电子表格中的每列成为数据表中的各列
电子表格中的每行成为数据表中的各行
数据只存在于电子表格的一个表单中
第一行可以为列的描述或标题

这是一种非常简单的布局，更常见的是在电子表格中存在其它内容，如标题、注释、公式中使用的常量、总结、宏和图片。如果你处于这种情况，最好的是按照前述以原始的形式在电子表格中新建一个表单，然后设置跨表引用来获取数据。一个跨表引用的示例是=Sheet2.A1。你需要为每个数据集分隔出一个表单，来放到PostgreSQL的单个数据表中。但是可以将多个表单加载到一个数据表中。

一些电子表格的使用者会说这些都没有必要，很明显是数据库的问题。真正的电子表格大量确实提倡这类布局 – 数据放在一个表单中，计算和展示放在其它表单中。因此，这实际上是一种设计电子表格的最佳实践，但我们也必须要适应现状。

如何实现…

这里，我们将展示一个将电子表格中数据加载到数据库中的示例。

如果你的数据在单个表单中有清晰的布局，如下图所示，那么就可以进入File | Save As然后从保存的文件类型中选择CSV：

这会将当前表单导出为一个文件，如下：

"Key","Value" 1,"c" 2,"d"

1
2
3

"Key","Value"
1,"c"
2,"d"
然后我们可以使用如下的psql命令将其载入到已有的PostgreSQL数据表中：

postgres=# \COPY sample FROM sample.csv CSV HEADER postgres=# SELECT * FROM sample; key | value -----+------- 1 | c 2 | d

1
2
3
4
5
6

postgres=# \COPY sample FROM sample.csv CSV HEADER
postgres=# SELECT * FROM sample;
key | value
-----+-------
1 | c
2 | d
或者通过像如下这样命令行：

psql -c '\COPY sample FROM sample.csv CSV HEADER'

1

psql -c '\COPY sample FROM sample.csv CSV HEADER'
注意如果数据在不同目录下的话该文件可包含完整文件路径。psql \COPY命令从客户端系统传输数据，在客户端中可以运行命令通往数据库服务端，这样文件在客户端。
如果通过另一种连接类型提交SQL，那么应当使用如下的SQL语句：

COPY sample FROM '/mydatafiledirectory/sample.csv' CSV HEADER;

1

COPY sample FROM '/mydatafiledirectory/sample.csv' CSV HEADER;

注意前面的 SQL语句运行于数据库服务器上，且仅能以超级用户执行。因此，需要确保允许该服务端进程读取该文件，然后自己将数据传输到服务端，最后加载该文件。在前面SQL中所使用的COPY语句使用了一个绝对路径来定位数据库文件，这是必要的。

COPY (或 \COPY)命令不会为你创建表格；这必须要事前创建好。还应注意HEADER选项什么都不做，只是忽略掉输入文件的第一行，因此.csv文件中的列名无需与Postgres中的数据表相匹配。如果你还遇到，那就不是问题。如果使用了HEADER而文件又没有表头这行，那么它会忽略掉第一行数据。很不幸PostgreSQL无法知道第一行是表头还是数据。务必要小心！

并没有什么标准工具用来从电子表格加载数据到数据库中。编写数据表宏来自动化前述任务就非常简单了，但这不在本书的讨论范畴内。

运行原理…

\COPY 命令执行COPY SQL语句，因此前面所述的两个方法极为相似。有关COPY还有更多可讨论的知识。我们将放在下一部分中进行。

扩展知识…

存在着很多数据提取和加载工具，有些便宜有些昂贵。记住从电子表格中导入数据最难的部分是将数据从其所包含的其它内容中分离出来。我还没碰到过能够实现这个功能的工具。

从普通文件加载数据

将数据加载到数据库中是最重要的任务之一。需要快而准地完成它。以下是方法。

准备工作

需要有一份ofpgloader，有所有的主要软件发行版中通常都会自带。

在写本书时，当前稳定版为3.4.1。3.x系列进行了大幅的重写，添加了很多功能，2.x系统目前已被视为淘汰版本。

如何实现…

PostgreSQL包含一个COPY命令，它提供基本的数据导入/导出机制。COPY命令在加载数据时做的并不充足，所以我们跳过基本命令，进行进入pgloader。

要加载数据，我们需要理解自己的需求，所以我们像如下这样逐步分解：

标识数据文件及其位置。确保pgloader在文件所在处进行了安装。
定位出所要载入的数据表，确保你有加载的权限，并查看可用空间。确定文本类型（如固定大小字段、分隔文本及CSV）并查看编码。
指定文件中列与所要载入数据表字段之间的映射。确保知道文件中哪些列是不需要的 – pgloader允许我们仅包含所需要的列。定位中对于表中哪些字段我们并没有数据。是否需要在表中为它们设置默认值，或者让pgloader通过函数或常量为这些字段生成值。
指定所需进行的任意转换。最常见的问题是日期格式，但还可能存在其它问题。
编写pgloader脚本。
pgloader脚本将创建一个日志文件来记录加载是成功还是失败，或者由其它文件存储被拒绝的行。如果会占用很大空间则需一个拥有足够磁盘空间的目录。它们的大小需要与失败行的数量比例相当。
最后，思考对于性能方面需要什么样的设置。这必然是最后的步骤，因为在早期这类问题会在你还在奔波于如何让导入正常时产生困扰。
必须使用脚本来执行pgloader。这实际上不是一种限制，更多的是一种最佳实践，因为它会迭代至可运作的方向进发。第一次导入总会出问题，那只会在电影里发生。

我们来通过pgloader的快速启动文档，csv.load文件来看一个典型的示例。

在命令中定义所需要的操作并将其保存到一个名为csv.load:的文件中：

LOAD CSV
     FROM '/tmp/file.csv' (x, y, a, b, c, d)
     INTO postgresql://postgres@localhost:5432/postgres?csv (a, b, d, c)

     WITH truncate,
          skip header = 1,
          fields optionally enclosed by '"',
          fields escaped by double-quote,
          fields terminated by ','

      SET client_encoding to 'latin1',
          work_mem to '12MB',
          standard_conforming_strings to 'on'

   BEFORE LOAD DO
    $$ drop table if exists csv; $$,
    $$ create table csv (
        a bigint,
        b bigint,
        c char(2),
        d text
       );
  $$;

LOAD CSV

FROM '/tmp/file.csv' (x, y, a, b, c, d)

INTO postgresql://postgres@localhost:5432/postgres?csv (a, b, d, c)

WITH truncate,

skip header = 1,

fields optionally enclosed by '"',

fields escaped by double-quote,

fields terminated by ','

SET client_encoding to 'latin1',

work_mem to '12MB',

standard_conforming_strings to 'on'

BEFORE LOAD DO

$$ drop table if exists csv; $$,

$$ create table csv (

a bigint,

b bigint,

c char(2),

d text

);

$$;

这个命令让我们可以加载如下CSV文件内容。将其保存至一个文件中，如在/tmp目录下的file.csv中：

Header, with a © sign
"2.6.190.56","2.6.190.63","33996344","33996351","GB","United Kingdom"
"3.0.0.0","4.17.135.31","50331648","68257567","US","United States"
"4.17.135.32","4.17.135.63","68257568","68257599","CA","Canada"
"4.17.135.64","4.17.142.255","68257600","68259583","US","United States"
"4.17.143.0","4.17.143.15","68259584","68259599","CA","Canada"
"4.17.143.16","4.18.32.71","68259600","68296775","US","United States"

"2.6.190.56","2.6.190.63","33996344","33996351","GB","United Kingdom"

"3.0.0.0","4.17.135.31","50331648","68257567","US","United States"

"4.17.135.32","4.17.135.63","68257568","68257599","CA","Canada"

"4.17.135.64","4.17.142.255","68257600","68259583","US","United States"

"4.17.143.0","4.17.143.15","68259584","68259599","CA","Canada"

"4.17.143.16","4.18.32.71","68259600","68296775","US","United States"

我们可以使用如下load脚本：

pgloader csv.load

1	pgloader csv.load

运行原理…

pgloader会优雅地处理错误。COPY 命令在单个事务中加载所有行，因此只要一条错误就足以终止导入。pgloader将一个输入文件分解为适当大小的块，并逐块进行导入。如果某块中的某些行导致错误，pgloader会进一步地进行分解，直接导入所有正常行并跳过有问题的行，然后将有问题的行放到一个单独的拒绝文件中供稍后查看。这一行为对于大量数据文件而其中又包含少量问题行时非常的方便；例如，你可以编辑拒绝文件、修复它们并最终通过再次运行pgloader来导入它们。

pgloader的2.x版本使用Python编写，并通过标准的Python客户端接口进行连接。3.x版本使用Common Lisp编写。是的，pgloader比使用COPY命令导入的效率要低，但运行COPY命令有更多的限制：文件必须要在服务器上相应的位置，必须是相应的格式，并且必须不能在导入时抛出错误。pgloader有其它开销，但它也具备使用同步多线程导入的能力，因此也可以在使用上更快速。pgloader的通过用户定义函数重构数据的功能通常是基本功能，而直接使用COPY则太过简单。

pgloader 还允许通过固定宽度文件中进行导入，而 COPY 则不然。

扩展知识…

如果需要从头重新导入数据表，可以在pgloader脚本中指定-WITH TRUNCATE从句。

还有一些选项用于指定在导入数据之前及之后执行的SQL。例如，可能在导入前有一个创建空表的脚本，或在导入后甚至是导入前后添加约束。

在导入时如果出现错误，那么可能会将一些垃圾数据导入到PostgreSQL数据表中。这并不是你肉眼可以看到的垃圾或者抛出任何语法错误，可将其看成是碎片。应当考虑是否需要在数据加载之后添加VACUUM命令，但这可能会让导入花费的时间更久。

我们需要当心重复导入数据。实现这点的唯一简易方式是确保至少对所导入的每张表定义一个唯一索引。那样导入就会很快失败。

字符串处理经常会很困难，因为有格式化或非打印字符的存在。对PostgreSQL的默认设置是将名为standard_conforming_strings的参数设置为off，这表示反斜线将会被视为转义字符。换句话说，默认\n字符串表示换行，这可能会导致数据的截断。那时需要将standard_conforming_strings设置为on，或者需要在导入参数文件中指定转义字符。

如果需要导入PostgreSQL中导出的数据，则需要使用pg_restore工具。pg_restore工具有一个并行重新导入数据的参数-j number_of_threads，但这仅在导出是通过自定义pg_dump格式进行时才会有效。参见第十一章：备份和还原中的小节获取更多详情。这对于重新导入所导出文件非常有用，但缺少我们所讨论过的pgloader的大部分功能。

如果需要使用没有错误的只读文本文件中的行，那么可以考虑使用file_fdwcontrib模块。简单的说它会让你创建一个可以在每次扫描该文本文件时进行解析的虚拟表。这与一次通过COPY 或 pgloader填入数据表有所不同；因此，它适用不同的用例。例如，考虑由第三方维护的外部数据并需要在不同数据库中进行共享的情况。

使用服务端事务存储过程进行数据批量修改

在有些情况下，需要批量修改数据。在大部分情况下需要根据复杂的各种规则扫描数据并进行修改。这时有如下选择：

编写可以执行所有任务的单条SQL语句
开启一个游标并读取各行，然后通过客户端程序进行修改
编写程序使用游标来读取各行并使用服务端 SQL 来进行修改

有时编写一条可以执行所有任务的SQL语句是可行的，但如果需要进行UPDATE以外的操作时会很快变得困难。主要的困难在于SQL并不可重启，因此需要进行中断，而那样会丢失所有任务。

将所有行读回到客户端程序可能会很慢 – 如果需要编写这类程序，最好是在数据库服务层面进行。

如何实现…

我们将会在PL/pgSQ编写一个存储过程（Procedure）。存储过程类似于函数，只是它不返回值或对象。我们使用存储过程的原因是让你可以运行多个服务端事务。通过以这种方式使用存储过程，我们可以将问题分解为更小的事务，这样对数据库增长及长期运行的事务会带来更少的问题。

作为示例，思考这样一种情况，我们需要使用A2任务等级对所有雇员进行更新，给每个人2%的涨薪：

CREATE PROCEDURE annual_pay_rise ()
LANGUAGE plpgsql AS $$
DECLARE
c CURSOR FOR
SELECT * FROM employee
    WHERE job_code = 'A2';
BEGIN
FOR r IN c LOOP
UPDATE employee
SET salary = salary * 1.02
WHERE empid = r.empid;
 IF mod (r.empid, 100) = 0 THEN
COMMIT;
END IF;
END LOOP;
END;
$$;

CREATE PROCEDURE annual_pay_rise ()

LANGUAGE plpgsql AS $$

DECLARE

c CURSOR FOR

SELECT * FROM employee

WHERE job_code = 'A2';

BEGIN

FOR r IN c LOOP

UPDATE employee

SET salary = salary * 1.02

WHERE empid = r.empid;

IF mod (r.empid, 100) = 0 THEN

COMMIT;

END IF;

END LOOP;

END;

$$;

执行以上存储过程如下：

CALL annual_pay_rise();

1	CALL annual_pay_rise();

我们希望来时间推进中进行常规执行。以上存储进程已进行了编码，会对大约每100行进行一次提交。这个数字并没有什么魔力：我们只是要将其分解成更小的部分来让对行进行扫描或更新。

扩展知识…

可以在存储过程中使用COMMIT 和 ROLLBACK。每个新事务会从前面的事务及其它发生的并发提交中看到改变。

如果存储过程被中断会发生什么？因此我们在使用多事务来完成任务，整个任务不具备原子发到。如果执行中断的话，我们将需要重新运行没有成功执行的部分。如果不小心运行了已经执行的部分会怎样呢？这会对某些人但不是所有人进行双倍加薪。

要处理这一问题，我们可以开发一个简单的任务重启机制。它会使用持久化表来对所做的修改进行追踪，可通过简易 API 进行访问：

CREATE TABLE job_status
(id bigserial not null primary key,status text not null,restartdata bigint);
CREATE OR REPLACE FUNCTION job_start_new ()
 RETURNS bigint
 LANGUAGE plpgsql
 AS $$
 DECLARE
  p_id BIGINT;
BEGIN
  INSERT INTO job_status (status, restartdata)
     VALUES ('START', 0)
   RETURNING id INTO p_id;
  RETURN p_id;  
 END; $$;

CREATE OR REPLACE FUNCTION job_get_status (jobid bigint)
RETURNS bigint
LANGUAGE plpgsql
AS $$
DECLARE
 rdata BIGINT;
BEGIN
  SELECT restartdata INTO rdata
    FROM job_status
    WHERE status != 'COMPLETE' AND id = jobid;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'job id does not exist';
  END IF;
  RETURN rdata;
END; $$;
CREATE OR REPLACE PROCEDURE
job_update (jobid bigint, rdata bigint)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE job_status
    SET status = 'IN PROGRESS'
       ,restartdata = rdata
    WHERE id = jobid;
END; $$;
CREATE OR REPLACE PROCEDURE job_complete (jobid bigint)
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE job_status SET status = 'COMPLETE'
    WHERE id = jobid;
END; $$;

CREATE TABLE job_status

(id bigserial not null primary key,status text not null,restartdata bigint);

CREATE OR REPLACE FUNCTION job_start_new ()

RETURNS bigint

LANGUAGE plpgsql

AS $$

DECLARE

p_id BIGINT;

BEGIN

INSERT INTO job_status (status, restartdata)

VALUES ('START', 0)

RETURNING id INTO p_id;

RETURN p_id;

END; $$;

CREATE OR REPLACE FUNCTION job_get_status (jobid bigint)

RETURNS bigint

LANGUAGE plpgsql

AS $$

DECLARE

rdata BIGINT;

BEGIN

SELECT restartdata INTO rdata

FROM job_status

WHERE status != 'COMPLETE' AND id = jobid;

IF NOT FOUND THEN

RAISE EXCEPTION 'job id does not exist';

END IF;

RETURN rdata;

END; $$;

CREATE OR REPLACE PROCEDURE

job_update (jobid bigint, rdata bigint)

LANGUAGE plpgsql

AS $$

BEGIN

UPDATE job_status

SET status = 'IN PROGRESS'

,restartdata = rdata

WHERE id = jobid;

END; $$;

CREATE OR REPLACE PROCEDURE job_complete (jobid bigint)

LANGUAGE plpgsql

AS $$

BEGIN

UPDATE job_status SET status = 'COMPLETE'

WHERE id = jobid;

END; $$;

首先我们启动一个新任务：

SELECT job_start_new();

1	SELECT job_start_new();

然后执行我们的存储过程，向其传递任务编号。假设这会返回8474：

CALL annual_pay_rise(8474);

1	CALL annual_pay_rise(8474);

如果存储过程中断，我们将从合适的地方重新启动，而无需指定任何修改：

CALL annual_pay_rise(8474);

1	CALL annual_pay_rise(8474);

需要修改已有的存储过程来使用新的重启 API，如以下代码快所示。还应注意需要修改游标来使用ORDER BY从句以让存储合理地可重复：

CREATE OR REPLACE PROCEDURE annual_pay_rise (job bigint)
LANGUAGE plpgsql AS $$
DECLARE
        job_empid bigint;
        c NO SCROLL CURSOR FOR
                SELECT * FROM employee
                WHERE job_code='A2'
                AND empid > job_empid
                ORDER BY empid;
BEGIN
        SELECT job_get_status(job) INTO job_empid;
        FOR r IN c LOOP
                UPDATE employee
                SET salary = salary * 1.02
                WHERE empid = r.empid;
                IF mod (r.empid, 100) = 0 THEN
                        CALL job_update(job, r.empid);
                        COMMIT;
                END IF;
        END LOOP;
        CALL job_complete(job);
END; $$;

CREATE OR REPLACE PROCEDURE annual_pay_rise (job bigint)

LANGUAGE plpgsql AS $$

DECLARE

job_empid bigint;

c NO SCROLL CURSOR FOR

SELECT * FROM employee

WHERE job_code='A2'

AND empid > job_empid

ORDER BY empid;

BEGIN

SELECT job_get_status(job) INTO job_empid;

FOR r IN c LOOP

UPDATE employee

SET salary = salary * 1.02

WHERE empid = r.empid;

IF mod (r.empid, 100) = 0 THEN

CALL job_update(job, r.empid);

COMMIT;

END IF;

END LOOP;

CALL job_complete(job);

END; $$;

对于其它练习，使用pgAdmin或OmniDB中的调试器跟踪执行。

CALL语句也可以用于调用返回空的函数，但除此之外，函数和存储过程是单独的概念。存储过程还允许我们在 PL/Python 和 PL/perl中执行事务。