當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

PostgreSQL SQL 语言：数据定义

發布時間：2025/3/20 数据库 61 豆豆

生活随笔收集整理的這篇文章主要介紹了 PostgreSQL SQL 语言：数据定义小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

本文檔為PostgreSQL 9.6.0文檔，本轉載已得到原譯者彭煜瑋授權。

1.表基礎

關系型數據庫中的一個表非常像紙上的一張表：它由行和列組成。列的數量和順序是固定的，并且每一列擁有一個名字。行的數目是變化的，它反映了在一個給定時刻表中存儲的數據量。SQL并不保證表中行的順序。當一個表被讀取時，表中的行將以非特定順序出現，除非明確地指定需要排序。這些將在Chapter 7介紹。此外，SQL不會為行分配唯一的標識符，因此在一個表中可能會存在一些完全相同的行。這是SQL之下的數學模型導致的結果，但并不是所期望的。稍后在本章中我們將看到如何處理這種問題。

每一列都有一個數據類型。數據類型約束著一組可以分配給列的可能值，并且它為列中存儲的數據賦予了語義，這樣它可以用于計算。例如，一個被聲明為數字類型的列將不會接受任何文本串，而存儲在這樣一列中的數據可以用來進行數學計算。反過來，一個被聲明為字符串類型的列將接受幾乎任何一種的數據，它可以進行如字符串連接的操作但不允許進行數學計算。

PostgreSQL包括了相當多的內建數據類型，可以適用于很多應用。用戶也可以定義他們自己的數據類型。大部分內建數據類型有著顯而易見的名稱和語義，所以我們將它們的詳細解釋放在Chapter 8中。一些常用的數據類型是：用于整數的integer；可以用于分數的numeric；用于字符串的text，用于日期的date，用于一天內時間的time以及可以同時包含日期和時間的timestamp。

要創建一個表，我們要用到CREATE TABLE命令。在這個命令中我們需要為新表至少指定一個名字、列的名字及數據類型。例如：

CREATE TABLE my_first_table (first_column text,second_column integer );

這將創建一個名為my_first_table的表，它擁有兩個列。第一個列名為first_column且數據類型為text；第二個列名為second_column且數據類型為integer。表和列的名字遵循Section 4.1.1中解釋的標識符語法。類型名稱通常也是標識符，但是也有些例外。注意列的列表由逗號分隔并被圓括號包圍。

當然，前面的例子是非常不自然的。通常，我們為表和列賦予的名稱都會表明它們存儲著什么類別的數據。因此讓我們再看一個更現實的例子：

CREATE TABLE products (product_no integer,name text,price numeric ); （numeric類型能夠存儲小數部分，典型的例子是金額。）

Tip: 當我們創建很多相關的表時，最好為表和列選擇一致的命名模式。例如，一種選擇是用單數或復數名詞作為表名，每一種都受到一些理論家支持。

一個表能夠擁有的列的數據是有限的，根據列的類型，這個限制介于250和1600之間。但是，極少會定義一個接近這個限制的表，即便有也是一個值的商榷的設計。

如果我們不再需要一個表，我們可以通過使用DROP TABLE命令來移除它。例如：

DROP TABLE my_first_table; DROP TABLE products;

嘗試移除一個不存在的表會引起錯誤。然而，在SQL腳本中在創建每個表之前無條件地嘗試移除它的做法是很常見的，即使發生錯誤也會忽略之，因此這樣的腳本可以在表存在和不存在時都工作得很好（如果你喜歡，可以使用DROP TABLE IF EXISTS變體來防止出現錯誤消息，但這并非標準SQL）。

如果我們需要修改一個已經存在的表，請參考本章稍后的Section 5。

利用到目前為止所討論的工具，我們可以創建一個全功能的表。本章的后續部分將集中于為表定義增加特性來保證數據完整性、安全性或方便。如果你希望現在就去填充你的表，你可以跳過這些直接去下章。

2.默認值

一個列可以被分配一個默認值。當一個新行被創建且沒有為某些列指定值時，這些列將會被它們相應的默認值填充。一個數據操縱命令也可以顯式地要求一個列被置為它的默認值，而不需要知道這個值到底是什么。

如果沒有顯式指定默認值，則默認值是空值。這是合理的，因為空值表示未知數據。

在一個表定義中，默認值被列在列的數據類型之后。例如：

CREATE TABLE products (product_no integer,name text,price numeric DEFAULT 9.99 );

默認值可以是一個表達式，它將在任何需要插入默認值的時候被實時計算（不是表創建時）。一個常見的例子是為一個timestamp列指定默認值為CURRENT_TIMESTAMP，這樣它將得到行被插入時的時間。另一個常見的例子是為每一行生成一個"序列號" 。這在PostgreSQL可以按照如下方式實現：

CREATE TABLE products (product_no integer DEFAULT nextval('products_product_no_seq'),... );

這里nextval()函數從一個序列對象Section 9.16）。還有一種特別的速寫：

CREATE TABLE products (product_no SERIAL,... );

SERIAL速寫將在后面文章進一步討論。

3. 約束

數據類型是一種限制能夠存儲在表中數據類別的方法。但是對于很多應用來說，它們提供的約束太粗糙。例如，一個包含產品價格的列應該只接受正值。但是沒有任何一種標準數據類型只接受正值。另一個問題是我們可能需要根據其他列或行來約束一個列中的數據。例如，在一個包含產品信息的表中，對于每個產品編號應該只有一行。

到目前為止，SQL允許我們在列和表上定義約束。約束讓我們能夠根據我們的愿望來控制表中的數據。如果一個用戶試圖在一個列中保存違反一個約束的數據，一個錯誤會被拋出。即便是這個值來自于默認值定義，這個規則也同樣適用。

3.1. 檢查約束

一個檢查約束是最普通的約束類型。它允許我們指定一個特定列中的值必須要滿足一個布爾表達式。例如，為了要求正值的產品價格，我們可以使用：

CREATE TABLE products (product_no integer,name text,price numeric CHECK (price > 0) );

如你所見，約束定義就和默認值定義一樣跟在數據類型之后。默認值和約束之間的順序沒有影響。一個檢查約束有關鍵字CHECK以及其后的包圍在圓括號中的表達式組成。檢查約束表達式應該涉及到被約束的列，否則該約束也沒什么實際意義。

我們也可以給與約束一個獨立的名稱。這會使得錯誤消息更為清晰，同時也允許我們在需要更改約束時能引用它。語法為：

CREATE TABLE products (product_no integer,name text,price numeric CONSTRAINT positive_price CHECK (price > 0) );

要指定一個命名的約束，請在約束名稱標識符前使用關鍵詞CONSTRAINT，然后把約束定義放在標識符之后（如果沒有以這種方式指定一個約束名稱，系統將會為我們選擇一個）。

一個檢查約束也可以引用多個列。例如我們存儲一個普通價格和一個打折后的價格，而我們希望保證打折后的價格低于普通價格：

CREATE TABLE products (product_no integer,name text,price numeric CHECK (price > 0),discounted_price numeric CHECK (discounted_price > 0),CHECK (price > discounted_price) );

前兩個約束看起來很相似。第三個則使用了一種新語法。它并沒有依附在一個特定的列，而是作為一個獨立的項出現在逗號分隔的列列表中。列定義和這種約束定義可以以混合的順序出現在列表中。

我們將前兩個約束稱為列約束，而第三個約束為表約束，因為它獨立于任何一個列定義。列約束也可以寫成表約束，但反過來不行，因為一個列約束只能引用它所依附的那一個列（PostgreSQL并不強制要求這個規則，但是如果我們希望表定義能夠在其他數據庫系統中工作，那就應該遵循它）。上述例子也可以寫成：

CREATE TABLE products (product_no integer,name text,price numeric,CHECK (price > 0),discounted_price numeric,CHECK (discounted_price > 0),CHECK (price > discounted_price) );

甚至是：

CREATE TABLE products (product_no integer,name text,price numeric CHECK (price > 0),discounted_price numeric,CHECK (discounted_price > 0 AND price > discounted_price) );

這只是口味的問題。

表約束也可以用列約束相同的方法來指定名稱：

CREATE TABLE products (product_no integer,name text,price numeric,CHECK (price > 0),discounted_price numeric,CHECK (discounted_price > 0),CONSTRAINT valid_discount CHECK (price > discounted_price) );

需要注意的是，一個檢查約束在其檢查表達式值為真或空值時被滿足。因為當任何操作數為空時大部分表達式將計算為空值，所以它們不會阻止被約束列中的控制。為了保證一個列不包含控制，可以使用下一節中的非空約束。

3.2. 非空約束

一個非空約束僅僅指定一個列中不會有空值。語法例子：

CREATE TABLE products (product_no integer NOT NULL,name text NOT NULL,price numeric );

一個非空約束總是被寫成一個列約束。一個非空約束等價于創建一個檢查約束CHECK (column_name IS NOT NULL)，但在PostgreSQL中創建一個顯式的非空約束更高效。這種方式創建的非空約束的缺點是我們無法為它給予一個顯式的名稱。

當然，一個列可以有多于一個的約束，只需要將這些約束一個接一個寫出：

CREATE TABLE products (product_no integer NOT NULL,name text NOT NULL,price numeric NOT NULL CHECK (price > 0) );

約束的順序沒有關系，因為并不需要決定約束被檢查的順序。

