本文針對目前最新版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. 函數體以雙"$"符號或單引號包裹，若用單引號包裹則需要注意特殊字符轉義?？梢栽诤瘮刁w內以參數名（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)'——外層以單引號包裹。我們可以操作復合類型的整體，也可以針對其某幾個字段操作，具體請參考文檔。

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

-- 1
CREATE 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）保持一致

-- 2
CREATE 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.

Polymorphic SQL Functions：運行select array_to_string('{"meeting", "lunch"}',',');報錯：could not determine polymorphic type because input has type "unknown"。原因：SQL functions can be declared to accept and return the polymorphic types anyelement, anyarray, anynonarray, anyenum, and anyrange.This is required if the argument is just a string literal, since otherwise it would be treated as type unknown。而通過文檔發現array_to_string(anyarray, text [, text])，array_to_string就是Polymorphic SQL Function。so，要么換成Array形式，要么顯式類型轉換。

穩定性級別：

PostgreSQL中的函數在定義時有三種穩定性級別：VOLATILE(不穩定)、STABLE(穩定)和IMMUTABLE(非常穩定)。默認情況下，CREATE FUNCTION創建函數的穩定性為VOLATILE。穩定性級別使得優化器可以判斷不同函數的行為。

VOLATILE函數可以做任何事情，包括修改數據庫。在調用中，輸入同樣的參數會返回不同的結果，優化器并不對這一類函數的行為做任何假設。在一個Query中，對于每一行都會重新計算該函數。
STABLE函數不能修改數據庫，單個Query中所有行給定同樣的參數確保返回相同的結果。這種穩定級別允許優化器將多次函數調用轉換為一次。在索引掃描的條件中使用這種函數是可行的，因為索引掃描只計算一次比較值(comparison value),而不是每行都計算一次。
IMMUTABLE函數不能修改數據庫，在任何情況下，只要輸入參數相同，返回結果就相同。這種級別的函數，優化器可以提前進行計算，在查詢過程中作為常量參數。比如：SELECT...WHERE x=2+2 可以簡化為SELECT...WHERE x=4。

為了得到最佳的優化結果，在創建函數時我們應該指定嚴格的穩定性級別。

任何有副作用的函數都應該被標記為VOLATILE；另外，有些沒有副作用但在一次query中值會發生改變的函數也應該標記為VOLATILE，比如random(),currval(),timeofday()。current_timestamp類型的函數應該被標記為STABLE，因為它們的值在同一事務中不會發生改變。

PostgreSQL還支持函數重載。。。，所以pg的函數定位還和所傳參數的個數有關。同時還支持可變參數和參數默認值，某種程度上提高了編寫函數的靈活性，但是還是有不方便的地方，比如參數默認值，假如一個函數有多個參數具有默認值，調用的時候，我想保留前面的參數默認值不變，只改變后面若干個參數值，那么也需要傳遞前面不變的和后面變了的參數值，而不像C#一樣，可以指定參數名傳遞值。所以有些時候還是得寫很多個重載，或者在函數內部作判斷。

對于返回setof的函數來說，通過select func方式返回的是一列(已逗號分隔的字符串)，select * from func返回的才是表格格式。

動態SQL

有時候我們會拼接字符串后，再執行該條語句，PostgreSQL也對這種情況作了支持（注意此時PL/pgSQL's normal attempts to cache plans for commands will not work in such scenarios.執行計劃是每次執行動態語句時臨時做的）。以PL/Pgsql為例，語法如下：

EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

上式中的可選項target表示a record variable, a row variable, or a comma-separated list of simple variables and record/row fields。如果要返回結果集，那么需要用到RETURN QUERY的一個變形：RETURN QUERY EXECUTE command-string [ USING expression [, ... ] ]; 參數表達式可以通過USING插入到計算查詢字符串中，以EXECUTE命令的同樣方式。

PostgreSQL也提供了一些字符串處理函數，可以更方便地拼接字符串。

quote_ident：Return the given string suitably quoted to be used as an identifier in an SQL statement string。在字符串是表名列名等標識數據庫對象時候有用。

quote_literal：Return the given string suitably quoted to be used as a string literal in an SQL statement string.它會對一些特殊字符進行轉義。

quote_nullable：當傳入參數可能為null時，可使用quote_nullable，而不是quote_literal。前者返回字符串格式的'Null'，后者返回的就是Null。當然了pg中所有東西與null比較返回的都是null，這點需要注意。

