PostgreSQL 9.5.1官方文檔學習筆記

PostgreSQL 9.5.1官方文檔學習筆記

本文針對目前最新版PostgreSQL 9.5.1，若非說明，文中所說文檔即指官方文檔。本人剛接觸PostgreSQL不久，文中不免錯漏，請大家指正；隨著瞭解深入，本文[可能]會不定期更新補足。

JSON

PostgreSQL支援Json格式資料，有兩種類型：json和jsonb。兩者在效率上有所區別，而這是因為jsonb儲存的是格式化後的位元據，所以在寫入時，json類型比較快，而在檢索時（注意這裡說的檢索不是簡單的讀取整個資料，而是比如檢索json資料中某個鍵的值的情境），jsonb效率較高。一般情況下，使用jsonb就可以了。json資料是為了彌補關係型資料在伸縮性擴充性上的不足，但是文檔也說了，不能啥都往裡放，要考慮資料原子性和資料大小。

json類型可以作包含判斷和是否存在的判斷（containment or existence），表示符號分別為@>和?（以及其它一些變種）。對於這兩種牽涉到多個鍵和元素的判斷情境，json類型比下面要講的arrays更適合，因為其對查詢有內在的最佳化機制，而array只是單純的線性尋找。

若json列需要經常檢索，那麼可以在其上建立GIN索引，jsonb支援兩種特有的GIN索引jsonb_ops和jsonb_path_ops。建立的文法如下：

CREATE INDEX idxgin ON api USING GIN (jdoc);CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops); -- 只是比前一行多了jsonb_path_ops標記

The jsonb_path_ops supports indexing the @> operator only. 關於這兩者使用和技術實現上的區別可參看：PgSQL 9.4 新特性jsonb類型解析，PostgreSQL 9.4 中使用 jsonb

我們可以對json資料中的某一屬性建GIN索引（可稱之為屬性索引），如：CREATE INDEX idxgintags ON api USING GIN ((jdoc -> ’tags’));  這能提升檢索索引值對的效率，比如如下情境：

SELECT jdoc->’guid’, jdoc->’name’ FROM api WHERE jdoc -> ’tags’ ? ’qui’;

當然我們也可以不使用屬性索引，而是換一種查詢方式：

SELECT jdoc->’guid’, jdoc->’name’ FROM api WHERE jdoc @> ’{"tags": ["qui"]}’;

jsonb also supports btree and hash indexes. These are usually useful only if it’s important to check equality of complete JSON documents.

Array

PostgreSQL支援Array類型，其欄位聲明有如下幾種方式：

1 CREATE TABLE emptable (2  　　arraycol1 integer[],3  　　arraycol2 text[][],4  　　arraycol3 text[3],5  　　arraycol4 integer ARRAY,6  　　arraycol5 integer ARRAY[4]7 );

在列聲明時我們可以指定數組中的元素類型、維度和長度，後兩者然並卵，目前的版本的PostgreSQL會忽略這兩者的設定，它們更多是以一種備忘的意義存在。

插入格式如下：

INSERT INTO emptable VALUES (    ’{10000, 10000, 10000, 10000}’,     ’{{"meeting", "lunch"}, {"training", "presentation"}}’,
　　ARRAY[10000, 10000, 10000, 10000], 
　　ARRAY[[’meeting’, ’lunch’], [’training’, ’presentation’]]);

注意字串的寫法，第3行單引號內部是以雙引號包含，第5行ARRAY建構函式方式則是以單引號包含。多維陣列中每個元素的長度要一致，否則會報錯，比如不能

INSERT INTO emptable VALUES (    ’{10000, 10000, 10000, 10000}’,     ’{{"meeting", "lunch"}, {"training"}}’ -- error);

訪問，arraycol[n]，PostgreSQL的數組預設下標是1基的，這點需要注意，即預設情況下我們訪問數組第項應使用arraycol[1]，而非慣常的arraycol[0]，當然我們可以 SET arraycol[-2:7] = '{XXOO,...}'的方式設定數組的上下界（這個例子就變成了-2基）；多維陣列訪問，以二維數組為例，arraycol[n][m]；若下標超出數組長度則返回null，並不會拋出異常。若訪問數組某部分毗鄰元素，則需要用到slice形式，形如arraycol[1:3][2:5]，表示要訪問1到3項，並且取這三項中的2到5項——仍以數組形式——返回，第一個中括弧表示第1維，第二個表示第2維，以此類推。需要注意的是arraycol[1:3][2]，並不是表示取1到3項中的第2項，PostgreSQL認為只要有一個維度是slice形式，則所有你要訪問的維度都是slice形式，若只有1位元，則前面附加1:，即arraycol[1:3][2] == arraycol[1:3][1:2]。如果slice的下標超出數組長度，又會怎樣呢？有兩種情況：若起始下標就超出了，那麼返回空數組（文檔中說是因為曆史原因）；若只是結束下標超出，則返回從起始下標到數組末尾這段資料。