NOT NULL約束有一個相反的情況：NULL約束。這并不意味著該列必須為空，進而肯定是無用的。相反，它僅僅選擇了列可能為空的默認行為。SQL標準中并不存在NULL約束，因此它不能被用于可移植的應用中（PostgreSQL中加入它是為了和某些其他數據庫系統兼容）。但是某些用戶喜歡它，因為它使得在一個腳本文件中可以很容易的進行約束切換。例如，初始時我們可以：

CREATE TABLE products (product_no integer NULL,name text NULL,price numeric NULL );

然后可以在需要的地方插入NOT關鍵詞。

Tip: 在大部分數據庫中多數列應該被標記為非空。

3.3. 唯一約束

唯一約束保證在一列中或者一組列中保存的數據在表中所有行間是唯一的。寫成一個列約束的語法是：

CREATE TABLE products (product_no integer UNIQUE,name text,price numeric );

寫成一個表約束的語法是：

CREATE TABLE products (product_no integer,name text,price numeric,UNIQUE (product_no) );

要為一組列定義一個唯一約束，把它寫作一個表級約束，列名用逗號分隔：

CREATE TABLE example (a integer,b integer,c integer,UNIQUE (a, c) );

這指定這些列的組合值在整個表的范圍內是唯一的，但其中任意一列的值并不需要是（一般也不是）唯一的。

我們可以通常的方式為一個唯一索引命名：

CREATE TABLE products (product_no integer CONSTRAINT must_be_different UNIQUE,name text,price numeric );

增加一個唯一約束會在約束中列出的列或列組上自動創建一個唯一B-tree索引。只覆蓋某些行的唯一性限制不能被寫為一個唯一約束，但可以通過創建一個唯一的部分索引來強制這種限制。

通常，如果表中有超過一行在約束所包括列上的值相同，將會違反唯一約束。但是在這種比較中，兩個空值被認為是不同的。這意味著即便存在一個唯一約束，也可以存儲多個在至少一個被約束列中包含空值的行。這種行為符合SQL標準，但我們聽說一些其他SQL數據庫可能不遵循這個規則。所以在開發需要可移植的應用時應注意這一點。

3.4. 主鍵

一個主鍵約束表示可以用作表中行的唯一標識符的一個列或者一組列。這要求那些值都是唯一的并且非空。因此，下面的兩個表定義接受相同的數據：

CREATE TABLE products (product_no integer UNIQUE NOT NULL,name text,price numeric ); CREATE TABLE products (product_no integer PRIMARY KEY,name text,price numeric );

主鍵也可以包含多于一個列，其語法和唯一約束相似：

CREATE TABLE example (a integer,b integer,c integer,PRIMARY KEY (a, c) );

增加一個主鍵將自動在主鍵中列出的列或列組上創建一個唯一B-tree索引。并且會強制這些列被標記為NOT NULL。

一個表最多只能有一個主鍵（可以有任意數量的唯一和非空約束，它們可以達到和主鍵幾乎一樣的功能，但只能有一個被標識為主鍵）。關系數據庫理論要求每一個表都要有一個主鍵。但PostgreSQL中并未強制要求這一點，但是最好能夠遵循它。

主鍵對于文檔和客戶端應用都是有用的。例如，一個允許修改行值的 GUI 應用可能需要知道一個表的主鍵，以便能唯一地標識行。如果定義了主鍵，數據庫系統也有多種方法來利用主鍵。例如，主鍵定義了外鍵要引用的默認目標列。

3.5. 外鍵

一個外鍵約束指定一列（或一組列）中的值必須匹配出現在另一個表中某些行的值。我們說這維持了兩個關聯表之間的引用完整性。

例如我們有一個使用過多次的產品表：

CREATE TABLE products (product_no integer PRIMARY KEY,name text,price numeric );

讓我們假設我們還有一個存儲這些產品訂單的表。我們希望保證訂單表中只包含真正存在的產品的訂單。因此我們在訂單表中定義一個引用產品表的外鍵約束：

CREATE TABLE orders (order_id integer PRIMARY KEY,product_no integer REFERENCES products (product_no),quantity integer );

現在就不可能創建包含不存在于產品表中的product_no值（非空）的訂單。

我們說在這種情況下，訂單表是引用表而產品表是被引用表。相應地，也有引用和被引用列的說法。

我們也可以把上述命令簡寫為：

CREATE TABLE orders (order_id integer PRIMARY KEY,product_no integer REFERENCES products,quantity integer );

因為如果缺少列的列表，則被引用表的主鍵將被用作被引用列。

一個外鍵也可以約束和引用一組列。照例，它需要被寫成表約束的形式。下面是一個例子：

CREATE TABLE t1 (a integer PRIMARY KEY,b integer,c integer,FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );

當然，被約束列的數量和類型應該匹配被引用列的數量和類型。

按照前面的方式，我們可以為一個外鍵約束命名。

一個表可以有超過一個的外鍵約束。這被用于實現表之間的多對多關系。例如我們有關于產品和訂單的表，但我們現在希望一個訂單能包含多種產品（這在上面的結構中是不允許的）。我們可以使用這種表結構：

CREATE TABLE products (product_no integer PRIMARY KEY,name text,price numeric );CREATE TABLE orders (order_id integer PRIMARY KEY,shipping_address text,... );CREATE TABLE order_items (product_no integer REFERENCES products,order_id integer REFERENCES orders,quantity integer,PRIMARY KEY (product_no, order_id) );

注意在最后一個表中主鍵和外鍵之間有重疊。

我們知道外鍵不允許創建與任何產品都不相關的訂單。但如果一個產品在一個引用它的訂單創建之后被移除會發生什么？SQL允許我們處理這種情況。直觀上，我們有幾種選項：

不允許刪除一個被引用的產品

同時也刪除引用產品的訂單

其他？

為了說明這些，讓我們在上面的多對多關系例子中實現下面的策略：當某人希望移除一個仍然被一個訂單引用（通過order_items）的產品時，我們組織它。如果某人移除一個訂單，訂單項也同時被移除：

CREATE TABLE products (product_no integer PRIMARY KEY,name text,price numeric );CREATE TABLE orders (order_id integer PRIMARY KEY,shipping_address text,... );CREATE TABLE order_items (product_no integer REFERENCES products ON DELETE RESTRICT,order_id integer REFERENCES orders ON DELETE CASCADE,quantity integer,PRIMARY KEY (product_no, order_id) );

限制刪除或者級聯刪除是兩種最常見的選項。RESTRICT阻止刪除一個被引用的行。NO ACTION表示在約束被檢察時如果有任何引用行存在，則會拋出一個錯誤，這是我們沒有指定任何東西時的默認行為（這兩種選擇的本質不同在于NO ACTION允許檢查被推遲到事務的最后，而RESTRICT則不會）。CASCADE指定當一個被引用行被刪除后，引用它的行也應該被自動刪除。還有其他兩種選項：SET NULL和SET DEFAULT。這些將導致在被引用行被刪除后，引用行中的引用列被置為空值或它們的默認值。注意這些并不會是我們免于遵守任何約束。例如，如果一個動作指定了SET DEFAULT，但是默認值不滿足外鍵約束，操作將會失敗。

與ON DELETE相似，同樣有ON UPDATE可以用在一個被引用列被修改（更新）的情況，可選的動作相同。在這種情況下，CASCADE意味著被引用列的更新值應該被復制到引用行中。

正常情況下，如果一個引用行的任意一個引用列都為空，則它不需要滿足外鍵約束。如果在外鍵定義中加入了MATCH FULL，一個引用行只有在它的所有引用列為空時才不需要滿足外鍵約束（因此空和非空值的混合肯定會導致MATCH FULL約束失敗）。如果不希望引用行能夠避開外鍵約束，將引用行聲明為NOT NULL。

一個外鍵所引用的列必須是一個主鍵或者被唯一約束所限制。這意味著被引用列總是擁有一個索引（位于主鍵或唯一約束之下的索引），因此在其上進行的一個引用行是否匹配的檢查將會很高效。由于從被引用表中DELETE一行或者UPDATE一個被引用列將要求對引用表進行掃描以得到匹配舊值的行，在引用列上建立合適的索引也會大有益處。由于這種做法并不是必須的，而且創建索引也有很多種選擇，所以外鍵約束的定義并不會自動在引用列上創建索引。

更多關于更新和刪除數據的信息請見Chapter 6。外鍵約束的語法描述請參考CREATE TABLE。

3.6. 排他約束

排他約束保證如果將任何兩行的指定列或表達式使用指定操作符進行比較，至少其中一個操作符比較將會返回否或空值。語法是：

CREATE TABLE circles (c circle,EXCLUDE USING gist (c WITH &&) );

增加一個排他約束將在約束聲明所指定的類型上自動創建索引。

4. 系統列

每一個表都擁有一些由系統隱式定義的系統列。因此，這些列的名字不能像用戶定義的列一樣使用（注意這種限制與名稱是否為關鍵詞沒有關系，即便用引號限定一個名稱也無法繞過這種限制）。事實上用戶不需要關心這些列，只需要知道它們存在即可。

oid

一行的對象標識符（對象ID）。該列只有在表使用WITH OIDS創建時或者default_with_oids配置變量被設置時才存在。該列的類型為oid（與列名一致）。

tableoid

包含這一行的表的OID。該列是特別為從繼承層次中選擇的查詢而準備，因為如果沒有它將很難知道一行來自于哪個表。tableoid可以與pg_class的oid列進行連接來獲得表的名稱。

xmin

插入該行版本的事務身份（事務ID）。一個行版本是一個行的一個特別版本，對一個邏輯行的每一次更新都將創建一個新的行版本。

cmin

插入事務中的命令標識符（從0開始）。

