PostgreSQL最常见问题-操作问题

　　4.1)如何只选择一个查询结果的头几行？或是随机的一行？如果你只是要提取几行数据，并且你在执行查询中知道确切的行数，你可以使用limit功能。 如果有一个索引与 order by中的条件匹配，postgresql 可能就只处理要求的头几条记录， （否则将对整个查询进行处理直到生成需要的行）。如果在执行查询功能时不知道确切的记录数， 可使用游标(cursor)和fetch功能。

　　可使用以下方法提取一行随机记录的：

select　cols
from tab
order by random()
limit 1 ;4.2)如何查看表、索引、数据库以及用户的定义？如何查看psql里用到的查询指令并显示它们？

　　在psql中使用 dt 命令来显示数据表的定义，要了解psql中的完整命令列表可使用? ，另外，你也可以阅读 psql 的源代码 文件pgsql/src/bin/psql/describe.c，它包括为生成psql反斜杠命令的输出的所有 sql 命令。你还可以带 -e 选项启动 psql， 这样它将打印出执行你在psql中所给出的命令的内部实际使用的sql查询。postgresql也提供了一个兼容sql的information schema接口， 你可以从这里获取关于数据库的信息。

　　在系统中有一些以pg_ 打头的系统表也描述了表的定义。

　　使用 psql -l 指令可以列出所有的数据库。

　　也可以浏览一下 pgsql/src/tutorial/syscat.source文件，它列举了很多可从数据库系统表中获取信息的select语法。

　　4.3)如何更改一个字段的数据类型？

　　在8.0版本里更改一个字段的数据类型很容易，可使用 alter table alter column type 。

　　在以前的版本中，可以这样做：

begin;
alter table tab add column new_col new_data_type;
update tab set new_col = cast(old_col as new_data_type);
alter table tab drop column old_col;
commit;

　　你然后可以使用vacuum full tab 指令来使系统收回无效数据所占用的空间。

　　4.4)一行记录，一个表，一个库的最大尺寸是多少？

　　下面是一些限制：

　　当然，实际上没有真正的无限制，还是要受可用磁盘空间、可用内存/交换区的制约。 事实上，当这些数值变得异常地大时，系统性能也会受很大影响。

　　表的最大尺寸 32 tb 不需要操作系统对大文件的支持。大表用多个 1 gb 的文件存储，因此文件系统尺寸的限制是不重要的。

　　如果缺省的块大小增长到 32k ，最大的表尺寸和最大列数还可以增加到四倍。

　　4.5)存储一个典型的文本文件里的数据需要多少磁盘空间？

　　一个 postgres 数据库（存储一个文本文件）所占用的空间最多可能需要相当于这个文本文件自身大小5倍的磁盘空间。

　　例如，假设有一个 100,000 行的文件，每行有一个整数和一个文本描述。 假设文本串的平均长度为20字节。文本文件占用 2.8 mb。存放这些数据的 postgresql 数据库文件大约是 6.4 mb:

　　 32 字节: 每行的头（估计值）
24 字节: 一个整数型字段和一个文本型字段
+　4 字节: 页面内指向元组的指针
----------------------------------------
60 字节每行
postgresql 数据页的大小是 8192 字节 (8 kb)，则：
8192 字节每页
-------------------　 =　136 行/数据页（向下取整）
60 字节每行
100000 数据行
--------------------　=　735 数据页（向上取整）
128 行每页
735 数据页 * 8192 字节/页　=　6,021,120 字节（6 mb）

　　索引不需要这么多的额外消耗，但也确实包括被索引的数据，因此它们也可能很大。

　　空值null存放在位图中，因此占用很少的空间。

　　4.6)为什么我的查询很慢？为什么这些查询没有利用索引？

　　并非每个查询都会自动使用索引。只有在表的大小超过一个最小值，并且查询只会选中表中较小比例的记录时才会采用索引。 这是因为索引扫描引起的随即磁盘存取可能比直接地读取表（顺序扫描）更慢。

　　为了判断是否使用索引，postgresql必须获得有关表的统计值。这些统计值可以使用 vacuum analyze，或 analyze 获得。 使用统计值，优化器知道表中有多少行，就能够更好地判断是否利用索引。统计值对确定优化的连接顺序和连接方法也很有用。在表的内容发生变化时，应定期进行统计值的更新收集。

　　索引通常不用于 order by 或执行连接。对一个大表的一次顺序扫描，再做一个显式的排序通常比索引扫描要快。

　　但是，在 limit 和 order by 结合使用时经常会使用索引，因为这只会返回表的一小部分。 实际上，虽然 max() 和 min() 并不使用索引，通过对 order by 和 llimit 使用索引取得最大值和最小值也是可以的：