一些函數：array_dims，以文本形式返回數組的所有維度；array_length，指定維度數組長度；array_upper，返回指定維度上界；array_lower，返回指定維度下界；cardinality，所有維度元素個數總和（不知能否用於子數組或子維度）。

對於一維數組，set arraycol[m] = xxoo，若m大於當前長度，那麼arraycol將自動擴充到m上界，而原上界到新上界之間位置的項將置為null，重複一遍，目前只有一維數組有這個特性。

array_prepend、array_append、array_cat用於元素的頭尾插入或數組的串連，前兩者只能用於一維數組，一般我們可以使用串連符 || 來提供這三者的功能。

數組檢索相關：any、all、generate_subscripts、array_position、array_positions、&&（左運算元是否包含右運算元）。關於數組檢索，官方文檔有這麼段提示：數組不是集合，搜尋數組中的特定元素通常表明你的資料庫設計有問題。 數組欄位通常是可以分裂成獨立的表（with a row for each item of the array）。 很明顯表要容易搜尋得多，並且在元素數目非常龐大的時候也可以更好地伸展。這似乎表示數組是設計用來進行直接展示的，若業務查詢需要關聯陣列中的特定值，則需要考慮重新設計或使用其它類型。

插：在使用MySql的時候，我們通常會被告知，使用有最大長度的char或者varchar會在效能方面有好處，而在PostgreSQL中，卻不一定是這樣。在PostgreSQL中，這三種類型的字串資料並沒有明顯的效能差別，而且character(n)類型的資料一般是最慢的，因為固定長度導致更多的儲存空間。所以，一般來說，text或者character varying就行了。

Functions

PostgreSQL沒有預存程序的概念（博主也不明白為何其它資料庫要劃分預存程序和函數）。函數會返回最後一條語句的結果[的第一行資料]；若要返回結果集，需要顯示聲明要返回某類型的結果集或Table。 Unless the function is declared to return void, the last statement must be a SELECT, or an INSERT, UPDATE, or DELETE that has a RETURNING clause. You cannot use transaction control commands, e.g. COMMIT, SAVEPOINT, and some utility commands, e.g. VACUUM, in SQL functions. 函數體以雙"$"符號或單引號包裹，若用單引號包裹則需要注意特殊字元轉義。可以在函數體內以參數名（9.2及以後版本支援）或"$n"的方式引用參數。舉個例子：

CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$     UPDATE bank         SET balance = balance - debit         WHERE accountno = tf1.accountno -- 由於參數名和列同名，前面需要加函數名作為首碼    -- ;SELECT balance FROM bank WHERE accountno = tf1.accountno;     RETURNING balance; $$ LANGUAGE SQL;

在INSERT INTO或者UPDATE的時候在最後面加上RETURNING colname，PostgreSQL會在插入或者更新資料之後會返回你指定的欄位。

函數可以接收、返回多個欄位，將這多個欄位看作一個整體，稱為複合類型。比如資料表中的一行，或者使用ROW建構函式構造的一行資料，或者以逗號分隔的多個欄位。我們可以顯式定義自己的複合類型，如：

CREATE TYPE inventory_item AS (    name            text,    supplier_id     integer,    price           numeric);

然後就可以將inventory_item用於很多地方了，甚至將一個表欄位類型設定為inventory_item，如下：

CREATE TABLE on_hand (    item      inventory_item,    count     integer);INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

在你建立表的時候，也會自動建立一個複合類型，名字與表名字相同，表示該表的複合類型。需要注意的是，表定義的各項約束（如不可為空白）對自動建立的同名複合類型無效。

關於複合類型值寫法，上面的ROW方式比較常用，如果是多個欄位，那麼ROW可以省略，即('fuzzy dice', 42, 1.99)；還可以一般格式——'("fuzzy dice",42,1.99)'——外層以單引號包裹。我們可以操作複合類型的整體，也可以針對其某幾個欄位操作，具體請參考文檔。

回到函數的介紹，比如下面兩段代碼錶示的是同一個意思：

-- 1CREATE FUNCTION new_emp() RETURNS emp AS $$     SELECT         text ’None’ AS name,  -- 注意類型轉換        1000.0 AS salary,         25 AS age,         point ’(2,2)’ AS cubicle; $$ LANGUAGE SQL;    -- 欄位順序和類型要和傳回型別（此處是emp）保持一致-- 2CREATE FUNCTION new_emp() RETURNS emp AS $$     SELECT ROW(’None’, 1000.0, 25, ’(2,2)’)::emp; $$ LANGUAGE SQL;

雙冒號:: 表示類型轉換。

前面說到，函數能返回集合和表，返回表是最近出版的SQL標準之一，所以可能比返回集合更好一點；但是對於返回表來說，It is not allowed to use explicit OUT or INOUT parameters with the RETURNS TABLE notation — you must put all the output columns in the TABLE list.

PostgreSQL安裝與配置

