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)'——外層以單引号包裹。我們可以操作複合類型的整體，也可以針對其某幾個字段操作，具體請參考文檔。

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

-- 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.cnblogs.com/newton/p/5203957.html