以下內(nèi)容來(lái)自:http://www.huihoo.com/postgresql/pgsql-faq.html
操作問(wèn)題
4.1)如何只選擇一個(gè)查詢結(jié)果的頭幾行？或是隨機(jī)的一行？
如果你只是要提取幾行數(shù)據(jù)，并且你在執(zhí)行查詢中知道確切的行數(shù)，你可以使用LIMIT功能。 如果有一個(gè)索引與 ORDER BY中的條件匹配，PostgreSQL 可能就只處理要求的頭幾條記錄， （否則將對(duì)整個(gè)查詢進(jìn)行處理直到生成需要的行）。如果在執(zhí)行查詢功能時(shí)不知道確切的記錄數(shù)， 可使用游標(biāo)(cursor)和FETCH功能。
可使用以下方法提取一行隨機(jī)記錄的：
SELECT cols
FROM tab
ORDER BY random()
LIMIT 1 ;
4.2)如何查看表、索引、數(shù)據(jù)庫(kù)以及用戶的定義？如何查看psql里用到的查詢指令并顯示它們？
在psql中使用 \dt 命令來(lái)顯示數(shù)據(jù)表的定義，要了解psql中的完整命令列表可使用\? ，另外，你也可以閱讀 psql 的源代碼 文件pgsql/src/bin/psql/describe.c，它包括為生成psql反斜杠命令的輸出的所有 SQL 命令。你還可以帶 -E 選項(xiàng)啟動(dòng) psql， 這樣它將打印出執(zhí)行你在psql中所給出的命令的內(nèi)部實(shí)際使用的SQL查詢。PostgreSQL也提供了一個(gè)兼容SQL的INFORMATION SCHEMA接口， 你可以從這里獲取關(guān)于數(shù)據(jù)庫(kù)的信息。
在系統(tǒng)中有一些以pg_ 打頭的系統(tǒng)表也描述了表的定義。
使用 psql -l 指令可以列出所有的數(shù)據(jù)庫(kù)。
也可以瀏覽一下 pgsql/src/tutorial/syscat.source文件，它列舉了很多可從數(shù)據(jù)庫(kù)系統(tǒng)表中獲取信息的SELECT語(yǔ)法。
4.3)如何更改一個(gè)字段的數(shù)據(jù)類型？
在8.0版本里更改一個(gè)字段的數(shù)據(jù)類型很容易，可使用 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 指令來(lái)使系統(tǒng)收回?zé)o效數(shù)據(jù)所占用的空間。
4.4)一行記錄，一個(gè)表，一個(gè)庫(kù)的最大尺寸是多少？
下面是一些限制：
| 一個(gè)數(shù)據(jù)庫(kù)最大尺寸？ | 無(wú)限制（已存在有 32TB 的數(shù)據(jù)庫(kù)） |
| 一個(gè)表的最大尺寸？ | 32 TB |
| 一行記錄的最大尺寸？ | 1.6 TB |
| 一個(gè)字段的最大尺寸? | 1 GB |
| 一個(gè)表里最大行數(shù)？ | 無(wú)限制 |
| 一個(gè)表里最大列數(shù)？ | 250-1600 （與列類型有關(guān)） |
| 一個(gè)表里的最大索引數(shù)量？ | 無(wú)限制 |
當(dāng)然，實(shí)際上沒(méi)有真正的無(wú)限制，還是要受可用磁盤空間、可用內(nèi)存/交換區(qū)的制約。 事實(shí)上，當(dāng)這些數(shù)值變得異常地大時(shí)，系統(tǒng)性能也會(huì)受很大影響。
表的最大尺寸 32 TB 不需要操作系統(tǒng)對(duì)大文件的支持。大表用多個(gè) 1 GB 的文件存儲(chǔ)，因此文件系統(tǒng)尺寸的限制是不重要的。
如果缺省的塊大小增長(zhǎng)到 32K ，最大的表尺寸和最大列數(shù)還可以增加到四倍。
4.5)存儲(chǔ)一個(gè)典型的文本文件里的數(shù)據(jù)需要多少磁盤空間？
一個(gè) Postgres 數(shù)據(jù)庫(kù)（存儲(chǔ)一個(gè)文本文件）所占用的空間最多可能需要相當(dāng)于這個(gè)文本文件自身大小5倍的磁盤空間。
例如，假設(shè)有一個(gè) 100,000 行的文件，每行有一個(gè)整數(shù)和一個(gè)文本描述。 假設(shè)文本串的平均長(zhǎng)度為20字節(jié)。文本文件占用 2.8 MB。存放這些數(shù)據(jù)的 PostgreSQL 數(shù)據(jù)庫(kù)文件大約是 6.4 MB:
32 字節(jié): 每行的頭（估計(jì)值）
24 字節(jié): 一個(gè)整數(shù)型字段和一個(gè)文本型字段
+ 4 字節(jié): 頁(yè)面內(nèi)指向元組的指針
----------------------------------------
60 字節(jié)每行
PostgreSQL 數(shù)據(jù)頁(yè)的大小是 8192 字節(jié) (8 KB)，則：
8192 字節(jié)每頁(yè)
------------------- = 136 行/數(shù)據(jù)頁(yè)（向下取整）
60 字節(jié)每行
100000 數(shù)據(jù)行
-------------------- = 735 數(shù)據(jù)頁(yè)（向上取整）
128 行每頁(yè)
735 數(shù)據(jù)頁(yè) * 8192 字節(jié)/頁(yè) = 6,021,120 字節(jié)（6 MB）
索引不需要這么多的額外消耗，但也確實(shí)包括被索引的數(shù)據(jù)，因此它們也可能很大。
空值NULL存放在位圖中，因此占用很少的空間。
4.6)為什么我的查詢很慢？為什么這些查詢沒(méi)有利用索引？
并非每個(gè)查詢都會(huì)自動(dòng)使用索引。只有在表的大小超過(guò)一個(gè)最小值，并且查詢只會(huì)選中表中較小比例的記錄時(shí)才會(huì)采用索引。 這是因?yàn)樗饕龗呙枰鸬碾S即磁盤存取可能比直接地讀取表（順序掃描）更慢。
為了判斷是否使用索引，PostgreSQL必須獲得有關(guān)表的統(tǒng)計(jì)值。這些統(tǒng)計(jì)值可以使用 VACUUM ANALYZE，或 ANALYZE
獲得。 使用統(tǒng)計(jì)值，優(yōu)化器知道表中有多少行，就能夠更好地判斷是否利用索引。
統(tǒng)計(jì)值對(duì)確定優(yōu)化的連接順序和連接方法也很有用。在表的內(nèi)容發(fā)生變化時(shí)，應(yīng)定期進(jìn)行統(tǒng)計(jì)值的更新收集。
索引通常不用于 ORDER BY 或執(zhí)行連接。對(duì)一個(gè)大表的一次順序掃描，再做一個(gè)顯式的排序通常比索引掃描要快。
但是，在 LIMIT 和 ORDER BY 結(jié)合使用時(shí)經(jīng)常會(huì)使用索引，因?yàn)檫@只會(huì)返回表的一小部分。 實(shí)際上，雖然 MAX() 和 MIN() 并不使用索引，通過(guò)對(duì) ORDER BY 和 LLIMIT 使用索引取得最大值和最小值也是可以的：
SELECT col
FROM tab
ORDER BY col [ DESC ]
LIMIT 1;
如果你確信PostgreSQL的優(yōu)化器使用順序掃描是不正確的，你可以使用SET enable_seqscan TO 'off'指令， 然后再次運(yùn)行查詢，你就可以看出使用一個(gè)索引掃描是否確實(shí)要快一些。
當(dāng)使用通配符操作，例如 LIKE 或 ~ 時(shí)，索引只能在特定的情況下使用：
- 字符串的開始部分必須是普通字符串，也就是說(shuō)：
- LIKE 模式不能以 % 打頭。
- ~ （正則表達(dá)式）模式必須以 ^ 打頭。
- 字符串不能以匹配多個(gè)字符的模式類打頭，例如 [a-e]。
- 大小寫無(wú)關(guān)的查找，如 ILIKE 和 ~* 等不使用索引，但可以用 4.8 節(jié)描述的函數(shù)索引。
- 在做 initdb 時(shí)必須采用缺省的本地設(shè)置 C locale，因?yàn)橄到y(tǒng)不可能知道在非C locale情況時(shí)下一個(gè)最大字符是什么。 在這種情況下，你可以創(chuàng)建一個(gè)特殊的
text_pattern_ops索引來(lái)用于LIKE的索引。
在8.0之前的版本中，除非要查詢的數(shù)據(jù)類型和索引的數(shù)據(jù)類型相匹配，否則索引經(jīng)常是未被用到，特別是對(duì)int2,int8和數(shù)值型的索引。
4.7)我如何才能看到查詢優(yōu)化器是怎樣評(píng)估處理我的查詢？
參考 EXPLAIN 手冊(cè)頁(yè)。
4.8)我怎樣做正則表達(dá)式搜索和大小寫無(wú)關(guān)的正則表達(dá)式查找？怎樣利用索引進(jìn)行大小寫無(wú)關(guān)查找？
操作符 ~ 處理正則表達(dá)式匹配，而 ~* 處理大小寫無(wú)關(guān)的正則表達(dá)式匹配。大寫些無(wú)關(guān)的 LIKE 變種成為 ILIKE。
大小寫無(wú)關(guān)的等式比較通常寫做：
SELECT *
FROM tab
WHERE lower(col) = 'abc';
這樣將不會(huì)使用標(biāo)準(zhǔn)的索引。但是可以創(chuàng)建一個(gè)可被利用的函數(shù)索引:
CREATE INDEX tabindex ON tab (lower(col));
4.9)在一個(gè)查詢里，我怎樣檢測(cè)一個(gè)字段是否為 NULL ？我如何才能準(zhǔn)確排序而不論某字段是否含 NULL 值？
用 IS NULL 和 IS NOT NULL 測(cè)試這個(gè)字段，具體方法如下：
SELECT *
FROM tab
WHERE col IS NULL;
為了能對(duì)含 NULL字段排序，可在 ORDER BY 條件中使用 IS NULL和 IS NOT NULL 修飾符，條件為真 true 將比條件為假false 排在前面，下面的例子就會(huì)將含 NULL 的記錄排在結(jié)果的上面部分：
SELECT *
FROM tab
ORDER BY (col IS NOT NULL)
4.10)各種字符類型之間有什么不同？
| 類型 | 內(nèi)部名稱 | 說(shuō)明 |
|---|
| VARCHAR(n) | varchar | 指定了最大長(zhǎng)度，變長(zhǎng)字符串，不足定義長(zhǎng)度的部分不補(bǔ)齊 |
| CHAR(n) | bpchar | 定長(zhǎng)字符串，實(shí)際數(shù)據(jù)不足定義長(zhǎng)度時(shí)，以空格補(bǔ)齊 |
| TEXT | text | 沒(méi)有特別的上限限制（僅受行的最大長(zhǎng)度限制） |
| BYTEA | bytea | 變長(zhǎng)字節(jié)序列（使用NULL也是允許的） |
| "char" | char | 一個(gè)字符 |
在系統(tǒng)表和在一些錯(cuò)誤信息里你將看到內(nèi)部名稱。
上面所列的前四種類型是"varlena"（變長(zhǎng)）類型（也就是說(shuō)，開頭的四個(gè)字節(jié)是長(zhǎng)度，后面才是數(shù)據(jù)）。 于是實(shí)際占用的空間比聲明的大小要多一些。 然而這些類型都可以被壓縮存儲(chǔ)，也可以用 TOAST 脫機(jī)存儲(chǔ)，因此磁盤空間也可能比預(yù)想的要少。
VARCHAR(n) 在存儲(chǔ)限制了最大長(zhǎng)度的變長(zhǎng)字符串是最好的。 TEXT 適用于存儲(chǔ)最大可達(dá) 1G左右但未定義限制長(zhǎng)度的字符串。
CHAR(n) 最適合于存儲(chǔ)長(zhǎng)度相同的字符串。 CHAR(n)會(huì)根據(jù)所給定的字段長(zhǎng)度以空格補(bǔ)足（不足的字段內(nèi)容）， 而 VARCHAR(n) 只存儲(chǔ)所給定的數(shù)據(jù)內(nèi)容。 BYTEA 用于存儲(chǔ)二進(jìn)制數(shù)據(jù)，尤其是包含 NULL 字節(jié)的值。這些類型具有相似的性能特性。
4.11.1)我怎樣創(chuàng)建一個(gè)序列號(hào)/自動(dòng)遞增的字段？
PostgreSQL 支持 SERIAL 數(shù)據(jù)類型。它在字段上自動(dòng)創(chuàng)建一個(gè)序列和索引。例如：
CREATE TABLE person (
id SERIAL,
name TEXT
);
會(huì)自動(dòng)轉(zhuǎn)換為：
CREATE SEQUENCE person_id_seq;
CREATE TABLE person (
id INT4 NOT NULL DEFAULT nextval('person_id_seq'),
name TEXT
);
參考 create_sequence 手冊(cè)頁(yè)獲取關(guān)于序列的更多信息。
4.11.2)我如何獲得一個(gè)插入的序列號(hào)的值？
一種方法是在插入之前先用函數(shù) nextval() 從序列對(duì)象里檢索出下一個(gè) 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 表的外鍵）。 注意自動(dòng)創(chuàng)建的 SEQUENCE 對(duì)象的名稱將會(huì)是 <table>_<serialcolumn>_seq， 這里 table 和 serialcolumn 分別是你的表的名稱和你的 SERIAL 字段的名稱。
類似的，在 SERIAL 對(duì)象缺省插入后你可以用函數(shù) currval() 檢索剛賦值的 SERIAL 值，例如：
execute("INSERT INTO person (name) VALUES ('Blaise Pascal')");
new_id = execute("SELECT currval('person_id_seq')");
4.11.3)使用 currval() 會(huì)導(dǎo)致和其他用戶的沖突情況（race condition）嗎？
不會(huì)。currval() 返回的是你本次會(huì)話進(jìn)程所賦的值而不是所有用戶的當(dāng)前值。
4.11.4)為什么不在事務(wù)異常中止后重用序列號(hào)呢？為什么在序列號(hào)字段的取值中存在間斷呢？
為了提高并發(fā)性，序列號(hào)在需要的時(shí)候賦予正在運(yùn)行的事務(wù)，并且在事務(wù)結(jié)束之前不進(jìn)行鎖定， 這就會(huì)導(dǎo)致異常中止的事務(wù)后，序列號(hào)會(huì)出現(xiàn)間隔。
4.12)什么是 OID ？什么是 CTID ？
PostgreSQL 里創(chuàng)建的每一行記錄都會(huì)獲得一個(gè)唯一的OID，除非在創(chuàng)建表時(shí)使用WITHOUT OIDS選項(xiàng)。 OID創(chuàng)建時(shí)會(huì)自動(dòng)生成一個(gè)4字節(jié)的整數(shù)，所有 OID 在整個(gè) PostgreSQL 中均是唯一的。 然而，它在超過(guò)40億時(shí)將溢出， OID此后會(huì)出現(xiàn)重復(fù)。PostgreSQL 在它的內(nèi)部系統(tǒng)表里使用 OID 在表之間建立聯(lián)系。
在用戶的數(shù)據(jù)表中，最好是使用SERIAl來(lái)代替OID 因?yàn)?small>SERIAL只是保證在單個(gè)表中數(shù)據(jù)是唯一的，這樣它溢出的可能性就非常小了， SERIAL8可用來(lái)保存8字節(jié)的序列號(hào)字段。
CTID 用于標(biāo)識(shí)帶著數(shù)據(jù)塊（地址）和（塊內(nèi)）偏移的特定的物理行。 CTID 在記錄被更改或重載后發(fā)生改變。索引入口使用它們指向物理行。
4.13)為什么我收到錯(cuò)誤信息“ERROR: Memory exhausted in AllocSetAlloc()”？
這很可能是系統(tǒng)的虛擬內(nèi)存用光了，或者內(nèi)核對(duì)某些資源有較低的限制值。在啟動(dòng) postmaster 之前試試下面的命令：
ulimit -d 262144
limit datasize 256m
取決于你用的 shell，上面命令只有一條能成功，但是它將把你的進(jìn)程數(shù)據(jù)段限制設(shè)得比較高，
因而也許能讓查詢完成。這條命令應(yīng)用于當(dāng)前進(jìn)程，以及所有在這條命令運(yùn)行后創(chuàng)建的子進(jìn)程。
如果你是在運(yùn)行SQL客戶端時(shí)因?yàn)楹笈_(tái)返回了太多的數(shù)據(jù)而出現(xiàn)問(wèn)題，請(qǐng)?jiān)谶\(yùn)行客戶端之前執(zhí)行上述命令。
4.14)我如何才能知道所運(yùn)行的 PostgreSQL 的版本？
從 psql 里，輸入 SELECT version();指令。
4.15)我如何創(chuàng)建一個(gè)缺省值是當(dāng)前時(shí)間的字段？
使用 CURRENT_TIMESTAMP：
CREATE TABLE test (x int, modtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
4.16)我怎樣進(jìn)行 outer join （外連接）？
PostgreSQL 采用標(biāo)準(zhǔn)的 SQL 語(yǔ)法支持外連接。這里是兩個(gè)例子：
SELECT *
FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);
或是
SELECT *
FROM t1 LEFT OUTER JOIN t2 USING (col);
這兩個(gè)等價(jià)的查詢?cè)?t1.col 和 t2.col 上做連接，并且返回 t1 中所有未連接的行（那些在 t2 中沒(méi)有匹配的行）。
右[外]連接(RIGHT OUTER JOIN)將返回 t2 中未連接的行。 完全外連接（FULL OUTER JOIN）將返回 t1 和
t2 中未連接的行。 關(guān)鍵字 OUTER 在左[外]連接、右[外]連接和完全[外]連接中是可選的，普通連接被稱為內(nèi)連接（INNER
JOIN）。
4.17)如何使用涉及多個(gè)數(shù)據(jù)庫(kù)的查詢？
沒(méi)有辦法查詢當(dāng)前數(shù)據(jù)庫(kù)之外的數(shù)據(jù)庫(kù)。 因?yàn)?PostgreSQL 要加載與數(shù)據(jù)庫(kù)相關(guān)的系統(tǒng)目錄（系統(tǒng)表），因此跨數(shù)據(jù)庫(kù)的查詢?nèi)绾螆?zhí)行是不定的。
附加增值模塊contrib/dblink允許采用函數(shù)調(diào)用實(shí)現(xiàn)跨庫(kù)查詢。當(dāng)然用戶也可以同時(shí)連接到不同的數(shù)據(jù)庫(kù)執(zhí)行查詢?nèi)缓笤诳蛻舳撕喜⒔Y(jié)果。
4.18)如何讓函數(shù)返回多行或多列？
在函數(shù)中返回?cái)?shù)據(jù)記錄集的功能是很容易使用的，詳情參見： http://techdocs.postgresql.org/guides/SetReturningFunctions
4.19)為什么我在使用PL/PgSQL函數(shù)存取臨時(shí)表時(shí)會(huì)收到錯(cuò)誤信息“relation with OID ##### does not exist”？
PL/PgSQL會(huì)緩存函數(shù)的內(nèi)容，由此帶來(lái)的一個(gè)不好的副作用是若一個(gè) PL/PgSQL 函數(shù)訪問(wèn)了一個(gè)臨時(shí)表，然后該表被刪除并重建了，則再次調(diào)用該函數(shù)將失敗， 因?yàn)榫彺娴暮瘮?shù)內(nèi)容仍然指向舊的臨時(shí)表。解決的方法是在 PL/PgSQL 中用EXECUTE 對(duì)臨時(shí)表進(jìn)行訪問(wèn)。這樣會(huì)保證查詢?cè)趫?zhí)行前總會(huì)被重新解析。
4.27)目前有哪些數(shù)據(jù)復(fù)制方案可用？
“復(fù)制”只是一個(gè)術(shù)語(yǔ)，有好幾種復(fù)制技術(shù)可使用，每種都有優(yōu)點(diǎn)和缺點(diǎn)：
主/從復(fù)制方式是允許一個(gè)主服務(wù)器接受讀/寫的申請(qǐng)，而多個(gè)從服務(wù)器只能接受讀/SELECT查詢的申請(qǐng)， 目前最流行且是免費(fèi)的主/從 PostgreSQL復(fù)制方案是 Slony-I 。
多個(gè)主服務(wù)器的復(fù)制方式允許將讀/寫的申請(qǐng)發(fā)送給多臺(tái)的計(jì)算機(jī)，這種方式由于需要在多臺(tái)服務(wù)器之間同步數(shù)據(jù)變動(dòng) 可能會(huì)帶來(lái)較嚴(yán)重的性能損失，Pgcluster是目前這種方案 中最好的，而且還可以免費(fèi)下載。
也有一些商業(yè)需付費(fèi)和基于硬件的數(shù)據(jù)復(fù)制方案，支持上述各種復(fù)制模型。