不得不說，自從入門了Linux之後，技術接觸面廣了很多，更樂意嘗試.NET“標配”之外的東西。要在CentOS7.0上安裝PostgreSQL，先到PostgreSQL RPM Building Project - Repository Packages找到對應的RPM包，並用yum安裝：

yum install https://download.postgresql.org/pub/repos/yum/9.5/RedHat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm

上步只是install了RPM包，then，安裝postgresql-server（還有其它一些packages，不過我們暫時安裝postgresql-server即可）

yum install postgresql95-server

如果你安裝的是9.4的版本，只要把上面的數字95改成94即可。

Due to policies for Red Hat family distributions, the PostgreSQL installation will not be enabled for automatic start or have the database initialized automatically. To make your database installation complete, you need to perform these two steps:

/usr/pgsql-9.5/bin/postgresql95-setup initdb    #初始化庫systemctl start postgresql-9.5.service    #啟動

為了其它主機能串連到伺服器，需要進行一些配置。關於系統參數配置，PostgreSQL提供了多種方式，適用情境稍有不同。這裡選擇編輯postgresql.conf檔案的方式，另外還有個postgresql.auto.conf檔案，儲存的是系統參數預設值，是不允許直接編輯的，可以使用ALTER SYSTEM 命令進行配置值設定。postgresql.conf儲存在PostgreSQL的data目錄下，data目錄可以在initdb時指定，如下：

initdb -D /usr/local/pgsql/data
#或者如下
pg_ctl -D /usr/local/pgsql/data initdb

This may be more intuitive if you are using pg_ctl for starting and stopping the server, so that pg_ctl would be the sole command you use for managing the database server instance.
不指定data目錄的話，會預設給你一個，博主這用find命令看到是/var/lib/pgsql/9.5/data/。因為PostgreSQL執行個體是依賴於data目錄的，所以可以在一台機子上開多個執行個體，每個執行個體都有自己的data目錄，配置自然也不同；要pg_ctl啟動、停止、重啟等操作時需要帶上data目錄，或者指定PGDATA環境變數，否則不知道針對哪個執行個體進行操作。

find / -name postgresql.conf
#輸出 /var/lib/pgsql/9.5/data/postgresql.conf

找到之後，就可以進行設定了，順便熟悉下vi的操作。

1 vi postgresql.conf     #開啟，此時為一般模式2 /address     #定位到listen_addresses3 0     #或者home鍵，移動游標到該行最前面4 X     #刪除最前面的井號，即取消該行注釋5 a     #A、i、I等皆可，進入編輯模式，將listen_addresses設為'localhost,開發機IP'6 <Esc>     #返回一般模式7 4<Enter>     #向下移動4行，定位到#port=5432，同樣刪除前面的井號8 :wq     #儲存並退出vi

開放連接埠centos7之後使用firewall：

firewall-cmd  --permanent --zone=public --add-port=5432/tcp

另外還要修改pg_hba.conf檔案，允許開發機串連（竊以為這裡和postgresql.conf的listen_addresses稍有重複了），這裡就不細說了，注意使用md5方式，表示用戶端需要使用使用者名稱和密碼（加密）串連服務端。重啟PostgresQL。

pg_ctl -D /var/lib/pgsql/9.5/data restart

最後修改預設使用者postgres的密碼。

# sudo -u postgres psqlpostgres=# ALTER USER postgres WITH PASSWORD 'postgres';

EF CodeFirst with PostgreSQL（暫緩）

後記：為什麼要選擇PostgreSQL？關聯式資料庫，博主接觸最多的是SQLSERVER和MYSQL，目前基本上已經告別SQLSERVER，你懂的；MYSQL號稱最流行，這點毋庸置疑，但如此流行的原因未必是因為最好的，或者最適用的。在MYSQL裡面做遞迴（遞迴不是SQL標準），基本上多少都是個坑，似乎也不太跟得上時代的腳步，對NoSQL的支援薄弱，如果你說它只要做好關係型資料庫的本分，那麼某些SQL標準尚不支援，比如LATERAL 。而PostgreSQL號稱是全球/宇宙最先進的資料庫，雖有誇大其詞之嫌，確實功能比較全面，而且開源，開源協議是MIT，比MYSQL的GPL來得更自由。

更多MongoDB相關教程見以下內容：

CentOS 編譯安裝 MongoDB與mongoDB的php擴充

CentOS 6 使用 yum 安裝MongoDB及伺服器端配置

Ubuntu 13.04下安裝MongoDB2.4.3

MongoDB入門必讀(概念與實戰並重)

Ubunu 14.04下MongoDB的安裝指南

《MongoDB 權威指南》(MongoDB: The Definitive Guide)英文文字版[PDF]

Nagios監控MongoDB分區叢集服務實戰

基於CentOS 6.5作業系統搭建MongoDB服務

MongoDB 的詳細介紹：請點這裡
MongoDB 的：請點這裡

本文永久更新連結地址：