xmax

刪除事務的身份（事務ID），對于未刪除的行版本為0。對于一個可見的行版本，該列值也可能為非零。這通常表示刪除事務還沒有提交，或者一個刪除嘗試被回滾。

cmax

刪除事務中的命令標識符，或者為0。

ctid

行版本在其表中的物理位置。注意盡管ctid可以被用來非常快速地定位行版本，但是一個行的ctid會在被更新或者被VACUUM FULL移動時改變。因此，ctid不能作為一個長期行標識符。OID或者最好是一個用戶定義的序列號才應該被用來標識邏輯行。

OID是32位量，它從一個服務于整個集簇的計數器分配而來。在一個大型的或者歷時長久的數據庫中，該計數器有可能會出現繞回。因此，不要總是假設OID是唯一的，除非你采取了措施來保證。如果需要在一個表中標識行，推薦使用一個序列生成器。然而，OID也可以被使用，但是是要采取一些額外的預防措施：

如果要將OID用來標識行，應該在OID列上創建一個唯一約束。當這樣一個唯一約束（或唯一索引）存在時，系統會注意不生成匹配現有行的OID（當然，這只有在表的航數目少于2^32（40億）時才成立。并且在實踐中表的尺寸最好遠比這個值小，否則將會犧牲性能）。
絕不要認為OID在表之間也是唯一的，使用tableoid和行OID的組合來作為數據庫范圍內的標識符。
當然，問題中的表都必須是用WITH OIDS創建。在PostgreSQL 8.1中，WITHOUT OIDS是默認形式。

事務標識符也是32位量。在一個歷時長久的數據庫中事務ID同樣會繞回。但如果采取適當的維護過程，這不會是一個致命的問題，詳見Chapter 24。但是，長期（超過10億個事務）依賴事務ID的唯一性是不明智的。

命令標識符也是32位量。這對一個事務中包含的SQL命令設置了一個硬極限： 2^32（40億）（40億）。在實踐中，該限制并不是問題 — 注意該限制只是針對SQL命令的數目而不是被處理的行數。同樣，只有真正修改了數據庫內容的命令才會消耗一個命令標識符。

5. 修改表

當我們已經創建了一個表并意識到犯了一個錯誤或者應用需求發生改變時，我們可以移除表并重新創建它。但如果表中已經被填充數據或者被其他數據庫對象引用（例如有一個外鍵約束），這種做法就顯得很不方便。因此，PostgreSQL提供了一族命令來對已有的表進行修改。注意這和修改表中所包含的數據是不同的，這里要做的是對表的定義或者說結構進行修改。

利用這些命令，我們可以：

增加列
移除列
增加約束
移除約束
修改默認值
修改列數據類型
重命名列
重命名表

所有這些動作都由ALTER TABLE命令執行，其參考頁面中包含更詳細的信息。

5.1. 增加列

要增加一個列，可以使用這樣的命令：

ALTER TABLE products ADD COLUMN description text;

新列將被默認值所填充（如果沒有指定DEFAULT子句，則會填充空值）。

也可以同時為列定義約束，語法：

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

事實上CREATE TABLE中關于一列的描述都可以應用在這里。記住不管怎樣，默認值必須滿足給定的約束，否則ADD將會失敗。也可以先將新列正確地填充好，然后再增加約束（見后文）。

Tip: 增加一個帶默認值的列需要更新表中的每一行（來存儲新列值）。然而，如果不指定默認值，PostgreSQL可以避免物理更新。因此如果我們準備向列中填充的值大多是非默認值，最好是增加列的時候不指定默認值，增加列后用UPDATE填充正確的數據并且增加所需要的默認值約束。

5.2. 移除列

為了移除一個列，使用如下的命令：

ALTER TABLE products DROP COLUMN description;

列中的數據將會消失。涉及到該列的表約束也會被移除。然而，如果該列被另一個表的外鍵所引用，PostgreSQL不會安靜地移除該約束。我們可以通過增加CASCADE來授權移除任何依賴于被刪除列的所有東西：

ALTER TABLE products DROP COLUMN description CASCADE;

關于這個操作背后的一般性機制請見Section 5.13。

5.3. 增加約束

為了增加一個約束，可以使用表約束的語法，例如：

ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;

要增加一個不能寫成表約束的非空約束，可使用語法：

ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

該約束會立即被檢查，所以表中的數據必須在約束被增加之前就已經符合約束。

5.4. 移除約束

為了移除一個約束首先需要知道它的名稱。如果在創建時已經給它指定了名稱，那么事情就變得很容易。否則約束的名稱是由系統生成的，我們必須先找出這個名稱。psql的命令d 表名將會對此有所幫助，其他接口也會提供方法來查看表的細節。因此命令是：

ALTER TABLE products DROP CONSTRAINT some_name;

（如果處理的是自動生成的約束名稱，如$2，別忘了用雙引號使它變成一個合法的標識符。）

和移除一個列相似，如果需要移除一個被某些別的東西依賴的約束，也需要加上CASCADE。一個例子是一個外鍵約束依賴于被引用列上的一個唯一或者主鍵約束。

這對除了非空約束之外的所有約束類型都一樣有效。為了移除一個非空約束可以用：

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

（回憶一下，非空約束是沒有名稱的，所以不能用第一種方式。）

5.5. 更改列的默認值

要為一個列設置一個新默認值，使用命令：

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

注意這不會影響任何表中已經存在的行，它只是為未來的INSERT命令改變了默認值。

要移除任何默認值，使用：

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

這等同于將默認值設置為空值。相應的，試圖刪除一個未被定義的默認值并不會引發錯誤，因為默認值已經被隱式地設置為空值。

5.6. 修改列的數據類型

為了將一個列轉換為一種不同的數據類型，使用如下命令：

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

只有當列中的每一個項都能通過一個隱式造型轉換為新的類型時該操作才能成功。如果需要一種更復雜的轉換，應該加上一個USING子句來指定應該如何把舊值轉換為新值。

PostgreSQL將嘗試把列的默認值轉換為新類型，其他涉及到該列的任何約束也是一樣。但是這些轉換可能失敗或者產生奇特的結果。因此最好在修改類型之前先刪除該列上所有的約束，然后在修改完類型后重新加上相應修改過的約束。

5.7. 重命名列

要重命名一個列：

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.8. 重命名表

要重命名一個表：

ALTER TABLE products RENAME TO items;

6. 權限

一旦一個對象被創建，它會被分配一個所有者。所有者通常是執行創建語句的角色。對于大部分類型的對象，初始狀態下只有所有者（或者超級用戶）能夠對該對象做任何事情。為了允許其他角色使用它，必須分配權限。

有多種不同的權限：SELECT、INSERT、UPDATE、DELETE、TRUNCATE、REFERENCES、TRIGGER、CREATE、CONNECT、TEMPORARY、EXECUTE以及USAGE。可以應用于一個特定對象的權限隨著對象的類型（表、函數等）而不同。PostgreSQL所支持的不同類型的完整權限信息請參考GRANT。下面的章節將簡單介紹如何使用這些權限。

修改或銷毀一個對象的權力通常是只有所有者才有的權限。

一個對象可以通過該對象類型相應的ALTER命令來重新分配所有者，例如ALTER TABLE。超級用戶總是可以做到這點，普通角色只有同時是對象的當前所有者（或者是擁有角色的一個成員）以及新擁有角色的一個成員時才能做同樣的事。

要分配權限，可以使用GRANT命令。例如，如果joe是一個已有角色，而accounts是一個已有表，更新該表的權限可以按如下方式授權：

GRANT UPDATE ON accounts TO joe;

用ALL取代特定權限會把與對象類型相關的所有權限全部授權。

一個特殊的名為PUBLIC的"角色"可以用來向系統中的每一個角色授予一個權限。同時，在數據庫中有很多用戶時可以設置"組"角色來幫助管理權限。

為了撤銷一個權限，使用REVOKE命令：

REVOKE ALL ON accounts FROM PUBLIC;

對象擁有者的特殊權限（即執行DROP、GRANT、REVOKE等的權力）總是隱式地屬于擁有者，并且不能被授予或撤銷。但是對象擁有者可以選擇撤銷他們自己的普通權限，例如把一個表變得對他們自己和其他人只讀。

一般情況下，只有對象擁有者（或者超級用戶）可以授予或撤銷一個對象上的權限。但是可以在授予權限時使用"with grant option"來允許接收人將權限轉授給其他人。如果后來授予選項被撤銷，則所有從接收人那里獲得的權限（直接或者通過授權鏈獲得）都將被撤銷。

7. 行安全性策略

除可以通過GRANT使用 SQL 標準的特權系統之外，表還可以具有行安全性策略，它針對每一個用戶限制哪些行可以被普通的查詢返回或者可以被數據修改命令插入、更新或刪除。這種特性也被稱為行級安全性。默認情況下，表不具有任何策略，這樣用戶根據 SQL 特權系統具有對表的訪問特權，對于查詢或更新來說其中所有的行都是平等的。

當在一個表上啟用行安全性時（使用 ALTER TABLE ... ENABLE ROW LEVEL SECURITY），所有對該表選擇行或者修改行的普通訪問都必須被一條行安全性策略所允許（不過，表的擁有者通常不服從行安全性策略）。如果表上不存在策略，將使用一條默認的否定策略，即所有的行都不可見或者不能被修改。應用在整個表上的操作不服從行安全性，例如TRUNCATE和 REFERENCES。

