PolarDB：INSERT

簡介

INSERT將新行插入到一個表中。可以插入一個或者更多由值運算式指定的行，或者插入來自一個查詢的零行或者更多行。

目標列的名稱可以以任意順序列出。如果沒有給出列名列表，則有兩種確定目標列的可能性。第一種是以被聲明的順序列出該表的所有列。另一種可能性是，如果VALUES 子句或者 query只提供 N個列，則以被聲明的順序列出該表的前 N列。VALUES 子句或者 query提供的值會被從左至右關聯到這些顯式或者隱式給出的目標列。

每一個沒有出現在顯式或者隱式列列表中的列都將被預設填充，如果為該列聲明過預設值則用預設值填充，否則用空值填充。

如果任一列的運算式不是正確的資料類型，將會嘗試自動類型轉換。

ON CONFLICT可以用來指定發生唯一約束或者排除約束違反錯誤時的替換動作。

可選的RETURNING子句讓INSERT根據實際被插入（如果使用了ON CONFLICT DO UPDATE子句， 可能是被更新）的每一行來計算和傳回值。這主要用來擷取由預設值提供的值，例如一個序號。不過，允許在其中包括使用該表列的任何錶達式。 RETURNING列表的文法與SELECT的輸出資料行表的相同。只有被成功地插入或者更新的行才將被返回。例如，如果一行被鎖定但由於不滿足ON CONFLICT DO UPDATE ... WHERE clause condition沒有被更新，該行將不被返回。

為了向表中插入，你必須具有其上的INSERT特權。 如果存在ON CONFLICT DO UPDATE子句，還要求該表上的UPDATE特權。

如果一個列列表被指定，你只需要其中的列上的INSERT 特權。類似地，在指定了ON CONFLICT DO UPDATE時，你只需要被列出要更新的列上的UPDATE特權。不過， ON CONFLICT DO UPDATE還要求其值被 ON CONFLICT DO UPDATE運算式或者 condition使用的列上的SELECT特權。

使用RETURNING子句需要RETURNING中提到的所有列的 SELECT許可權。 如果使用 query子句從查詢中插入行， 則當然需要對查詢中使用的任何錶或列具有SELECT許可權。

