974      阿裏雲  技術社區[雲棲]

PostgreSQL SQL 語言：數據定義

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

關係型數據庫中的一個表非常像紙上的一張表：它由行和列組成。列的數量和順序是固定的，並且每一列擁有一個名字。行的數目是變化的，它反映了在一個給定時刻表中存儲的數據量。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。

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

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

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

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

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速寫將在後麵文章進一步討論。

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

到目前為止，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 &&)
);

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

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

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命令的數目而不是被處理的行數。同樣，隻有真正 修改了數據庫內容的命令才會消耗一個命令標識符。

當我們已經創建了一個表並意識到犯了一個錯誤或者應用需求發生改變時，我們可以移除表並重新創建它。但如果表中已經被填充數據或者被其他數據庫對象引用（例如有一個外鍵約束），這種做法就顯得很不方便。因此，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;

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

有多種不同的權限：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"來允許接收人將權限轉授給其他人。如果後來授予選項被撤銷，則所有從接收人那裏獲得的權限（直接或者通過授權鏈獲得）都將被撤銷。

除可以通過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 managers
    USING (manager = current_user);

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

CREATE POLICY user_policy ON users
    USING (user_name = current_user);

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

CREATE POLICY user_policy ON users
    USING (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 UPDATE
  USING (current_user = user_name)
  WITH CHECK (
    current_user = username AND
    shell 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/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | 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/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | 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 SELECT
  USING (group_id <= (SELECT group_id FROM users WHERE user_name = current_user));
CREATE POLICY fp_u ON information FOR UPDATE
  USING (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可能被嵌入到一個安全性定義者函數中）。 還有，在被引用的表上過多並發地使用行共享鎖可能會導致性能問題， 特別是表更新比較頻繁時。另一種解決方案（如果被引用表上的更新 不頻繁就可行）是在更新被引用表時對它取一個排他鎖，這樣就沒有 並發事務能夠檢查舊的行值了。或者我們可以在提交對被引用表的更新 之後、在做依賴於新安全性情況的更改之前等待所有並發事務結束。

一個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數據庫係統可能根本沒有實現模式，或者提供允許跨數據庫訪問的名字空間。如果需要使用這樣一些係統，最好不要使用模式。

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, altitude
    FROM cities
    WHERE altitude > 500;

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

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

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

SELECT name, altitude
    FROM ONLY cities
    WHERE altitude > 500;

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

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

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

SELECT name, altitude
    FROM cities*
    WHERE altitude > 500;

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

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

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

將會返回：

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

（如果重新生成這個結果，可能會得到不同的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;

將會返回：

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

另一種得到同樣效果的方法是使用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不在此列）的

最後更新：2017-08-16 17:32:14

  上一篇：  阿裏雲DNS專家，手把手教你定位域名解析不生效

  下一篇：  mybatis