行安全性策略可以針對特定的命令、角色或者兩者。一條策略可以被指定為適用于ALL命令，或者SELECT、 INSERT、UPDATE或者DELETE。可以為一條給定策略分配多個角色，并且通常的角色成員關系和繼承規則也適用。

要指定哪些行根據一條策略是可見的或者是可修改的，需要一個返回布爾結果的表達式。對于每一行，在計算任何來自用戶查詢的條件或函數之前，先會計算這個表達式（這條規則的唯一例外是leakproof函數，它們被保證不會泄露信息，優化器可能會選擇在行安全性檢查之前應用這類函數）。使該表達式不返回true的行將不會被處理。可以指定獨立的表達式來單獨控制哪些行可見以及哪些行被允許修改。策略表達式會作為查詢的一部分運行并且帶有運行該查詢的用戶的特權，但是安全性定義者函數可以被用來訪問對調用用戶不可用的數據。

具有BYPASSRLS屬性的超級用戶和角色在訪問一個表時總是可以繞過行安全性系統。表擁有者通常也能繞過行安全性，不過表擁有者可以選擇用ALTER TABLE ... FORCE ROW LEVEL SECURITY來服從行安全性。

啟用和禁用行安全性以及向表增加策略是只有表擁有者具有的特權。

策略的創建可以使用CREATE POLICY命令，策略的修改可以使用ALTER POLICY命令，而策略的刪除可以使用 DROP POLICY命令。要為一個給定表啟用或者禁用行安全性，可以使用ALTER TABLE命令。

每一條策略都有名稱并且可以為一個表定義多條策略。由于策略是表相關的，一個表的每一條策略都必須有一個唯一的名稱。不同的表可以擁有相同名稱的策略。

當多條策略適用于一個給定查詢時，它們會被用OR 組合起來，這樣只要任一策略允許，行就是可訪問的。這類似于一個給定角色具有它所屬的所有角色的特權的規則。

作為一個簡單的例子，這里是如何在account關系上創建一條策略以允許只有managers角色的成員能訪問行，并且只能訪問它們賬戶的行：

CREATE TABLE accounts (manager text, company text, contact_email text);ALTER TABLE accounts ENABLE ROW LEVEL SECURITY;CREATE POLICY account_managers ON accounts TO managersUSING (manager = current_user);

如果沒有指定角色或者使用了特殊的用戶名PUBLIC，則該策略適用于系統上所有的用戶。要允許所有用戶訪問users 表中屬于他們自己的行，可以使用一條簡單的策略：

CREATE POLICY user_policy ON usersUSING (user_name = current_user);

要對相對于可見行是被增加到表中的行使用一條不同的策略，可以使用 WITH CHECK子句。這條策略將允許所有用戶查看 users表中的所有行，但是只能修改它們自己的行：

CREATE POLICY user_policy ON usersUSING (true)WITH CHECK (user_name = current_user);

也可以用ALTER TABLE命令禁用行安全性。禁用行安全性不會移除定義在表上的任何策略，它們只是被簡單地忽略。然后該表中的所有行都是可見的并且可修改，服從于標準的 SQL 特權系統。

下面是一個較大的例子，它展示了這種特性如何被用于生產環境。表 passwd模擬了一個 Unix 口令文件：

-- 簡單的口令文件例子 CREATE TABLE passwd (user_name text UNIQUE NOT NULL,pwhash text,uid int PRIMARY KEY,gid int NOT NULL,real_name text NOT NULL,home_phone text,extra_info text,home_dir text NOT NULL,shell text NOT NULL );CREATE ROLE admin; -- 管理員 CREATE ROLE bob; -- 普通用戶 CREATE ROLE alice; -- 普通用戶-- 填充表 INSERT INTO passwd VALUES('admin','xxx',0,0,'Admin','111-222-3333',null,'/root','/bin/dash'); INSERT INTO passwd VALUES('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh'); INSERT INTO passwd VALUES('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');-- 確保在表上啟用行級安全性 ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;-- 創建策略 -- 管理員能看見所有行并且增加任意行 CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true); -- 普通用戶可以看見所有行 CREATE POLICY all_view ON passwd FOR SELECT USING (true); -- 普通用戶可以更新它們自己的記錄，但是限制普通用戶可用的 shell CREATE POLICY user_mod ON passwd FOR UPDATEUSING (current_user = user_name)WITH CHECK (current_user = username ANDshell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh'));-- 允許管理員有所有普通權限 GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin; -- 用戶只在公共列上得到選擇訪問 GRANT SELECT(user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)ON passwd TO public; -- 允許用戶更新特定行 GRANT UPDATE(pwhash, real_name, home_phone, extra_info, shell)ON passwd TO public;

對于任意安全性設置來說，重要的是測試并確保系統的行為符合預期。使用上述的例子，下面展示了權限系統工作正確：

-- admin 可以看到所有的行和域 postgres=> set role admin; SET postgres=> table passwd;user_name | pwhash | uid | gid | real_name | home_phone | extra_info | home_dir | shell -----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------admin | xxx | 0 | 0 | Admin | 111-222-3333 | | /root | /bin/dashbob | xxx | 1 | 1 | Bob | 123-456-7890 | | /home/bob | /bin/zshalice | xxx | 2 | 1 | Alice | 098-765-4321 | | /home/alice | /bin/zsh (3 rows)-- 測試 Alice 能做什么 postgres=> set role alice; SET postgres=> table passwd; ERROR: permission denied for relation passwd postgres=> select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;user_name | real_name | home_phone | extra_info | home_dir | shell -----------+-----------+--------------+------------+-------------+-----------admin | Admin | 111-222-3333 | | /root | /bin/dashbob | Bob | 123-456-7890 | | /home/bob | /bin/zshalice | Alice | 098-765-4321 | | /home/alice | /bin/zsh (3 rows)postgres=> update passwd set user_name = 'joe'; ERROR: permission denied for relation passwd -- Alice 被允許更改她自己的 real_name，但不能改其他的 postgres=> update passwd set real_name = 'Alice Doe'; UPDATE 1 postgres=> update passwd set real_name = 'John Doe' where user_name = 'admin'; UPDATE 0 postgres=> update passwd set shell = '/bin/xx'; ERROR: new row violates WITH CHECK OPTION for "passwd" postgres=> delete from passwd; ERROR: permission denied for relation passwd postgres=> insert into passwd (user_name) values ('xxx'); ERROR: permission denied for relation passwd -- Alice 可以更改她自己的口令；行級安全性會悄悄地阻止更新其他行 postgres=> update passwd set pwhash = 'abc'; UPDATE 1

參照完整性檢查（例如唯一或逐漸約束和外鍵引用）總是會繞過行級安全性以保證數據完整性得到維護。在開發模式和行級安全性時必須小心避免 "隱通道"通過這類參照完整性檢查泄露信息。

在某些環境中確保行安全性沒有被應用很重要。例如，在做備份時，如果行安全性悄悄地導致某些行被從備份中忽略掉，這會是災難性的。在這類情況下，你可以設置row_security配置參數為 off。這本身不會繞過行安全性，它所做的是如果任何結果會被一條策略過濾掉，就會拋出一個錯誤。然后錯誤的原因就可以被找到并且修復。

在上面的例子中，策略表達式只考慮了要被訪問的行中的當前值。這是最簡單并且表現最好的情況。如果可能，最好設計行安全性應用以這種方式工作。如果需要參考其他行或者其他表來做出策略的決定，可以在策略表達式中通過使用子-SELECT或者包含SELECT的函數來實現。不過要注意這類訪問可能會導致競爭條件，在不小心的情況下這可能會導致信息泄露。作為一個例子，考慮下面的表設計：

-- 特權組的定義 CREATE TABLE groups (group_id int PRIMARY KEY,group_name text NOT NULL);INSERT INTO groups VALUES(1, 'low'),(2, 'medium'),(5, 'high');GRANT ALL ON groups TO alice; -- alice 是管理員 GRANT SELECT ON groups TO public;-- 用戶的特權級別的定義 CREATE TABLE users (user_name text PRIMARY KEY,group_id int NOT NULL REFERENCES groups);INSERT INTO users VALUES('alice', 5),('bob', 2),('mallory', 2);GRANT ALL ON users TO alice; GRANT SELECT ON users TO public;-- 保存要被保護的信息的表 CREATE TABLE information (info text,group_id int NOT NULL REFERENCES groups);INSERT INTO information VALUES('barely secret', 1),('slightly secret', 2),('very secret', 5);ALTER TABLE information ENABLE ROW LEVEL SECURITY;-- 對于安全性 group_id 大于等于一行的 group_id 的用戶， -- 這一行應該是可見的/可更新的 CREATE POLICY fp_s ON information FOR SELECTUSING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user)); CREATE POLICY fp_u ON information FOR UPDATEUSING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));-- 我們只依賴于行級安全性來保護信息表 GRANT ALL ON information TO public;

現在假設alice希望更改"有一點點秘密" 的信息，但是覺得mallory不應該看到該行中的新內容，因此她這樣做：

BEGIN; UPDATE users SET group_id = 1 WHERE user_name = 'mallory'; UPDATE information SET info = 'secret from mallory' WHERE group_id = 2; COMMIT;

這看起來是安全的，沒有窗口可供mallory看到 "對 mallory 保密"的字符串。不過，這里有一種競爭條件。如果mallory正在并行地做：

SELECT * FROM information WHERE group_id = 2 FOR UPDATE;

