PostgreSQL INSERT

描述

INSERT將新行插入到一個(gè)表中。我們可以 插入一個(gè)或者更多由值表達(dá)式指定的行，或者插入來(lái)自一個(gè)查詢的零行 或者更多行。

目標(biāo)列的名稱可以以任意順序列出。如果沒(méi)有給出列名列表，則有兩種確定 目標(biāo)列的可能性。第一種是以被聲明的順序列出該表的所有列。另一種可能 性是，如果VALUES 子句或者query只提 供N個(gè)列，則以被聲明的順序列出該表的前 N列。 VALUES子句或者 query提供的值會(huì)被從左至右關(guān)聯(lián)到這些顯式或者隱式 給出的目標(biāo)列。

每一個(gè)沒(méi)有出現(xiàn)在顯式或者隱式列列表中的列都將被默認(rèn)填充，如果為該列 聲明過(guò)默認(rèn)值則用默認(rèn)值填充，否則用空值填充。

如果任意列的表達(dá)式不是正確的數(shù)據(jù)類型，將會(huì)嘗試自動(dòng)類型轉(zhuǎn)換。

ON CONFLICT可以用來(lái)指定發(fā)生唯一約束或者排除約束 違背錯(cuò)誤時(shí)的替換動(dòng)作（見(jiàn)下文的ON CONFLICT 子句）。

可選的RETURNING子句讓INSERT根據(jù) 實(shí)際被插入（如果使用了ON CONFLICT DO UPDATE子句， 可能是被更新）的每一行來(lái)計(jì)算和返回值。這主要用來(lái)獲取由默認(rèn)值提供 的值，例如一個(gè)序列號(hào)。不過(guò)，允許在其中包括使用該表列的任何表達(dá)式。 RETURNING列表的語(yǔ)法與 SELECT的輸出 列表的相同。只有被成功地插入或者更新的行才將被返回。例如，如果一 行被鎖定但由于不滿足ON CONFLICT DO UPDATE ... WHERE clause condition沒(méi)有被更新，該行將 不被返回。

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

如果一個(gè)列列表被指定，你只需要其中的列上的INSERT 特權(quán)。類似地，在指定了ON CONFLICT DO UPDATE時(shí)，你只 需要被列出要更新的列上的UPDATE特權(quán)。不過(guò)， ON CONFLICT DO UPDATE還要求其值被 ON CONFLICT DO UPDATE表達(dá)式或者 condition 使用的列上的SELECT特權(quán)。

使用RETURNING子句需要RETURNING中提到的所有列的 SELECT權(quán)限。     如果使用query子句從查詢中插入行， 則當(dāng)然需要對(duì)查詢中使用的任何表或列具有SELECT權(quán)限。

參數(shù)

輸出

成功完成時(shí)，INSERT命令會(huì)返回以下形式的命令標(biāo)簽：

INSERT oid count

count是被插入或更新的行數(shù)。 oid總是0（過(guò)去，如果count恰好為1， 并且目標(biāo)表被聲明為WITH OIDS，則它是分配給插入行的OID， 否則為0， 但現(xiàn)在已不再支持創(chuàng)建WITH OIDS表）。

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

注解

如果指定的表是一個(gè)分區(qū)表，每一行都會(huì)被路由到合適的分區(qū)并且插入其中。如果指定的表是一個(gè)分區(qū)，如果輸入行之一違背該分區(qū)的約束則將發(fā)生錯(cuò)誤。

示例

向films中插入一行：

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

在這個(gè)例子中，len列被省略并且因此會(huì)具有默認(rèn)值：

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

這個(gè)例子為日期列使用DEFAULT子句而不是指定一個(gè)值：

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');

插入一個(gè)完全由默認(rèn)值構(gòu)成的行：

INSERT INTO films DEFAULT VALUES;

用多行VALUES語(yǔ)法插入多個(gè)行：

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

這個(gè)例子從表tmp_films中獲得一些行插入到表 films中，兩個(gè)表具有相同的列布局：

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

這個(gè)例子插入數(shù)組列：

-- 為 noughts-and-crosses 游戲創(chuàng)建一個(gè)空的 3x3 棋盤(pán)
INSERT INTO tictactoe (game, board[1:3][1:3])
    VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}');
-- 實(shí)際上可以不用上面例子中的下標(biāo)
INSERT INTO tictactoe (game, board)
    VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}');

向表distributors中插入一行，返回由 DEFAULT子句生成的序號(hào)：

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

增加為 Acme Corporation 管理賬戶的銷售人員的銷量，并且把整個(gè)被 更新的行以及當(dāng)前時(shí)間記錄到一個(gè)日志表中：

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。假設(shè)已經(jīng)定義了一個(gè)唯一索引來(lái)約束 出現(xiàn)在did列中的值。注意，特殊的 excluded表被用來(lái)引用原來(lái)要插入的值：

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

插入一個(gè) distributor，或者在一個(gè)被排除的行（具有一個(gè)匹配約束的列或者 會(huì)讓行級(jí)前（或者后）插入觸發(fā)器引發(fā)的列的行）存在時(shí)不處理要插入的行。 例子假設(shè)已經(jīng)定義了一個(gè)唯一觸發(fā)器來(lái)約束出現(xiàn)在did列 中的值：

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

酌情插入或者更新新的 distributor。例子假設(shè)已經(jīng)定義了一個(gè)唯一觸發(fā)器來(lái) 約束出現(xiàn)在did列中的值。WHERE子句被用 來(lái)限制實(shí)際被更新的行（不過(guò)，任何沒(méi)有被更新的已有行仍將被鎖定）：

-- 根據(jù)一個(gè)特定的 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';

-- 直接在語(yǔ)句中命名一個(gè)約束（使用相關(guān)的索引來(lái)判斷是否做
-- DO NOTHING 動(dòng)作）
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;

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

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

兼容性

INSERT符合 SQL 標(biāo)準(zhǔn)，不過(guò) RETURNING子句是一種 PostgreSQL擴(kuò)展， 在 INSERT中使用WITH也是， 用ON CONFLICT指定一個(gè)替代動(dòng)作也是擴(kuò)展。 還有，標(biāo)準(zhǔn)不允許省略列名列表但不通過(guò) VALUES子句或者query填充 所有列的情況。

SQL標(biāo)準(zhǔn)指定只有存在一個(gè)總是會(huì)生成值的標(biāo)識(shí)列時(shí)才能指定OVERRIDING SYSTEM VALUE。而PostgreSQL在任何情況下都允許這個(gè)子句，并且在不適用時(shí)會(huì)忽略它。

query子句可能的限制在 SELECT有介紹。