format：EXECUTE format('UPDATE tbl SET %I = %L WHERE key = %L', colname, newvalue, keyvalue); or EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname) USING newvalue, keyvalue; 后者更有效率，because the parameters newvalue and keyvalue are not converted to text.注意format的格式化類型字符s, I, L. 分別表示字符串, identified, 和literal（注意s、L不要搞反了）。示例：

 1 CREATE OR REPLACE FUNCTION func_get_merchandises(
 2     keyword text,
 3     isinland boolean,
 4     startindex integer DEFAULT 0,
 5     takecount integer DEFAULT 20,
 6     sortfield text DEFAULT 'MerchandiseName'::text,
 7     sortorder text DEFAULT 'asc'::text)
 8   RETURNS SETOF "Merchandises" AS
 9 
10 $BODY$
11 begin    
12     return query EXECUTE 
13     format('select m.* from "Merchandises" m    
14     where m.tsv @@ plainto_tsquery($1) and m."IsInland"=$2
15     order by %I %s limit $3 offset $4',sortfield,sortorder) using keyword,isinland,takecount,startindex;
16 end
17 $BODY$
18   LANGUAGE plpgsql VOLATILE

需要注意的是第15行sortfield和sortorder不能作為execute參數放在using后面，否則并不會替換，因為前者是標識變量（如表名列名），后者為何并不非常清楚。using參數適用場景，在文檔中稍有提及。

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_addresses
3 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 psql
postgres=# ALTER USER postgres WITH PASSWORD 'postgres';

EF CodeFirst with PostgreSQL（暫緩）

目前EF操作PostgreSQL使用的是EntityFramework6.Npgsql，版本為3.0.5，大部分的數據類型都支持使用linq操作，然而json和array并不在此列（雖然它所基于的Npgsql是支持json和array類型的）。

Npgsql does support TransactionScope，有兩種方法：Include Enlist=true in your connection string, or Call NpgsqlConnection.EnlistTransaction

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

Postgres中執行：UPDATE "TTest" SET "CTest"= floor(random()*16+1); 會發現每條記錄的CTest并不一致，可知并非先生成隨機數再統一賦值，而是逐一生成不同的隨機數。不知道其它數據庫是怎樣。

postgres游標:

do $$
declare 
  tn text;
  curs1 CURSOR for select tablename from pg_tables where schemaname='public';
begin

    OPEN curs1;
    loop
    fetch curs1 into tn;
    if not found then 
    exit;
    end if;
    --RAISE INFO 'VARIABLE: %', tn;
    EXECUTE 'ALTER TABLE "' || tn || '" OWNER TO masondever';     
    end loop;
    close curs1;

end;

$$language plpgsql;

有用的函數：coalesce、string_agg

視圖分為普通視圖和物化視圖，物化視圖是物理存在的，可以認為是數據庫層的緩存或臨時存儲，當基礎表數據更新時，需要手動刷新。由于物化視圖是真實存在的，可在其上建索引提高查詢效率。物化視圖創建——CREATE MATERIALIZED VIEW ...

關于PL/Pgsql的語法可參看Chapter 40. PL/pgSQL - SQL Procedural Language。文檔中所說的SQL語法相對PL/Pgsql來說，your client application must send each query to the database server, wait for it to be processed, receive and process the results, do some computation, then send further queries to the server. 這應該指的是標準SQL語法沒有變量和控制結構等，導致中間過程處理只能移到外部（比如應用層）。

發現了一個比較坑的地方（使用的是9.4版）：select r.ShopId from (select p."ShopId" from "IndexProductInfoes" p) r 是不行的，因為pg對大小寫敏感，列名若有大寫字母，則必須以雙引號括起來，否則會報錯“column r.shopid does not exist”，可以看到，雖然代碼里我們寫的是r.ShopId，但提示信息是shopid，pg自動給轉為小寫了。以下幾種寫法都是正確的：

1、select r."ShopId" from (select p."ShopId" from "IndexProductInfoes" p) r; --結果列名ShopId

2、select r.ShopId from (select p."ShopId" ShopId from "IndexProductInfoes" p) r; --結果列名shopid

3、select r.shopid from (select p."ShopId" ShopId from "IndexProductInfoes" p) r; --結果列名shopid

pgsql（目前最新的13版本）中的主鍵并不像其它關系型數據庫如mysql、sqlsever那樣會自動有“索引”的特性，它其實只是一個唯一約束，如果需要按主鍵排序，需要另外給主鍵創建索引。注意如此創建的索引是非聚集索引。如果要創建聚集索引，可以使用“CLUSTER”命令，但是該命令的效果是一次性的，when the table is subsequently updated, the changes are not clustered. That is, no attempt is made to store new or updated rows according to their index order.

參考資料：

SQL、T-SQL與PL-SQL的區別

轉載請注明本文出處：http://www.rzrgm.cn/newton/p/5203957.html