PostgreSQL 教程

​ — — — — —— 參考： 菜鳥教程

ORDBMS 術語

在我們開始學習 PostgreSQL 資料庫前，讓我們先瞭解下 ORDBMS 的一些術語：

• 資料庫: 資料庫是一些關聯表的集合。
• 數據表: 表是數據的矩陣。在一個資料庫中的表看起來像一個簡單的電子錶格。
• 列: 一列(數據元素) 包含了相同的數據, 例如郵政編碼的數據。
• 行：一行（=元組，或記錄）是一組相關的數據，例如一條用戶訂閱的數據。
• 冗餘：存儲兩倍數據，冗餘降低了性能，但提高了數據的安全性。
• 主鍵：主鍵是唯一的。一個數據表中只能包含一個主鍵。你可以使用主鍵來查詢數據。
• 外鍵：外鍵用於關聯兩個表。
• 複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
• 索引：使用索引可快速訪問資料庫表中的特定信息。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
• 參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證數據的一致性。

PostgreSQL 特征

• 函數：通過函數，可以在資料庫伺服器端執行指令程式。

• 索引：用戶可以自定義索引方法，或使用內置的 B 樹，哈希表與 GiST 索引。

• 觸發器：觸發器是由SQL語句查詢所觸發的事件。如：一個INSERT語句可能觸發一個檢查數據完整性的觸發器。觸發器通常由INSERT或UPDATE語句觸發。 多版本併發控制：PostgreSQL使用多版本併發控制（MVCC，Multiversion concurrency control）系統進行併發控制，該系統向每個用戶提供了一個資料庫的"快照"，用戶在事務內所作的每個修改，對於其他的用戶都不可見，直到該事務成功提交。

• 規則：規則（RULE）允許一個查詢能被重寫，通常用來實現對視圖（VIEW）的操作，如插入（INSERT）、更新（UPDATE）、刪除（DELETE）。

• 數據類型：包括文本、任意精度的數值數組、JSON 數據、枚舉類型、XML 數據

  等。

• 全文檢索：通過 Tsearch2 或 OpenFTS，8.3版本中內嵌 Tsearch2。

• NoSQL：JSON，JSONB，XML，HStore 原生支持，至 NoSQL 資料庫的外部數據包裝器。

• 數據倉庫：能平滑遷移至同屬 PostgreSQL 生態的 GreenPlum，DeepGreen，HAWK 等，使用 FDW 進行 ETL。

postgreSQL 安裝

打開 PostgreSQL 官網 https://www.postgresql.org/，點擊菜單欄上的 Download ，可以看到這裡包含了很多平臺的安裝包，包括 Linux、Windows、Mac OS等 。

Linux

安裝代碼

sudo apt-get update
sudo apt-get install postgresql postgresql-client

會創建一個超級資料庫超級用戶 postgres 密碼為空

sudo -i -u postgres

這時使用以下命令進入 postgres，輸出以下信息，說明安裝成功：

~$ psql
psql (9.5.17)
Type "help" for help.

postgres=# 

輸入以下命令退出 PostgreSQL 提示符：

\q

PostgreSQL 安裝完成後預設是已經啟動的，但是也可以通過下麵的方式來手動啟動服務。

sudo /etc/init.d/postgresql start   # 開啟
sudo /etc/init.d/postgresql stop    # 關閉
sudo /etc/init.d/postgresql restart # 重啟

Windows

這裡使用 EnterpriseDB 來下載安裝，EnterpriseDB 是全球唯一一家提供基於 PostgreSQL 企業級產品與服務的廠商。

下載地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads。

預設安裝下一步就可以了

mac 沒有mac

PostgreSQL 數據類型

PostgreSQL提 供了豐富的數據類型。用戶可以使用 CREATE TYPE 命令在資料庫中創建新的數據類型。PostgreSQL 的數據類型有很多種，下麵我們具體來說明。

數據類型

貨幣類型

money 類型存儲帶有固定小數精度的貨幣金額。

numeric、int 和 bigint 類型的值可以轉換為 money，不建議使用浮點數來處理處理貨幣類型，因為存在舍入錯誤的可能性。

字元類型

下表列出了 PostgreSQL 所支持的字元類型：

日期/時間類型

下表列出了 PostgreSQL 支持的日期和時間類型。

布爾類型

PostgreSQL 支持標準的 boolean 數據類型。

boolean 有"true"(真)或"false"(假)兩個狀態， 第三種"unknown"(未知)狀態，用 NULL 表示。

