PostgreSQL學習手冊(資料表)

一、表的定義：

    對於任何一種關係型資料庫而言，表都是資料存放區的最核心、最基礎的對象單元。現在就讓我們從這裡起步吧。
    1. 建立表：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric
    );
    
    2. 刪除表：
    DROP TABLE products;
    
    3. 建立帶有預設值的表：
    CREATE TABLE products (
        product_no integer,
        name text,
        price numeric DEFAULT 9.99 --DEFAULT是關鍵字，其後的數值9.99是欄位price的預設值。
    );
    
    CREATE TABLE products (
        product_no SERIAL,            --SERIAL類型的欄位表示該欄位為自增欄位，完全等同於Oracle中的Sequence。
        name text,
        price numeric DEFAULT 9.99
    );
    輸出為：
    NOTICE:  CREATE TABLE will create implicit sequence "products_product_no_seq" for serial column "products.product_no"
   
    4. 約束：
    檢查約束是表中最為常見的約束類型，它允許你聲明在某個欄位裡的數值必須滿足一個布林運算式。不僅如此，我們也可以聲明表層級的檢查約束。
    CREATE TABLE products (
        product_no integer,
        name text,
        --price欄位的值必須大於0，否則在插入或修改該欄位值是，將引發違規錯誤。還需要說明的是，該檢查約束
        --是匿名約束，即在表定義時沒有顯示命名該約束，這樣PostgreSQL將會根據當前的表名、欄位名和約束類型，
        --為該約束自動命名，如：products_price_check。
        price numeric CHECK (price > 0)
    );
 
    CREATE TABLE products (
        product_no integer,
        name text,
        --該欄位的檢查約束被顯示命名為positive_price。這樣做好處在於今後維護該約束時，可以根據該名進行直接操作。
        price numeric CONSTRAINT positive_price CHECK (price > 0)
    );
    下面的約束是非空約束，即約束的欄位不能插入空值，或者是將已有資料更新為空白值。
    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric
    );
    如果一個欄位中存在多個約束，在定義時可以不用考慮約束的聲明順序。
    CREATE TABLE products (
        product_no integer NOT NULL,
        name text NOT NULL,
        price numeric NOT NULL CHECK (price > 0)
    );
    唯一性限制式，即指定的欄位不能插入重複值，或者是將某一記錄的值更新為當前表中的已有值。
    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
    );
    在插入資料時，空值(NULL)之間被視為不相等的資料，因此對於某一唯一性欄位，可以多次插入空值。然而需要注意的是，這一規則並不是被所有資料庫都遵守，因此在進行資料庫移植時可能會造成一定的麻煩。
   
    5. 主鍵和外鍵：
    從技術上來講，主鍵約束只是唯一約束和非空約束的組合。
    CREATE TABLE products (
        product_no integer PRIMARY KEY,  --欄位product_no被定義為該表的唯一主鍵。
        name text,
        price numeric
    );
    和唯一性限制式一樣，主鍵可以同時作用於多個欄位，形成聯合主鍵：
    CREATE TABLE example (
        a integer,
        b integer,
        c integer,
        PRIMARY KEY (b, c)
    );
    外鍵約束聲明一個欄位（或者一組欄位）的數值必須匹配另外一個表中某些行出現的數值。 我們把這個行為稱做兩個相關表之間的參考完整性。
    CREATE TABLE orders (
        order_id integer PRIMARY KEY, --該表也可以有自己的主鍵。
        --該表的product_no欄位為上面products表主鍵(product_no)的外鍵。
        product_no integer REFERENCES products(product_no),
        quantity integer
    );
   
    CREATE TABLE t1 (
        a integer PRIMARY KEY,
        b integer,
        c integer,
        --該外鍵的欄位數量和被參考資料表中主鍵的數量必須保持一致。
        FOREIGN KEY (b, c) REFERENCES example (b, c)
    );  
    當多個表之間存在了主外鍵的參考性約束關係時，如果想刪除被應用表(主鍵表)中的某行記錄，由於該行記錄的主鍵欄位值可能正在被其參考資料表(外鍵表)中某條記錄所關聯，所以刪除操作將會失敗。如果想完成此操作，一個顯而易見的方法是先刪除參考資料表中和該記錄關聯的行，之後再刪除被參考資料表中的該行記錄。然而需要說明的是，PostgreSQL為我們提供了更為方便的方式完成此類操作。
    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 選項，它是在被引用欄位修改(更新)的時候調用的。可用的動作是一樣的。

