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