select col
from tab
order by col [ desc ]
limit 1;

　　如果你确信postgresql的优化器使用顺序扫描是不正确的，你可以使用set enable_seqscan to 'off'指令， 然后再次运行查询，你就可以看出使用一个索引扫描是否确实要快一些。

　　当使用通配符操作，例如 like 或 ~ 时，索引只能在特定的情况下使用：

　　字符串的开始部分必须是普通字符串，也就是说：

　　like 模式不能以 % 打头。

　　~ （正则表达式）模式必须以 ^ 打头。

　　字符串不能以匹配多个字符的模式类打头，例如 [a-e]。

　　大小写无关的查找，如 ilike 和 ~* 等不使用索引，但可以用 4.8 节描述的函数索引。

　　在做 initdb 时必须采用缺省的本地设置 c locale，因为系统不可能知道在非c locale情况时下一个最大字符是什么。 在这种情况下，你可以创建一个特殊的text_pattern_ops索引来用于like的索引。

　　在8.0之前的版本中，除非要查询的数据类型和索引的数据类型相匹配，否则索引经常是未被用到，特别是对int2,int8和数值型的索引。

　　4.7)我如何才能看到查询优化器是怎样评估处理我的查询？

　　参考 explain 手册页。

　　4.8)我怎样做正则表达式搜索和大小写无关的正则表达式查找？怎样利用索引进行大小写无关查找？

　　操作符 ~ 处理正则表达式匹配，而 ~* 处理大小写无关的正则表达式匹配。大写些无关的 like 变种成为 ilike。

　　大小写无关的等式比较通常写做：

　　select *
from tab
where lower(col) = 'abc';

　　这样将不会使用标准的索引。但是可以创建一个可被利用的函数索引:

　　create index tabindex on tab (lower(col));4.9)在一个查询里，我怎样检测一个字段是否为 null ？我如何才能准确排序而不论某字段是否含 null 值？

　　用 is null 和 is not null 测试这个字段，具体方法如下：

　 select *
from tab
where col is null;

　　为了能对含 null字段排序，可在 order by 条件中使用 is null和 is not null 修饰符，条件为真 true 将比条件为假false 排在前面，下面的例子就会将含 null 的记录排在结果的上面部分：

　 select *
from tab
order by (col is not null)4.10)各种字符类型之间有什么不同？

　　在系统表和在一些错误信息里你将看到内部名称。

　　上面所列的前四种类型是"varlena"（变长）类型（也就是说，开头的四个字节是长度，后面才是数据）。 于是实际占用的空间比声明的大小要多一些。 然而这些类型都可以被压缩存储，也可以用 toast 脱机存储，因此磁盘空间也可能比预想的要少。

　　varchar(n) 在存储限制了最大长度的变长字符串是最好的。 text 适用于存储最大可达 1g左右但未定义限制长度的字符串。

　　char(n) 最适合于存储长度相同的字符串。 char(n)会根据所给定的字段长度以空格补足（不足的字段内容）， 而 varchar(n) 只存储所给定的数据内容。 bytea 用于存储二进制数据，尤其是包含 null 字节的值。这些类型具有相似的性能特性。

　　4.11.1)我怎样创建一个序列号/自动递增的字段？

　　postgresql 支持 serial 数据类型。它在字段上自动创建一个序列和索引。例如：

create table person (
id　 serial,
name text
);

　　会自动转换为：

create sequence person_id_seq;
create table person (
id　 int4 not null default nextval('person_id_seq'),
name text
);

　　参考 create_sequence 手册页获取关于序列的更多信息。

　　4.11.2)我如何获得一个插入的序列号的值？

　　一种方法是在插入之前先用函数 nextval() 从序列对象里检索出下一个 serial 值，然后再显式插入。使用 4.11.1 里的例表，可用伪码这样描述：

new_id = execute("select nextval('person_id_seq')");
execute("insert into person (id, name) values (new_id, 'blaise pascal')");

　　这样还能在其他查询中使用存放在 new_id 里的新值（例如，作为 person 表的外键）。 注意自动创建的 sequence 对象的名称将会是 <table>_<serialcolumn>_seq， 这里 table 和 serialcolumn 分别是你的表的名称和你的 serial 字段的名称。

　　类似的，在 serial 对象缺省插入后你可以用函数 currval() 检索刚赋值的 serial 值，例如：

execute("insert into person (name) values ('blaise pascal')");
new_id = execute("select currval('person_id_seq')");4.11.3)使用 currval() 会导致和其他用户的冲突情况（race condition）吗？

