PostgreSQL操作問題

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)一行記錄，一個表，一個庫的最大尺寸是多少？
下面是一些限制：

一個資料庫最大尺寸？ 無限制（已存在有 32TB 的資料庫）
一個表的最大尺寸？ 32 TB
一行記錄的最大尺寸？ 1.6 TB
一個欄位的最大尺寸? 1 GB
一個表裡最大行數？ 無限制
一個表裡最大列數？ 250-1600 （與列類型有關）
一個表裡的最大索引數量？ 無限制

當然，實際上沒有真正的無限制，還是要受可用磁碟空間、可用記憶體/交換區的制約。 事實上，當這些數值變得異常地大時，系統效能也會受很大影響。

表的最大尺寸 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 模式不能以 % 打頭。
~ （Regex）模式必須以 ^ 打頭。
字串不能以匹配多個字元的模式類打頭，例如 [a-e]。
大小寫無關的尋找，如 ILIKE 和 ~* 等不使用索引，但可以用 4.8 節描述的函數索引。
在做 initdb 時必須採用預設的本地設定 C locale，因為系統不可能知道在非C locale情況時下一個最大字元是什麼。 在這種情況下，你可以建立一個特殊的text_pattern_ops索引來用於LIKE的索引。
在8.0之前的版本中，除非要查詢的資料類型和索引的資料類型相匹配，否則索引經常是未被用到，特別是對int2,int8和數值型的索引。

4.7)我如何才能看到查詢最佳化工具是怎樣評估處理我的查詢？
參考 EXPLAIN 手冊頁。

4.8)我怎樣做Regex搜尋和大小寫無關的Regex尋找？怎樣利用索引進行大小寫無關尋找？
操作符 ~ 處理Regex匹配，而 ~* 處理大小寫無關的Regex匹配。大寫些無關的 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)各種字元類型之間有什麼不同？
類型 內部名稱 說明
VARCHAR(n) varchar 指定了最大長度，變長字串，不足定義長度的部分不補齊
CHAR(n) bpchar 定長字串，實際資料不足定義長度時，以空格補齊
TEXT text 沒有特別的上限限制（僅受行的最大長度限制）
BYTEA bytea 變長位元組序列（使用NULL也是允許的）
"char" char 一個字元

在系統資料表和在一些錯誤資訊裡你將看到內部名稱。

上面所列的前四種類型是"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是目前這種方案 中最好的，而且還可以免費下載。

也有一些商業需付費和基於硬體的資料複製方案，支援上述各種複製模型。

 

轉自：http://www.xxlinux.com/linux/article/development/database/20060906/3988_2.html