文法

    [ WITH [ RECURSIVE ] with_query [, ...] ]
    INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
        [ OVERRIDING { SYSTEM | USER } VALUE ]
        { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
        [ ON CONFLICT [ conflict_target ] conflict_action ]
        [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

    其中 conflict_target 可以是以下之一：

        ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
        ON CONSTRAINT constraint_name

    並且 conflict_action 是以下之一：

        DO NOTHING
        DO UPDATE SET { column_name = { expression | DEFAULT } |
                        ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                        ( column_name [, ...] ) = ( sub-SELECT )
                      } [, ...]
                  [ WHERE condition ]

參數

插入

• with_query：WITH子句允許指定一個或者更多子查詢，在 INSERT查詢中可以用名稱引用這些子查詢。

  query ：（SELECT語句）也可以包含一個 WITH子句。在這種情況下 query中可以引用兩組 with_query，但是第二個優先順序更高（因為它被嵌套更近）。

• table_name：已有表的名稱（可以被模式限定）。

• alias：table_name 的替補名稱。當提供了別名時，它會完全隱藏掉表的實際名稱。 當ON CONFLICT DO UPDATE的目標是一個被排除的表時這特別有用，因為那將被當作表示要被插入行的特殊表的名稱。

• column_name：名為 table_name的表中的列的名稱。如有必要，列名可以用一個子網域名稱或者數組下標限定（指向一個組合列的某些列中插入會讓其他域為空白）。當用 ON CONFLICT DO UPDATE引用一列時，不要在一個目標列的說明中包括表名。例如， INSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1是非法的（這遵循UPDATE 的一般行為）。

• OVERRIDING SYSTEM VALUE：如果指定了此子句，那麼為識別欄位提供的任何值都將覆蓋預設的序列產生的值。

  對於定義為GENERATED ALWAYS的識別欄位，插入顯式值（DEFAULT除外）而不指定 OVERRIDING SYSTEM VALUE或OVERRIDING USER VALUE是錯誤的。（對於定義為GENERATED BY DEFAULT的識別欄位，OVERRIDING SYSTEM VALUE是正常行為， 並指定其不執行任何操作，但是PolarDB允許它作為副檔名。）

• OVERRIDING USER VALUE：如果指定了此子句，則將忽略為識別欄位提供的任何值，並應用預設的序列產生的值。

  例如，當在表之間拷貝值時，這個子句有能派上用場。INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1將從tbl1中拷貝所有在tbl2中不是識別欄位的列，而tbl2中識別欄位的值將由與tbl2關聯的序列產生。

• DEFAULT VALUES：所有列都將被其預設值填充，就像為每個列顯式指定了DEFAULT。 （例如這種形式下不允許OVERRIDING子句）。

• expression：要賦予相應列的運算式或者值。

• DEFAULT：相應的列將填充其預設值。識別欄位將由關聯序列產生的新值填充。對於產生的列，允許指定該值，但僅指定根據其產生運算式計算該列的正常行為。

• query：提供要被插入行的查詢（SELECT語句）。

• output_expression：在每一行被插入或更新後由INSERT命令計算並且返回的運算式。該運算式可以使用 table_name 指定的表中的任何列。寫成可返回被插入或更新行的所有列。

• output_name：要用於被返回列的名稱。

ON CONFLICT 子句

可選的ON CONFLICT子句為出現唯一性違反或排除約束違反錯誤時提供另一種可供選擇的動作。對於每一個要插入的行，不管是插入進行下去還是由conflict_target指定的一個仲裁者約束或者索引被違反，都會採取可供選擇的conflict_action。ON CONFLICT DO NOTHING簡單地避免插入行。ON CONFLICT DO UPDATE則會更新與要插入的行衝突的已有行。

conflict_target可以執行唯一索引推斷。在執行推斷時，它由一個或者多個index_column_name列或者index_expression運算式以及一個可選的index_predicate構成。所有剛好包含conflict_target指定的列/運算式的table_name唯一索引（不管順序）都會被推斷為（選擇為）仲裁者索引。如果指定了index_predicate，它必須滿足仲裁者索引（也是推斷過程的一個進一步的要求）。這意味著如果有一個滿足其他條件的非部分唯一索引（沒有謂詞的唯一索引）可用，它將被推斷為仲裁者（並且會被ON CONFLICT使用）。如果推斷嘗試不成功，則會發生一個錯誤。

ON CONFLICT DO UPDATE保證一個原子的 INSERT或者 UPDATE結果。在沒有無關錯誤的前提下，這兩種結果之一可以得到保證，即使在很高的並發度也能保證。這也可以被稱作UPSERT — “UPDATE 或 INSERT”。

• conflict_target：通過選擇仲裁者索引來指定哪些行與ON CONFLICT在其上採取可替代動作的行相衝突。要麼執行唯一索引推斷，要麼顯式命名一個約束。對於ON CONFLICT DO NOTHING來說，它對於指定一個conflict_target是可選的。在被省略時，與所有有效約束（以及唯一索引）的衝突都會被處理。對於ON CONFLICT DO UPDATE，必須提供一個conflict_target。

• conflict_action：conflict_action指定一個可替換的ON CONFLICT動作。它可以是DO NOTHING，也可以是一個指定在衝突情況下要被執行的UPDATE動作細節的DO UPDATE子句。ON CONFLICT DO UPDATE中的SET和WHERE子句能夠使用該表的名稱（或者別名）訪問現有的行，並且可以用特殊的被排除表訪問要插入的行。這個動作要求被排除列所在目標表的任何列上的SELECT特權。

注意所有行級BEFORE INSERT觸發器的效果都會反映在被排除值中，因為那些效果可能會讓該行避免被插入。

• index_column_name：table_name列的名稱。它被用來推斷仲裁者索引。它遵循CREATE INDEX格式。這要求 index_column_name 上的SELECT特權。

• index_expression：和index_column_name類似，但是被用來推斷出現在索引定義中的 table_name列（非簡單列）上的運算式。遵循CREATE INDEX格式。這要求任何出現在 index_expression中的列上的 SELECT特權。

• collation：指定後，強制相應的 index_column_name或 index_expression 使用一種特定的定序以便在推斷期間能被匹配上。通常會被省略，因為定序通常不會影響約束違背的發生。遵循 CREATE INDEX格式。

• opclass指定時，強制相應的index_column_name或index_expression使用特定的操作符類以便在推斷期間能被匹配上。通常會被省略，因為相等語義在一種類型的操作符類之間都是等價的，或者因為足以信任已定義的唯一索引具有適當的相等定義。遵循CREATE INDEX格式。

• index_predicate：用於允許推斷部分唯一索引。任何滿足該謂詞（不一定需要真的是部分索引）的索引都能被推斷。遵循CREATE INDEX格式。這要求任何出現在index_predicate中的列上的SELECT特權。

• constraint_name：用名稱顯式地指定仲裁者約束，而不是推斷約束或者索引。

• condition：能返回boolean值的運算式。只有讓這個運算式返回true的行才將被更新，不過在採用ON CONFLICT DO UPDATE動作時所有的行都會被鎖定。

  condition會被最後計算，即一個衝突被標識為要更新的候選對象之後。

輸出

成功完成時，INSERT命令會返回以下形式的命令標籤：

    INSERT oid count

count是被插入或更新的行數。oid總是0（之前，如果count恰好為1，並且目標表被聲明為WITH OIDS，則它是分配給插入行的OID， 否則為0，但現在已不再支援建立WITH OIDS表）。

如果INSERT命令包含RETURNING子句，其結果會類似於包含RETURNING列表中定義的列和值的SELECT語句，這些結果是由該命令在被插入或更新行上計算得到。

說明

如果指定的表是分區表，每一行都會被路由到合適的分區並且插入其中。如果指定的表是分區，如果任意一行輸入違反該分區的約束，則將發生錯誤。

樣本

向films中插入一行：

    INSERT INTO films VALUES
        ('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes');

len列被省略並且因此會具有預設值：

    INSERT INTO films (code, title, did, date_prod, kind)
        VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama');

為日期列使用DEFAULT子句而不是指定一個值：

    INSERT INTO films VALUES
        ('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes');
    INSERT INTO films (code, title, did, date_prod, kind)
        VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama');

插入一個完全由預設值構成的行：

    INSERT INTO films DEFAULT VALUES;

用多行VALUES文法插入多個行：

    INSERT INTO films (code, title, did, date_prod, kind) VALUES
        ('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'),
        ('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy');

從表tmp_films中獲得一些行插入到表 films中，兩個表具有相同的列布局：

    INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';

插入數組列：

    -- 為 noughts-and-crosses 遊戲建立一個空的 3x3 棋盤
    INSERT INTO tictactoe (game, board[1:3][1:3])
        VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
    -- 實際上可以不用上面例子中的下標
    INSERT INTO tictactoe (game, board)
        VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');

向表distributors中插入一行，返回由 DEFAULT子句產生的序號：

    INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
       RETURNING did;

增加為 Acme Corporation 管理賬戶的銷售人員的銷量，並且把整個被更新的行以及目前時間記錄到一個日誌表中：

    WITH upd AS (
      UPDATE employees SET sales_count = sales_count + 1 WHERE id =
        (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation')
        RETURNING *
    )
    INSERT INTO employees_log SELECT *, current_timestamp FROM upd;

酌情插入或者更新新的 distributor。假設已經定義了一個唯一索引來約束出現在did列中的值。注意，特殊的 excluded表被用來引用原來要插入的值：

    INSERT INTO distributors (did, dname)
        VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
        ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;

插入一個 distributor，或者在一個被排除的行（具有一個匹配約束的列或者會讓行級前（或者後）插入觸發器引發的列的行）存在時不處理要插入的行。 例子假設已經定義了一個唯一觸發器來約束出現在did列中的值：

    INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
        ON CONFLICT (did) DO NOTHING;

酌情插入或者更新新的distributor。例子假設已經定義了一個唯一觸發器來約束出現在did列中的值。WHERE子句被用來限制實際被更新的行（不過，任何沒有被更新的已有行仍將被鎖定）：

    -- 根據一個特定的 ZIP 編碼更新 distributors
    INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
        ON CONFLICT (did) DO UPDATE
        SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
        WHERE d.zipcode <> '21201';

    -- 直接在語句中命名一個約束（使用相關的索引來判斷是否做
    -- DO NOTHING 動作）
    INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
        ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

如果可能就插入新的 distributor，否則DO NOTHING。 例子假設已經定義了一個唯一索引，它約束讓is_active 布爾列為true的行子集上did列中的值：

    -- 這個語句可能推斷出一個在 "did" 上帶有謂詞 "WHERE is_active"
    -- 的部分唯一索引，但是它可能也只是使用了 "did" 上的一個常規唯一約束
    INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International')
        ON CONFLICT (did) WHERE is_active DO NOTHING;