　　不会。currval() 返回的是你本次会话进程所赋的值而不是所有用户的当前值。

　　4.11.4)为什么不在事务异常中止后重用序列号呢？为什么在序列号字段的取值中存在间断呢？

　　为了提高并发性，序列号在需要的时候赋予正在运行的事务，并且在事务结束之前不进行锁定， 这就会导致异常中止的事务后，序列号会出现间隔。

　　4.12)什么是 oid ？什么是 ctid ？

　　postgresql 里创建的每一行记录都会获得一个唯一的oid，除非在创建表时使用without oids选项。 oid创建时会自动生成一个4字节的整数，所有 oid 在整个 postgresql 中均是唯一的。 然而，它在超过40亿时将溢出， oid此后会出现重复。postgresql 在它的内部系统表里使用 oid 在表之间建立联系。

　　在用户的数据表中，最好是使用serial来代替oid 因为serial只是保证在单个表中数据是唯一的，这样它溢出的可能性就非常小了， serial8可用来保存8字节的序列号字段。

　　ctid 用于标识带着数据块（地址）和（块内）偏移的特定的物理行。 ctid 在记录被更改或重载后发生改变。索引入口使用它们指向物理行。

　　4.13)为什么我收到错误信息“error: memory exhausted in allocsetalloc()”？

　　这很可能是系统的虚拟内存用光了，或者内核对某些资源有较低的限制值。在启动 postmaster 之前试试下面的命令：

ulimit -d 262144
limit datasize 256m

　　取决于你用的 shell，上面命令只有一条能成功，但是它将把你的进程数据段限制设得比较高，因而也许能让查询完成。这条命令应用于当前进程，以及所有在这条命令运行后创建的子进程。如果你是在运行sql客户端时因为后台返回了太多的数据而出现问题，请在运行客户端之前执行上述命令。

　　4.14)我如何才能知道所运行的 postgresql 的版本？

　　从 psql 里，输入 select version();指令。

　　4.15)我如何创建一个缺省值是当前时间的字段？

　　使用 current_timestamp：

create table test (x int, modtime timestamp default current_timestamp );4.16)我怎样进行 outer join （外连接）？

　　postgresql 采用标准的 sql 语法支持外连接。这里是两个例子：

select *
from t1 left outer join t2 on (t1.col = t2.col);

　　或是

select *
from t1 left outer join t2 using (col);

　　这两个等价的查询在 t1.col 和 t2.col 上做连接，并且返回 t1 中所有未连接的行（那些在 t2 中没有匹配的行）。右[外]连接(right outer join)将返回 t2 中未连接的行。 完全外连接（full outer join）将返回 t1 和 t2 中未连接的行。 关键字 outer 在左[外]连接、右[外]连接和完全[外]连接中是可选的，普通连接被称为内连接（inner join）。

　　4.17)如何使用涉及多个数据库的查询？

　　没有办法查询当前数据库之外的数据库。 因为 postgresql 要加载与数据库相关的系统目录（系统表），因此跨数据库的查询如何执行是不定的。

　　附加增值模块contrib/dblink允许采用函数调用实现跨库查询。当然用户也可以同时连接到不同的数据库执行查询然后在客户端合并结果。

　　4.18)如何让函数返回多行或多列？

　　在函数中返回数据记录集的功能是很容易使用的，详情参见： http://techdocs.postgresql.org/guides/setreturningfunctions

　　4.19)为什么我在使用pl/pgsql函数存取临时表时会收到错误信息“relation with oid ##### does not exist”？

　　pl/pgsql会缓存函数的内容，由此带来的一个不好的副作用是若一个 pl/pgsql 函数访问了一个临时表，然后该表被删除并重建了，则再次调用该函数将失败， 因为缓存的函数内容仍然指向旧的临时表。解决的方法是在 pl/pgsql 中用execute 对临时表进行访问。这样会保证查询在执行前总会被重新解析。

　　4.27)目前有哪些数据复制方案可用？

　　“复制”只是一个术语，有好几种复制技术可使用，每种都有优点和缺点：

　　主/从复制方式是允许一个主服务器接受读/写的申请，而多个从服务器只能接受读/select查询的申请， 目前最流行且是免费的主/从 postgresql复制方案是 slony-i 。

　　多个主服务器的复制方式允许将读/写的申请发送给多台的计算机，这种方式由于需要在多台服务器之间同步数据变动 可能会带来较严重的性能损失，pgcluster是目前这种方案 中最好的，而且还可以免费下载。

　　也有一些商业需付费和基于硬件的数据复制方案，支持上述各种复制模型。