并且她的事務處于READ COMMITTED模式，她就可能看到 "s對 mallory 保密"的東西。如果她的事務在alice 做完之后就到達信息行，這就會發生。它會阻塞等待 alice的事務提交，然后拜FOR UPDATE子句所賜取得更新后的行內容。不過，對于來自users的隱式 SELECT，它不會取得一個已更新的行，因為子-SELECT沒有FOR UPDATE，相反會使用查詢開始時取得的快照讀取users行。因此，策略表達式會測試mallory的特權級別的舊值并且允許她看到被更新的行。

有多種方法能解決這個問題。一種簡單的答案是在行安全性策略中的子-SELECT里使用SELECT ... FOR SHARE。不過，這要求在被引用表（這里是users）上授予 UPDATE特權給受影響的用戶，這可能不是我們想要的（但是另一條行安全性策略可能被應用來阻止它們實際使用這個特權，或者子-SELECT可能被嵌入到一個安全性定義者函數中）。還有，在被引用的表上過多并發地使用行共享鎖可能會導致性能問題，特別是表更新比較頻繁時。另一種解決方案（如果被引用表上的更新不頻繁就可行）是在更新被引用表時對它取一個排他鎖，這樣就沒有并發事務能夠檢查舊的行值了。或者我們可以在提交對被引用表的更新之后、在做依賴于新安全性情況的更改之前等待所有并發事務結束。

8. 模式

一個PostgreSQL數據庫集簇中包含一個或更多命名的數據庫。用戶和用戶組被整個集簇共享，但沒有其他數據在數據庫之間共享。任何給定客戶端連接只能訪問在連接中指定的數據庫中的數據。

Note:
一個集簇的用戶并不必擁有訪問集簇中每一個數據庫的權限。用戶名的共享意味著不可能在同一個集簇中出現重名的不同用戶，例如兩個數據庫中都有叫joe的用戶。但系統可以被配置為只允許joe訪問某些數據庫。

一個數據庫包含一個或多個命名模式，模式中包含著表。模式還包含其他類型的命名對象，包括數據類型、函數和操作符。相同的對象名稱可以被用于不同的模式中二不會出現沖突，例如schema1和myschema都可以包含名為mytable的表。和數據庫不同，模式并不是被嚴格地隔離：一個用戶可以訪問他們所連接的數據庫中的所有模式內的對象，只要他們有足夠的權限。

下面是一些使用模式的原因：

允許多個用戶使用一個數據庫并且不會互相干擾。
將數據庫對象組織成邏輯組以便更容易管理。
第三方應用的對象可以放在獨立的模式中，這樣它們就不會與其他對象的名稱發生沖突。

模式類似于操作系統層的目錄，但是模式不能嵌套。

8.1. 創建模式

要創建一個模式，可使用CREATE SCHEMA命令，并且給出選擇的模式名稱。例如：

CREATE SCHEMA myschema;

在一個模式中創建或訪問對象，需要使用由模式名和表名構成的限定名，模式名和表名之間以點號分隔：

模式.表

在任何需要一個表名的地方都可以這樣用，包括表修改命令和后續章節要討論的數據訪問命令（為了簡潔我們在這里只談到表，但是這種方式對其他類型的命名對象同樣有效，例如類型和函數）。

事實上，還有更加通用的語法：

數據庫.模式.表

也可以使用，但是目前它只是在形式上與SQL標準兼容。如果我們寫一個數據庫名稱，它必須是我們正在連接的數據庫。

因此，如果要在一個新模式中創建一個表，可用：

CREATE TABLE myschema.mytable (... );

要刪除一個為空的模式（其中的所有對象已經被刪除），可用：

DROP SCHEMA myschema;

要刪除一個模式以及其中包含的所有對象，可用：

DROP SCHEMA myschema CASCADE;
有關于此的更一般的機制請參見Section 13。

我們常常希望創建一個由其他人所擁有的模式（因為這是將用戶動作限制在良定義的名字空間中的方法之一）。其語法是：

CREATE SCHEMA schema_name AUTHORIZATION user_name;

我們甚至可以省略模式名稱，在此種情況下模式名稱將會使用用戶名，參見Section 8.6。

以pg_開頭的模式名被保留用于系統目的，所以不能被用戶所創建。

8.2. 公共模式

在前面的小節中，我們創建的表都沒有指定任何模式名稱。默認情況下這些表（以及其他對象）會自動的被放入一個名為"public"的模式中。任何新數據庫都包含這樣一個模式。因此，下面的命令是等效的：

CREATE TABLE products ( ... );

以及：

CREATE TABLE public.products ( ... );

8.3. 模式搜索路徑

限定名寫起來很冗長，通常最好不要把一個特定模式名拉到應用中。因此，表名通常被使用非限定名來引用，它只由表名構成。系統將沿著一條搜索路徑來決定該名稱指的是哪個表，搜索路徑是一個進行查看的模式列表。搜索路徑中第一個匹配的表將被認為是所需要的。如果在搜索路徑中沒有任何匹配，即使在數據庫的其他模式中存在匹配的表名也將會報告一個錯誤。

搜索路徑中的第一個模式被稱為當前模式。除了是第一個被搜索的模式外，如果CREATE TABLE命令沒有指定模式名，它將是新創建表所在的模式。

要顯示當前搜索路徑，使用下面的命令：

SHOW search_path;

在默認設置下這將返回：

search_path --------------"$user",public

第一個元素說明一個和當前用戶同名的模式會被搜索。如果不存在這個模式，該項將被忽略。第二個元素指向我們已經見過的公共模式。

搜索路徑中的第一個模式是創建新對象的默認存儲位置。這就是默認情況下對象會被創建在公共模式中的原因。當對象在任何其他沒有模式限定的環境中被引用（表修改、數據修改或查詢命令）時，搜索路徑將被遍歷直到一個匹配對象被找到。因此，在默認配置中，任何非限定訪問將只能指向公共模式。

要把新模式放在搜索路徑中，我們可以使用：

SET search_path TO myschema,public;

（我們在這里省略了$user，因為我們并不立即需要它）。然后我們可以該表而無需使用模式限定：

DROP TABLE mytable;

同樣，由于myschema是路徑中的第一個元素，新對象會被默認創建在其中。

我們也可以這樣寫：

SET search_path TO myschema;

這樣我們在沒有顯式限定時再也不必去訪問公共模式了。公共模式沒有什么特別之處，它只是默認存在而已，它也可以被刪除。

搜索路徑對于數據類型名稱、函數名稱和操作符名稱的作用與表名一樣。數據類型和函數名稱可以使用和表名完全相同的限定方式。如果我們需要在一個表達式中寫一個限定的操作符名稱，我們必須寫成一種特殊的形式：

OPERATOR(schema.operator)

這是為了避免句法歧義。例如：

SELECT 3 OPERATOR(pg_catalog.+) 4;

實際上我們通常都會依賴于搜索路徑來查找操作符，因此沒有必要去寫如此“丑陋”的東西。

8.4. 模式和權限

默認情況下，用戶不能訪問不屬于他們的模式中的任何對象。要允許這種行為，模式的擁有者必須在該模式上授予USAGE權限。為了允許用戶使用模式中的對象，可能還需要根據對象授予額外的權限。

一個用戶也可以被允許在其他某人的模式中創建對象。要允許這種行為，模式上的CREATE權限必須被授予。注意在默認情況下，所有人都擁有在public模式上的CREATE和USAGE權限。這使得用戶能夠連接到一個給定數據庫并在它的public模式中創建對象。如果不希望允許這樣，可以撤銷該權限：

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

（第一個"public"是模式，第二個"public"指的是 "每一個用戶"。第一種是一個標識符，第二種是一個關鍵詞，所以兩者的大小寫不同。）

8.5. 系統目錄模式

除public和用戶創建的模式之外，每一個數據庫還包括一個pg_catalog模式，它包含了系統表和所有內建的數據類型、函數以及操作符。pg_catalog總是搜索路徑的一個有效部分。如果沒有在路徑中顯式地包括該模式，它將在路徑中的模式之前被搜索。這保證了內建的名稱總是能被找到。然而，如果我們希望用用戶定義的名稱重載內建的名稱，可以顯式的將pg_catalog放在搜索路徑的末尾。

由于系統表名稱以pg_開頭，最好還是避免使用這樣的名稱，以避免和未來新版本中可能出現的系統表名發生沖突。系統表將繼續采用以pg_開頭的方式，這樣它們不會與非限制的用戶表名稱沖突。

8.6. 慣用法

模式可以被用來以多種方式組織我們的數據。在默認配置下，一些常見的用法是：

如果我們不創建任何模式則所有用戶會隱式地訪問公共模式。這就像根本不存在模式一樣。當數據庫中只有一個用戶或者少量合作用戶時，推薦使用這種配置。這種配置使得我們很容易從沒有模式的環境中轉換過來。

我們可以為每一個用戶創建與它同名的模式。回想一下，默認的搜索路徑以$user開始，它將會被解析成用戶名。因此，如果每一個用戶有一個獨立的模式，它們將會默認訪問自己的模式。

如果我們使用這種配置，則我們可能也希望撤銷到公共模式的訪問（或者把它也一起刪除），這樣用戶被真正地限制在他們自己的模式中。

要安裝共享的應用（任何人都可以用的表、由第三方提供的附加函數等），將它們放在獨立的模式中。記住要授予適當的權限以允許其他用戶訪問它們。然后用戶就可以使用帶模式名的限定名稱來引用這些附加對象，或者他們可以把附加模式放入到他們的搜索路徑中。

8.7. 可移植性