枚舉類型 （類似自定義數據類型）

枚舉類型是一個包含靜態和值的有序集合的數據類型。

PostgreSQL 中的枚舉類型類似於 C 語言中的 enum 類型。

與其他類型不同的是枚舉類型需要使用 CREATE TYPE 命令創建。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

創建一周中的幾天，如下所示:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

就像其他類型一樣，一旦創建，枚舉類型可以用於表和函數定義。

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood 
------+--------------
 Moe  | happy
(1 row)

幾何類型

幾何數據類型表示二維的平面物體。

下表列出了 PostgreSQL 支持的幾何類型。

最基本的類型：點。它是其它類型的基礎。

網路地址類型

PostgreSQL 提供用於存儲 IPv4 、IPv6 、MAC 地址的數據類型。

用這些數據類型存儲網路地址比用純文本類型好， 因為這些類型提供輸入錯誤檢查和特殊的操作和功能。

在對 inet 或 cidr 數據類型進行排序的時候， IPv4 地址總是排在 IPv6 地址前面，包括那些封裝或者是映射在 IPv6 地址里的 IPv4 地址， 比如 ::10.2.3.4 或 ::ffff:10.4.3.2。

位串類型

位串就是一串 1 和 0 的字元串。它們可以用於存儲和直觀化位掩碼。 我們有兩種 SQL 位類型：bit(n) 和bit varying(n)， 這裡的n是一個正整數。

bit 類型的數據必須準確匹配長度 n， 試圖存儲短些或者長一些的數據都是錯誤的。bit varying 類型數據是最長 n 的變長類型；更長的串會被拒絕。 寫一個沒有長度的bit 等效於 bit(1)， 沒有長度的 bit varying 意思是沒有長度限制。

文本搜索類型

全文檢索即通過自然語言文檔的集合來找到那些匹配一個查詢的檢索。

PostgreSQL 提供了兩種數據類型用於支持全文檢索：

UUID 類型

uuid 數據類型用來存儲 RFC 4122，ISO/IEF 9834-8:2005 以及相關標准定義的通用唯一標識符（UUID）。 （一些系統認為這個數據類型為全球唯一標識符，或GUID。） 這個標識符是一個由演算法產生的 128 位標識符，使它不可能在已知使用相同演算法的模塊中和其他方式產生的標識符相同。 因此，對分散式系統而言，這種標識符比序列能更好的提供唯一性保證，因為序列只能在單一資料庫中保證唯一。

UUID 被寫成一個小寫十六進位數字的序列，由分字元分成幾組， 特別是一組8位數字+3組4位數字+一組12位數字，總共 32 個數字代表 128 位， 一個這種標準的 UUID 例子如下：

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

XML 類型

xml 數據類型可以用於存儲XML數據。 將 XML 數據存到 text 類型中的優勢在於它能夠為結構良好性來檢查輸入值， 並且還支持函數對其進行類型安全性檢查。 要使用這個數據類型，編譯時必須使用 configure --with-libxml。

xml 可以存儲由XML標准定義的格式良好的"文檔"， 以及由 XML 標準中的 XMLDecl? content 定義的"內容"片段， 大致上，這意味著內容片段可以有多個頂級元素或字元節點。 xmlvalue IS DOCUMENT 表達式可以用來判斷一個特定的 xml 值是一個完整的文件還是內容片段。

創建XML值

使用函數 xmlparse: 來從字元數據產生 xml 類型的值：

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

JSON 類型

json 數據類型可以用來存儲 JSON（JavaScript Object Notation）數據， 這樣的數據也可以存儲為 text，但是 json 數據類型更有利於檢查每個存儲的數值是可用的 JSON 值。

此外還有相關的函數來處理 json 數據：

數組類型

PostgreSQL 允許將欄位定義成變長的多維數組。

數組類型可以是任何基本類型或用戶定義類型，枚舉類型或複合類型。

聲明數組

創建表的時候，我們可以聲明數組，方式如下：

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

pay_by_quarter 為一維整型數組、schedule 為二維文本類型數組。

我們也可以使用 "ARRAY" 關鍵字，如下所示：

CREATE TABLE sal_emp (
   name text,
   pay_by_quarter integer ARRAY[4],
   schedule text[][]
);

插入值

插入值使用花括弧 {}，元素在 {} 使用逗號隔開：

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

訪問數組

現在我們可以在這個表上運行一些查詢。