二、系統欄位：

    PostgreSQL的每個資料表中都包含幾個隱含定義的系統欄位。因此，這些名字不能用於使用者定義的欄位名。這些系統欄位的功能有些類似於Oracle中的rownum和rowid等。
    oid: 行的物件識別碼(對象ID)。這個欄位只有在建立表的時候使用了WITH OIDS，或者是設定了配置參數default_with_oids時出現。這個欄位的類型是oid(和欄位同名)。
    tableoid: 包含本行的表的OID。這個欄位對那些從繼承層次中選取的查詢特別有用，因為如果沒有它的話，我們就很難說明一行來自哪個獨立的表。tableoid可以和pg_class的oid欄位串連起來擷取表名字。
    xmin: 插入該行版本的事務的標識(事務ID)。
    cmin: 在插入事務內部的命令標識(從零開始)。
    xmax: 刪除事務的標識(事務ID)，如果不是被刪除的行版本，那麼是零。
    cmax: 在刪除事務內部的命令標識符，或者是零。
    ctid: 一個行版本在它所處的表內的物理位置。請注意，儘管ctid可以用於非常快速地定位行版本，但每次VACUUM FULL之後，一個行的ctid都會被更新或者移動。因此ctid是不能作為長期的行標識符的。   
    OID是32位的量，是在同一個叢集內通用的計數器上賦值的。對於一個大型或者長時間使用的資料庫，這個計數器是有可能重疊的。因此，假設OID是唯一的是非常錯誤的，除非你自己採取了措施來保證它們是唯一的。如果你需要標識表中的行，我們強烈建議使用序號產生器。     
   
三、表的修改：

    1. 增加欄位：
    ALTER TABLE products ADD COLUMN description text;
    新增的欄位對於表中已經存在的行而言最初將先填充所給出的預設值(如果你沒有聲明DEFAULT子句，那麼預設是空值)。
    在新增欄位時，可以同時給該欄位指定約束。
    ALTER TABLE products ADD COLUMN description text CHECK(description <> '');
    
    2. 刪除欄位：
    ALTER TABLE products DROP COLUMN description;
    如果該表為被參考資料表，該欄位為被引用欄位，那麼上面的刪除操作將會失敗。如果要想在刪除被引用欄位的同時級聯的刪除其所有引用欄位，可以採用下面的文法形式。
    ALTER TABLE products DROP COLUMN description CASCADE;
    
    3. 增加約束：
    ALTER TABLE products ADD CHECK(name <> '');  --增加一個表級約束
    ALTER TABLE products ADD CONSTRAINT some_name UNIQUE(product_no);--增加命名的唯一性限制式。
    ALTER TABLE products ADD FOREIGN KEY(pdt_grp_id) REFERENCES pdt_grps; --增加外鍵約束。
    ALTER TABLE products ALTER COLUMN product_no SET NOT NULL; --增加一個非空約束。
    
    4. 刪除約束：
    ALTER TABLE products DROP CONSTRAINT some_name;
    對於顯示命名的約束，可以根據其名稱直接刪除，對於隱式自動命名的約束，可以通過psql的\d tablename來擷取該約束的名字。和刪除欄位一樣，如果你想刪除有著被依賴關係地約束，你需要用CASCADE。一個例子是某個外鍵約束依賴被引用欄位上的唯一約束或者主鍵約束。如：
    MyTest=# \d products
         Table "public.products"
       Column     |  Type   | Modifiers
     ------------+---------+-----------
     product_no | integer |
     name          | text    |
     price           | numeric |
     Check constraints:
        "positive_price" CHECK (price > 0::numeric)
    和其他約束不同的是，非空約束沒有名字，因此只能通過下面的方式刪除：
    ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
    
    5. 改變欄位的預設值:
    在為已有欄位添加預設值時，不會影響任何錶中現有的資料行， 它只是為將來INSERT命令改變預設值。
    ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
    下面為刪除預設值：
    ALTER TABLE products ALTER COLUMN price DROP DEFAULT
   
    6. 修改欄位的資料類型：
    只有在欄位裡現有的每個項都可以用一個隱含的類型轉換轉換成新的類型時才可能成功。比如當前的資料都是整型，而轉換的目標類型為numeric或varchar，這樣的轉換一般都可以成功。與此同時，PostgreSQL還將試圖把欄位的預設值（如果存在）轉換成新的類型， 還有涉及該欄位的任何約束。但是這些轉換可能失敗，或者可能產生奇怪的結果。 在修改某欄位類型之前，你最好刪除那些約束，然後再把自己手工修改過的添加上去。
    ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
       
    7. 修改欄位名：
    ALTER TABLE products RENAME COLUMN product_no TO product_number;
    
    8. 修改表名：
    ALTER TABLE products RENAME TO items;
   
四、許可權：

    只有表的所有者才能修改或者刪除表的許可權。要賦予一個許可權，我們使用GRANT命令，要撤銷一個許可權，使用REVOKE命令。
    需要指出的是，PUBLIC是特殊"使用者"可以用於將許可權賦予系統中的每一個使用者。在聲明許可權的位置寫ALL則將所有的與該物件類型相關的許可權都賦予出去。
    GRANT UPDATE ON table_name TO user;  --將表的更新許可權賦予指定的user。
    GRANT SELECT ON table_name TO GROUP group; --將表的select許可權賦予指定的組。
    REVOKE ALL ON table_name FROM PUBLIC; --將表的所有許可權從Public撤銷。
    最初，只有對象所有者(或者超級使用者)可以賦予或者撤銷對象的許可權。但是，我們可以賦予一個"with grant option"許可權，這樣就給接受許可權的人以授予該許可權給其它人的許可權。如果授予選項後來被撤銷，那麼所有那些從這個接受者接受了許可權的使用者(直接或者通過級連的授權)都將失去該許可權。

    這裡需要特別說明的是，該部落格中的大部分案例和段落均取自於PostgreSQL中文文檔，如轉載本系列部落格，請同樣註明該出處。