在SQL標準中，在由不同用戶擁有的同一個模式中的對象是不存在的。此外，某些實現不允許創建與擁有者名稱不同名的模式。事實上，在那些僅實現了標準中基本模式支持的數據庫中，模式和用戶的概念是等同的。因此，很多用戶認為限定名稱實際上是由user_name.table_name組成的。如果我們為每一個用戶都創建了一個模式，PostgreSQL實際也是這樣認為的。

同樣，在SQL標準中也沒有public模式的概念。為了最大限度的與標準一致，我們不應使用（甚至是刪除）public模式。

當然，某些SQL數據庫系統可能根本沒有實現模式，或者提供允許跨數據庫訪問的名字空間。如果需要使用這樣一些系統，最好不要使用模式。

9. 繼承

PostgreSQL實現了表繼承，這對數據庫設計者來說是一種有用的工具（SQL:1999及其后的版本定義了一種類型繼承特性，但和這里介紹的繼承有很大的不同）。

讓我們從一個例子開始：假設我們要為城市建立一個數據模型。每一個州有很多城市，但是只有一個首府。我們希望能夠快速地檢索任何特定州的首府城市。這可以通過創建兩個表來實現：一個用于州首府，另一個用于不是首府的城市。然而，當我們想要查看一個城市的數據（不管它是不是一個首府）時會發生什么？繼承特性將有助于解決這個問題。我們可以將capitals表定義為繼承自cities表：

CREATE TABLE cities (name text,population float,altitude int -- in feet );CREATE TABLE capitals (state char(2) ) INHERITS (cities);

在這種情況下，capitals表繼承了它的父表cities的所有列。州首府還有一個額外的列state用來表示它所屬的州。

在PostgreSQL中，一個表可以從0個或者多個其他表繼承，而對一個表的查詢則可以引用一個表的所有行或者該表的所有行加上它所有的后代表。默認情況是后一種行為。例如，下面的查詢將查找所有海拔高于500尺的城市的名稱，包括州首府：

SELECT name, altitudeFROM citiesWHERE altitude > 500;

對于來自PostgreSQL教程（見Section 2.1）的例子數據，它將返回：

name | altitude -----------+----------Las Vegas | 2174Mariposa | 1953Madison | 845

在另一方面，下面的查詢將找到海拔超過500尺且不是州首府的所有城市：

SELECT name, altitudeFROM ONLY citiesWHERE altitude > 500;name | altitude -----------+----------Las Vegas | 2174Mariposa | 1953

這里的ONLY關鍵詞指示查詢只被應用于cities上，而其他在繼承層次中位于cities之下的其他表都不會被該查詢涉及。很多我們已經討論過的命令（如SELECT、UPDATE和DELETE）都支持ONLY關鍵詞。

我們也可以在表名后寫上一個*來顯式地將后代表包括在查詢范圍內：

SELECT name, altitudeFROM cities*WHERE altitude > 500;

并不是必須的，因為它對應的行為是默認的（除非改變sql_inheritance配置選項的設置）。但是書寫有助于強調會有附加表被搜索。

在某些情況下，我們可能希望知道一個特定行來自于哪個表。每個表中的系統列tableoid可以告訴我們行來自于哪個表：

SELECT c.tableoid, c.name, c.altitude FROM cities c WHERE c.altitude > 500;

將會返回：

（如果重新生成這個結果，可能會得到不同的OID數字。）通過與pg_class進行連接可以看到實際的表名：

SELECT p.relname, c.name, c.altitude FROM cities c, pg_class p WHERE c.altitude > 500 AND c.tableoid = p.oid;

將會返回：

另一種得到同樣效果的方法是使用regclass偽類型，它將象征性地打印出表的 OID：

SELECT c.tableoid::regclass, c.name, c.altitude FROM cities c WHERE c.altitude > 500;

繼承不會自動地將來自INSERT或COPY命令的數據傳播到繼承層次中的其他表中。在我們的例子中，下面的INSERT語句將會失敗：

INSERT INTO cities (name, population, altitude, state) VALUES ('Albany', NULL, NULL, 'NY');

我們也許希望數據能被以某種方式被引入到capitals表中，但是這不會發生：INSERT總是向指定的表中插入。在某些情況下，可以通過使用一個規則（見Chapter 39）來將插入動作重定向。但是這對上面的情況并沒有幫助，因為cities表根本就不包含state列，因而這個命令將在觸發規則之前就被拒絕。

父表上的所有檢查約束和非空約束都將自動被它的后代所繼承。其他類型的約束（唯一、主鍵和外鍵約束）則不會被繼承。

一個表可以從超過一個的父表繼承，在這種情況下它擁有父表們所定義的列的并集。任何定義在子表上的列也會被加入到其中。如果在這個集合中出現重名列，那么這些列將被"合并"，這樣在子表中只會有一個這樣的列。重名列能被合并的前提是這些列必須具有相同的數據類型，否則會導致錯誤。合并后的列將會從被合并的列中復制所有的檢查約束，并且如果其中一個被合并的列上有非空約束，合并后的列也會被標記為非空。

表繼承通常是在子表被創建時建立，使用CREATE TABLE語句的INHERITS子句。一個已經被創建的表也可以另外一種方式增加一個新的父親關系，使用ALTER TABLE的INHERIT變體。要這樣做，新的子表必須已經包括和父表相同名稱和數據類型的列。子表還必須包括和父表相同的檢查約束和檢查表達式。相似地，一個繼承鏈接也可以使用ALTER TABLE的 NO INHERIT變體從一個子表中移除。動態增加和移除繼承鏈接可以用于實現表劃分（見Section 10）。

一種創建一個未來將被用做子女的新表的方法是在CREATE TABLE中使用LIKE子句。這將創建一個和源表具有相同列的新表。如果源表上定義有任何CHECK約束，LIKE的INCLUDING CONSTRAINTS選項可以用來讓新的子表也包含和父表相同的約束。

當有任何一個子表存在時，父表不能被刪除。當子表的列或者檢查約束繼承于父表時，它們也不能被刪除或修改。如果希望移除一個表和它的所有后代，一種簡單的方法是使用CASCADE選項刪除父表（見Section 13）。

ALTER TABLE將會把列的數據定義或檢查約束上的任何變化沿著繼承層次向下傳播。同樣，刪除被其他表依賴的列只能使用CASCADE選項。ALTER TABLE對于重名列的合并和拒絕遵循與CREATE TABLE同樣的規則。

請注意表訪問權限的處理方式。查詢一個父表將自動地訪問子表中的數據而不需要進一步的訪問權限檢查。這體現了子表的數據（也）在父表里存在。但是，訪問子表并不是自動被允許的且可能需要進一步被授予權限。

外部表（見Section 11）也可以是繼承層次中的一部分，即可以作為父表也可以作為子表，就像常規表一樣。如果一個外部表是繼承層次的一部分，那么任何不被該外部表支持的操作也不被整個層次所支持。

9.1. 警告

注意并非所有的SQL命令都能工作在繼承層次上。用于數據查詢、數據修改或模式修改（例如SELECT、UPDATE、DELETE、大部分ALTER TABLE的變體，但INSERT或ALTER TABLE ... RENAME不在此列）的命令會默認將子表包含在內并且支持ONLY記號來排除子表。負責數據庫維護和調整的命令（如REINDEX、VACUUM）只工作在獨立的、物理的表上并且不支持在繼承層次上的遞歸。每個命令相應的行為請參見它們的參考頁（Reference I, SQL 命令）。

繼承特性的一個嚴肅的限制是索引（包括唯一約束）和外鍵約束值應用在單個表上而非它們的繼承子女。在外鍵約束的引用端和被引用端都是這樣。因此，按照上面的例子：

如果我們聲明cities.name為UNIQUE或者PRIMARY KEY，這將不會阻止capitals表中擁有和cities中城市同名的行。而且這些重復的行將會默認顯示在cities的查詢中。事實上，capitals在默認情況下是根本不能擁有唯一約束的，并且因此能夠包含多個同名的行。我們可以為capitals增加一個唯一約束，但這無法阻止相對于cities的重復。
相似地，如果我們指定cities.name REFERENCES某個其他表，該約束不會自動地傳播到capitals。在此種情況下，我們可以變通地在capitals上手工創建一個相同的REFERENCES約束。
指定另一個表的列REFERENCES cities(name)將允許其他表包含城市名稱，但不會包含首府名稱。這對于這個例子不是一個好的變通方案。

這些不足可能還將存在于某些未來的發布中，但是同時在決定繼承是否對我們的應用有用時需要相當小心。

10. 劃分

PostgreSQL支持基本的表劃分。本小節介紹為何以及怎樣把劃分實現為數據庫設計的一部分。

10.1. 概述

劃分指的是將邏輯上的一個大表分成一些小的物理上的片。劃分有很多益處：

在某些情況下查詢性能能夠顯著提升，特別是當那些訪問壓力大的行在一個分區或者少數幾個分區時。劃分可以取代索引的主導列、減小索引尺寸以及使索引中訪問壓力大的部分更有可能被放在內存中。
當查詢或更新訪問一個分區的大部分行時，可以通過該分區上的一個順序掃描來取代分散到整個表上的索引和隨機訪問，這樣可以改善性能。
如果需求計劃使用劃分設計，可以通過增加或移除分區來完成批量載入和刪除。ALTER TABLE NO INHERIT和DROP TABLE都遠快于一個批量操作。這些命令也完全避免了由批量DELETE造成的VACUUM負載。
很少使用的數據可以被遷移到便宜且較慢的存儲介質上。