首先，我們演示如何訪問數組的一個元素。 這個查詢檢索在第二季度薪水變化的雇員名：

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

數組的下標數字是寫在方括弧內的。

修改數組

我們可以對數組的值進行修改：

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

或者使用 ARRAY 構造器語法：

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

數組中檢索

要搜索一個數組中的數值，你必須檢查該數組的每一個值。

比如：

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

另外，你可以用下麵的語句找出數組中所有元素值都等於 10000 的行：

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

或者，可以使用 generate_subscripts 函數。例如：

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

複合類型

複合類型表示一行或者一條記錄的結構； 它實際上只是一個欄位名和它們的數據類型的列表。PostgreSQL 允許像簡單數據類型那樣使用複合類型。比如，一個表的某個欄位可以聲明為一個複合類型。

聲明複合類型

下麵是兩個定義複合類型的簡單例子：

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

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

語法類似於 CREATE TABLE，只是這裡只可以聲明欄位名字和類型。

定義了類型，我們就可以用它創建表：

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

複合類型值輸入

要以文本常量書寫複合類型值，在圓括弧里包圍欄位值並且用逗號分隔他們。 你可以在任何欄位值周圍放上雙引號，如果值本身包含逗號或者圓括弧， 你必須用雙引號括起。

複合類型常量的一般格式如下：

'( val1 , val2 , ... )'

一個例子是:

'("fuzzy dice",42,1.99)'

訪問複合類型

要訪問複合類型欄位的一個域，我們寫出一個點以及域的名字， 非常類似從一個表名字里選出一個欄位。實際上，因為實在太像從表名字中選取欄位， 所以我們經常需要用圓括弧來避免分析器混淆。比如，你可能需要從on_hand 例子表中選取一些子域，像下麵這樣：

SELECT item.name FROM on_hand WHERE item.price > 9.99;

這樣將不能工作，因為根據 SQL 語法，item是從一個表名字選取的， 而不是一個欄位名字。你必須像下麵這樣寫：

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

或者如果你也需要使用表名字(比如，在一個多表查詢里)，那麼這麼寫：

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

現在圓括弧對象正確地解析為一個指向item欄位的引用，然後就可以從中選取子域。

範圍類型

範圍數據類型代表著某一元素類型在一定範圍內的值。

例如，timestamp 範圍可能被用於代表一間會議室被預定的時間範圍。

PostgreSQL 內置的範圍類型有：

• int4range — integer的範圍
• int8range —bigint的範圍
• numrange —numeric的範圍
• tsrange —timestamp without time zone的範圍
• tstzrange —timestamp with time zone的範圍
• daterange —date的範圍

此外，你可以定義你自己的範圍類型。

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- 包含
SELECT int4range(10, 20) @> 3;

-- 重疊
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- 提取上邊界
SELECT upper(int8range(15, 25));

-- 計算交叉
SELECT int4range(10, 20) * int4range(15, 25);

-- 範圍是否為空
SELECT isempty(numrange(1, 5));

範圍值的輸入必須遵循下麵的格式：

(下邊界,上邊界)
(下邊界,上邊界]
[下邊界,上邊界)
[下邊界,上邊界]
空

圓括弧或者方括弧顯示下邊界和上邊界是不包含的還是包含的。註意最後的格式是 空，代表著一個空的範圍（一個不含有值的範圍）。

-- 包括3，不包括7，並且包括二者之間的所有點
SELECT '[3,7)'::int4range;

-- 不包括3和7，但是包括二者之間所有點
SELECT '(3,7)'::int4range;

-- 只包括單一值4
SELECT '[4,4]'::int4range;

-- 不包括點（被標準化為‘空’）
SELECT '[4,4)'::int4range;

對象標識符類型

PostgreSQL 在內部使用對象標識符(OID)作為各種系統表的主鍵。

同時，系統不會給用戶創建的表增加一個 OID 系統欄位(除非在建表時聲明瞭WITH OIDS 或者配置參數default_with_oids設置為開啟)。oid 類型代表一個對象標識符。除此以外 oid 還有幾個別名：regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig, 和regdictionary。

偽類型

PostgreSQL類型系統包含一系列特殊用途的條目， 它們按照類別來說叫做偽類型。偽類型不能作為欄位的數據類型， 但是它可以用於聲明一個函數的參數或者結果類型。 偽類型在一個函數不只是簡單地接受並返回某種SQL 數據類型的情況下很有用。

下表列出了所有的偽類型：