當一個表非常大時，劃分所帶來的好處是非常值得的。一個表何種情況下會從劃分獲益取決于應用，一個經驗法則是當表的尺寸超過了數據庫服務器物理內存時，劃分會為表帶來好處。

目前，PostgreSQL支持通過表繼承來進行劃分。每一個分區被創建為父表的一個子表。父表本身通常是空的，它的存在僅僅為了表示整個數據集。在嘗試建立劃分之前，應該先熟悉繼承（參見Section 9）。

在PostgreSQL中可以實現下列形式的劃分：

范圍劃分

表被根據一個關鍵列或一組列劃分為"范圍"，不同的分區的范圍之間沒有重疊。例如，我們可以根據日期范圍劃分，或者根據特定業務對象的標識符劃分。

列表劃分

通過顯式地列出每一個分區中出現的鍵值來劃分表。

10.2. 實現劃分

要建立一個劃分的表，可以這樣做：

1.創建"主"表，所有的分區都將繼承它。

這個表將不會包含任何數據。不要在這個表上定義任何檢查約束，除非準備將它們應用到所有分區。同樣也不需要定義任何索引或者唯一約束。

2.創建一些繼承于主表的"子"表。通常，這些表不會在從主表繼承的列集中增加任何列。

我們將這些子表認為是分區，盡管它們在各方面來看普通的PostgreSQL表（或者可能是外部表）。

3.為分區表增加表約束以定義每個分區中允許的鍵值。

典型的例子是：

CHECK ( x = 1 ) CHECK ( county IN ( 'Oxfordshire', 'Buckinghamshire', 'Warwickshire' )) CHECK ( outletID >= 100 AND outletID < 200 )

要確保這些約束能夠保證在不同分區所允許的鍵值之間不存在重疊。設置范圍約束時一種常見的錯誤是：

CHECK ( outletID BETWEEN 100 AND 200 ) CHECK ( outletID BETWEEN 200 AND 300 )

這是錯誤的，因為鍵值200并沒有被清楚地分配到某一個分區。

注意在語法上范圍劃分和列表劃分沒有區別，這些術語只是為了描述方便而存在。

4.對于每一個分區，在關鍵列上創建一個索引，并創建其他我們所需要的索引（關鍵索引并不是嚴格必要的，但是在大部分情況下它都是有用的。如果我們希望鍵值是唯一的，則我們還要為每一個分區創建一個唯一或者主鍵約束。）。

5.還可以有選擇地定義一個觸發器或者規則將在主表上的數據插入重定向到合適的分區上。

6.確保在postgresql.conf中constraint_exclusion配置參數沒有被禁用。如果它被禁用，查詢將不會被按照期望的方式優化。

例如，假設我們正在為一個大型的冰淇淋公司構建一個數據庫。該公司測量每天在每一個區域的最高氣溫以及冰淇淋銷售。在概念上，我們想要一個這樣的表：

CREATE TABLE measurement (city_id int not null,logdate date not null,peaktemp int,unitsales int );

由于該表的主要用途是為管理層提供在線報告，我們知道大部分查詢將只會訪問上周、上月或者上季度的數據。為了減少需要保存的舊數據的量，我們決定只保留最近3年的數據。在每一個月的開始，我們將刪除最老的一個月的數據。

在這種情況下，我們可以使用劃分來幫助我們滿足對于測量表的所有不同需求。按照上面所勾勒的步驟，劃分可以這樣來建立：

1.主表是measurement表，完全按照以上的方式聲明。

2.下一步我們為每一個活動月創建一個分區：

CREATE TABLE measurement_y2006m02 ( ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( ) INHERITS (measurement);

每一個分區自身都是完整的表，但是它們的定義都是從measurement表繼承而來。

這解決了我們的一個問題：刪除舊數據。每個月，我們所需要做的是在最舊的子表上執行一個DROP TABLE命令并為新一個月的數據創建一個新的子表。

3.我們必須提供不重疊的表約束。和前面簡單地創建分區表不同，實際的表創建腳本應該是：

CREATE TABLE measurement_y2006m02 (CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 (CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 (CHECK ( logdate >= DATE '2007-11-01' AND logdate < DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 (CHECK ( logdate >= DATE '2007-12-01' AND logdate < DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 (CHECK ( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) ) INHERITS (measurement);

4.我們可能在關鍵列上也需要索引：

CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); ... CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate);

在這里我們選擇不增加更多的索引。

5.我們希望我們的應用能夠使用INSERT INTO measurement ...并且數據將被重定向到合適的分區表。我們可以通過為主表附加一個合適的觸發器函數來實現這一點。如果數據將只被增加到最后一個分區，我們可以使用一個非常簡單的觸發器函數：

CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$BEGININSERT INTO measurement_y2008m01 VALUES (NEW.*);RETURN NULL; END;$$LANGUAGE plpgsql;

完成函數創建后，我們創建一個調用該觸發器函數的觸發器：

CREATE TRIGGER insert_measurement_triggerBEFORE INSERT ON measurementFOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

我們必須在每個月重新定義觸發器函數，這樣它才會總是指向當前分區。而觸發器的定義則不需要被更新。

我們也可能希望插入數據時服務器會自動地定位應該加入數據的分區。我們可以通過一個更復雜的觸發器函數來實現之，例如：

CREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$BEGINIF ( NEW.logdate >= DATE '2006-02-01' ANDNEW.logdate < DATE '2006-03-01' ) THENINSERT INTO measurement_y2006m02 VALUES (NEW.*);ELSIF ( NEW.logdate >= DATE '2006-03-01' ANDNEW.logdate < DATE '2006-04-01' ) THENINSERT INTO measurement_y2006m03 VALUES (NEW.*);...ELSIF ( NEW.logdate >= DATE '2008-01-01' ANDNEW.logdate < DATE '2008-02-01' ) THENINSERT INTO measurement_y2008m01 VALUES (NEW.*);ELSERAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';END IF;RETURN NULL; END;$$LANGUAGE plpgsql;

觸發器的定義和以前一樣。注意每一個IF測試必須準確地匹配它的分區的CHECK約束。

當該函數比單月形式更加復雜時，并不需要頻繁地更新它，因為可以在需要的時候提前加入分支。

Note:
在實踐中，如果大部分插入都會進入最新的分區，最好先檢查它。為了簡潔，我們為觸發器的檢查采用了和本例中其他部分一致的順序。

如我們所見，一個復雜的劃分模式可能需要大量的DDL。在上面的例子中，我們需要每月創建一個新分區，所以最好能夠編寫一個腳本自動地生成所需的DDL。

10.3. 管理分區

通常當初始定義的表傾向于動態變化時，一組分區會被創建。刪除舊的分區并周期性地為新數據增加新分區是很常見的。劃分的一個最重要的優點是可以通過操縱分區結構來使得這種痛苦的任務幾乎是自發地完成，而不需要去物理地移除大量的數據。

移除舊數據的最簡單的選項是直接刪除不再需要的分區：

DROP TABLE measurement_y2006m02;

這可以非常快地刪除百萬級別的記錄，因為它不需要逐一地刪除記錄。

另一個經常使用的選項是將分區從被劃分的表中移除，但是把它作為一個獨立的表保留下來：

ALTER TABLE measurement_y2006m02 NO INHERIT measurement;

這允許在數據被刪除前執行更進一步的操作。例如，這是一個很有用的時機通過COPY、pg_dump或類似的工具來備份數據。這也是進行數據聚集、執行其他數據操作或運行報表的好時機。

相似地我們也可以增加新分區來處理新數據。我們可以在被劃分的表中創建一個新的空分區：

CREATE TABLE measurement_y2008m02 (CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ) ) INHERITS (measurement);

作為一種選擇方案，有時創建一個在分區結構之外的新表更方便，并且在以后才將它作為一個合適的分區。這使得數據可以在出現于被劃分表中之前被載入、檢查和轉換：

CREATE TABLE measurement_y2008m02(LIKE measurement INCLUDING DEFAULTS INCLUDING CONSTRAINTS); ALTER TABLE measurement_y2008m02 ADD CONSTRAINT y2008m02CHECK ( logdate >= DATE '2008-02-01' AND logdate < DATE '2008-03-01' ); \copy measurement_y2008m02 from 'measurement_y2008m02' -- 可能做一些其他數據準備工作 ALTER TABLE measurement_y2008m02 INHERIT measurement;

10.4. 劃分和約束排除

約束排除是一種查詢優化技術，它可以為按照以上方式定義的被劃分表提高性能。例如：

SET constraint_exclusion = on;
SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
如果沒有約束排除，上述查詢將掃描measurement表的每一個分區。在啟用約束排除后，規劃器將檢查每一個分區的約束來確定該分區需不需要被掃描，因為分區中可能不包含滿足查詢WHERE子句的行。如果規劃器能夠證實這一點，則它將會把該分區排除在查詢計劃之外。

可以使用EXPLAIN命令來顯示開啟了constraint_exclusion的計劃和沒有開啟該選項的計劃之間的區別。一個典型的未優化的計劃是：

SET constraint_exclusion = off; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';QUERY PLAN -----------------------------------------------------------------------------------------------Aggregate (cost=158.66..158.68 rows=1 width=0)-> Append (cost=0.00..151.88 rows=2715 width=0)-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date)-> Seq Scan on measurement_y2006m02 measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date)-> Seq Scan on measurement_y2006m03 measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date) ...-> Seq Scan on measurement_y2007m12 measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date)-> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date)

其中的某些或者全部分區將會使用索引掃描而不是全表順序掃描，但是關鍵在于根本不需要掃描舊分區來回答這個查詢。當我們開啟約束排除后，對于同一個查詢我們會得到一個更加廉價的計劃：

SET constraint_exclusion = on; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';QUERY PLAN -----------------------------------------------------------------------------------------------Aggregate (cost=63.47..63.48 rows=1 width=0)-> Append (cost=0.00..60.75 rows=1086 width=0)-> Seq Scan on measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date)-> Seq Scan on measurement_y2008m01 measurement (cost=0.00..30.38 rows=543 width=0)Filter: (logdate >= '2008-01-01'::date)

注意約束排除只由CHECK約束驅動，而非索引的存在。因此，沒有必要在關鍵列上定義索引。是否在給定分區上定義索引取決于我們希望查詢經常掃描表的大部分還是小部分。在后一種情況中索引將會發揮作用。

constraint_exclusion的默認（也是推薦）設置實際上既不是on也不是off，而是一個被稱為partition的中間設置，這使得該技術只被應用于將要在被分區表上工作的查詢。設置on將使得規劃器在所有的查詢中檢查CHECK約束，即使簡單查詢不會從中受益。

10.5. 可選劃分方法

另一種將插入重定向到合適的分區表的方法是在主表上建立規則而不是觸發器，例如：

CREATE RULE measurement_insert_y2006m02 AS ON INSERT TO measurement WHERE( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) DO INSTEADINSERT INTO measurement_y2006m02 VALUES (NEW.*); ... CREATE RULE measurement_insert_y2008m01 AS ON INSERT TO measurement WHERE( logdate >= DATE '2008-01-01' AND logdate < DATE '2008-02-01' ) DO INSTEADINSERT INTO measurement_y2008m01 VALUES (NEW.*);

一個規則比一個觸發器具有明顯更高的負荷，但是該負荷是由每個查詢承擔而不是每一個行，因此這種方法可能對于批量插入的情況有益。但是，在大部分情況下觸發器方法能提供更好的性能。

注意COPY會忽略規則。如果希望使用COPY來插入數據，我們將希望將數據復制到正確的分區表而不是主表。COPY會引發觸發器，因此如果使用觸發器方法就可以正常地使用它。

規則方法的另一個缺點是如果一組規則沒有覆蓋被插入的數據，則該數據將被插入到主表中而不會發出任何錯誤。

劃分也可以使用一個UNION ALL視圖來組織。例如：

CREATE VIEW measurement ASSELECT * FROM measurement_y2006m02 UNION ALL SELECT * FROM measurement_y2006m03 ... UNION ALL SELECT * FROM measurement_y2007m11 UNION ALL SELECT * FROM measurement_y2007m12 UNION ALL SELECT * FROM measurement_y2008m01;

但是，如果要增加或者刪除單獨的分區，就需要重新地創建視圖。在實踐中，相對于使用繼承，這種方法很少被推薦。

10.6. 警告

下面的警告適用于被劃分表：

沒有自動的方法來驗證所有的CHECK約束是互斥的。創建代碼來生成分區并創建或修改相關對象比手工寫命令要更安全。
這里展示的模式都假設分區的關鍵列從不改變，或者是其改變不足以導致它被移到另一個分區。一個嘗試將行移到另一個分區的UPDATE會失敗，因為CHECK約束的存在。如果我們需要處理這類情況，我們可以在分區表上放置合適的更新觸發器，但是它會使得結構的管理更加復雜。
如果我們在使用手工的VACUUM或ANALYZE命令，別忘了需要在每一個分區上都運行一次。以下的命令：

ANALYZE measurement;

只會處理主表。

帶有ON CONFLICT子句的INSERT 語句不太可能按照預期的方式工作，因為ON CONFLICT動作只有在指定的目標關系（而非它的子關系）上有唯一違背的情況下才會被采用。

下面的警告適用于約束排除：

只有在查詢的WHERE子句包含常量（或者外部提供的參數）時，約束排除才會起效。例如，一個與非不變函數（例如CURRENT_TIMESTAMP）的比較不能被優化，因為規劃器不知道該函數的值在運行時會落到哪個分區內。
保持劃分約束簡單，否則規劃器可能沒有辦法驗證無需訪問的分區。按前面的例子所示，為列表劃分使用簡單相等條件或者為范圍劃分使用簡單范圍測試。一個好的經驗法則是劃分約束應該只包含使用B-tree可索引操作符的比較，比較的雙方應該是劃分列和常量。
在約束排除期間，主表所有的分區上的所有約束都會被檢查，所以大量的分區將會顯著地增加查詢規劃時間。使用這些技術的劃分在大約最多100個分區的情況下工作得很好，但是不要嘗試使用成千個分區。

11. 外部數據

PostgreSQL實現了部分的SQL/MED規定，允許我們使用普通SQL查詢來訪問位于PostgreSQL之外的數據。這種數據被稱為外部數據（注意這種用法不要和外鍵混淆，后者是數據庫中的一種約束）。

外部數據可以在一個外部數據包裝器的幫助下被訪問。一個外部數據包裝器是一個庫，它可以與一個外部數據源通訊，并隱藏連接到數據源和從它獲取數據的細節。在contrib模塊中有一些外部數據包裝器，參見Appendix F。其他類型的外部數據包裝器可以在第三方產品中找到。如果這些現有的外部數據包裝器都不能滿足你的需要，可以自己編寫一個。

要訪問外部數據，我們需要建立一個外部服務器對象，它根據它所支持的外部數據包裝器所使用的一組選項定義了如何連接到一個特定的外部數據源。接著我們需要創建一個或多個外部表，它們定義了外部數據的結構。一個外部表可以在查詢中像一個普通表一樣地使用，但是在PostgreSQL服務器中外部表沒有存儲數據。不管使用什么外部數據包裝器，PostgreSQL會要求外部數據包裝器從外部數據源獲取數據，或者在更新命令的情況下傳送數據到外部數據源。

訪問遠程數據可能需要在外部數據源的授權。這些信息通過一個用戶映射提供，它基于當前的PostgreSQL角色提供了附加的數據例如用戶名和密碼。

12. 其他數據庫對象

表是一個關系型數據庫結構中的核心對象，因為它們承載了我們的數據。但是它們并不是數據庫中的唯一一種對象。有很多其他種類的對象可以被創建來使得數據的使用和刮泥更加方便或高效。在本章中不會討論它們，但是我們在會給出一個列表：

視圖
函數和操作符
數據類型和域
觸發器和重寫規則

13. 依賴跟蹤

當我們創建一個涉及到很多具有外鍵約束、視圖、觸發器、函數等的表的復雜數據庫結構時，我們隱式地創建了一張對象之間的依賴關系網。例如，具有一個外鍵約束的表依賴于它所引用的表。

為了保證整個數據庫結構的完整性，PostgreSQL確保我們無法刪除仍然被其他對象依賴的對象。例如，嘗試刪除Section 5.3.5中的產品表會導致一個如下的錯誤消息，因為有訂單表依賴于產品表：

DROP TABLE products;ERROR: cannot drop table products because other objects depend on it DETAIL: constraint orders_product_no_fkey on table orders depends on table products HINT: Use DROP ... CASCADE to drop the dependent objects too.

該錯誤消息包含了一個有用的提示：如果我們不想一個一個去刪除所有的依賴對象，我們可以執行：

DROP TABLE products CASCADE;

這樣所有的依賴對象將被移除，同樣依賴于它們的任何對象也會被遞歸刪除。在這種情況下，訂單表不會被移除，但是它的外鍵約束會被移除。之所以在這里會停下，是因為沒有什么依賴著外鍵約束（如果希望檢查DROP ... CASCADE會干什么，運行不帶CASCADE的DROP并閱讀DETAIL輸出）。

PostgreSQL中的幾乎所有DROP命令都支持CASCADE。當然，其本質的區別隨著對象的類型而不同。我們也可以用RESTRICT代替CASCADE來獲得默認行為，它將阻止刪除任何被其他對象依賴的對象。

Note: 根據SQL標準，在DROP命令中指定RESTRICT或CASCADE是被要求的。但沒有哪個數據庫系統真正強制了這個規則，但是不同的系統中兩種默認行為都是可能的。

如果一個DROP命令列出了多個對象，只有在存在指定對象構成的組之外的依賴關系時才需要CASCADE。例如，如果發出命令DROP TABLE tab1, tab2且存在從tab2到tab1的外鍵引用，那么就不需要CASCADE即可成功執行。

對于用戶定義的函數，PostgreSQL會追蹤與函數外部可見性質相關的依賴性，例如它的參數和結果類型，但不追蹤檢查函數體才能知道的依賴性。例如，考慮這種情況：

CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow','green', 'blue', 'purple');CREATE TABLE my_colors (color rainbow, note text);CREATE FUNCTION get_color_note (rainbow) RETURNS text AS'SELECT note FROM my_colors WHERE color = $1'LANGUAGE SQL;

PostgreSQL將會注意到get_color_note函數依賴于rainbow類型：刪掉該類型會強制刪除該函數，因為該函數的參數類型就無法定義了。但是PostgreSQL不會認為get_color_note依賴于my_colors表，因此即使該表被刪除也不會刪除這個函數。雖然這種方法有缺點，但是也有好處。如果該表丟失，這個函數在某種程度上仍然是有效的，但是執行它會導致錯誤。創建一個同名的新表將允許該函數重新有效。

與50位技術專家面對面20年技術見證，附贈技術全景圖

總結

以上是生活随笔為你收集整理的PostgreSQL SQL 语言：数据定义的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇：关于android工程添加support
下一篇： C++类模版------STL的原理