INSERT — 在資料表中建立新的資料列
[ WITH [ RECURSIVE ]with_query
[, ...] ] INSERT INTOtable_name
[ ASalias
] [ (column_name
[, ...] ) ] [ OVERRIDING { SYSTEM | USER } VALUE ] { DEFAULT VALUES | VALUES ( {expression
| DEFAULT } [, ...] ) [, ...] |query
} [ ON CONFLICT [conflict_target
]conflict_action
] [ RETURNING { * |output_expression
[ [ AS ]output_name
] } [, ...] ] whereconflict_target
can be one of: ( {index_column_name
| (index_expression
) } [ COLLATEcollation
] [opclass
] [, ...] ) [ WHEREindex_predicate
] ON CONSTRAINTconstraint_name
andconflict_action
is one of: DO NOTHING DO UPDATE SET {column_name
= {expression
| DEFAULT } | (column_name
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (column_name
[, ...] ) = (sub-SELECT
) } [, ...] [ WHEREcondition
]
INSERT
會將新的資料列插入資料表中。 可以插入由值運算式指定的一或多個資料列,或插入由查詢產生零或多個資料列。
目標欄位名稱可以依任何順序排列。 如果完全沒有提供欄位名稱清單,則預設為資料表中所有欄位依其宣告的順序排列;或者,如果 VALUES
子句或 query
僅提供 N
個欄位,則為前 N
個欄位名稱。 由 VALUES
子句或 query
提供的值會由左至右與明確或隱含的欄位清單相關聯。
未出現在明確或隱含欄位清單中的每個欄位都將填入預設值,可以是其宣告的預設值,如果沒有則為 null。
如果任何欄位的運算式不是正確的資料類型,將會嘗試自動類型轉換。
對缺少唯一索引的資料表執行 INSERT
,不會因並行活動而受到阻擋。 如果並行工作階段執行鎖定或修改符合要插入的唯一索引值的資料列的動作,則具有唯一索引的資料表可能會受到阻擋;詳細資訊請參閱 第 62.5 節。ON CONFLICT
可用於指定替代動作,以避免引發唯一約束或排除約束違規錯誤。(請參閱下方的 ON CONFLICT 子句。)
選用的 RETURNING
子句會使 INSERT
根據實際插入(或更新,如果使用了 ON CONFLICT DO UPDATE
子句)的每個資料列來計算並傳回值。 這主要用於取得由預設值提供的值,例如序列編號。 但是,允許使用資料表欄位的任何運算式。RETURNING
清單的語法與 SELECT
的輸出清單相同。 只會傳回成功插入或更新的資料列。 例如,如果由於未滿足 ON CONFLICT DO UPDATE ... WHERE
子句 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
權限。
本節介紹僅在插入新資料列時可能使用的參數。 與 ON CONFLICT
子句專門使用的參數將單獨說明。
with_query
WITH
子句允許您指定一或多個可在 INSERT
查詢中按名稱參照的子查詢。 有關詳細資訊,請參閱 第 7.8 節和 SELECT。
query
(SELECT
陳述式) 也可能包含 WITH
子句。 在這種情況下,可以在 query
中參照兩組 with_query
,但第二組優先,因為它更緊密地嵌套。
table_name
現有資料表的名稱(可選擇使用結構描述限定)。
alias
table_name
的替代名稱。 提供別名時,它會完全隱藏資料表的實際名稱。 當 ON CONFLICT DO UPDATE
以名為 excluded
的資料表為目標時,這尤其有用,因為否則會將其視為表示建議插入的資料列的特殊資料表的名稱。
column_name
由 table_name
命名的資料表中的欄位名稱。 如果需要,欄位名稱可以用子欄位名稱或陣列下標限定。(僅插入複合欄位的部分欄位會將其他欄位保留為 null。) 在使用 ON CONFLICT DO UPDATE
參照欄位時,請勿在目標欄位的規格中包含資料表的名稱。 例如,INSERT INTO table_name ... ON CONFLICT DO UPDATE SET table_name.col = 1
無效(這遵循 UPDATE
的一般行為)。
OVERRIDING SYSTEM VALUE
如果指定了此子句,則為識別欄位提供的任何值都將覆蓋預設的序列產生值。
對於定義為 GENERATED ALWAYS
的身分欄位,若未指定 OVERRIDING SYSTEM VALUE
或 OVERRIDING USER VALUE
,則插入明確的值(除了 DEFAULT
之外)會發生錯誤。(對於定義為 GENERATED BY DEFAULT
的身分欄位,OVERRIDING SYSTEM VALUE
是正常的行為,指定它沒有任何作用,但 PostgreSQL 允許它作為一種延伸。)
OVERRIDING USER VALUE
如果指定了此子句,則將忽略為身分欄位提供的任何值,並應用預設的序列產生值。
例如,在表格之間複製值時,此子句很有用。寫入 INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1
將從 tbl1
複製所有不是 tbl2
中身分欄位的欄位,而 tbl2
中身分欄位的值將由與 tbl2
關聯的序列產生。
DEFAULT VALUES
所有欄位都將填入其預設值,如同明確指定每個欄位的 DEFAULT
一樣。(在此形式中不允許使用 OVERRIDING
子句。)
expression
要指定給相應欄位的表達式或值。
DEFAULT
相應的欄位將填入其預設值。身分欄位將填入由關聯的序列產生的一個新值。對於產生的欄位,允許指定此值,但僅僅指定了從其產生表達式計算欄位的正常行為。
query
一個查詢(SELECT
語句),提供要插入的列。 有關語法的描述,請參閱 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
組成。 所有 table_name
唯一索引,不考慮順序,恰好包含 conflict_target
指定的欄位/表達式,都會被推斷(選擇)為仲裁者索引。 如果指定了 index_predicate
,則作為推斷的進一步要求,它必須滿足仲裁者索引。 請注意,這意味著如果存在滿足所有其他標準的非部分唯一索引(沒有謂詞的唯一索引),則將推斷該索引(因此 ON CONFLICT
將使用該索引)。 如果推斷嘗試失敗,則會引發錯誤。
ON CONFLICT DO UPDATE
保證原子性的 INSERT
或 UPDATE
結果; 如果沒有獨立的錯誤,則保證這兩種結果之一,即使在高並發下也是如此。 這也稱為 UPSERT — “UPDATE or 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
,也可以是 DO UPDATE
子句,用於指定在發生衝突時要執行的 UPDATE
操作的確切細節。 ON CONFLICT DO UPDATE
中的 SET
和 WHERE
子句可以使用表格的名稱(或別名)訪問現有列,並使用特殊的 excluded
表訪問建議插入的列。 需要對目標表格中的任何欄位具有 SELECT
權限,在這些欄位中讀取了相應的 excluded
欄位。
請注意,所有逐列的 BEFORE INSERT
觸發器的效果都反映在 excluded
值中,因為這些效果可能導致該列被排除在插入之外。
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
用於允許推斷部分唯一索引。任何滿足謂詞(predicate)(不需要是實際的部分索引)的索引都可以被推斷。遵循 CREATE INDEX
格式。需要對 index_predicate
中出現的任何欄位具有 SELECT
權限。
constraint_name
明確指定一個仲裁 constraint 的名稱,而不是推斷一個約束或索引。
condition
一個返回 boolean
類型值的表達式。只有該表達式返回 true
的列才會被更新,儘管在執行 ON CONFLICT DO UPDATE
動作時,所有列都會被鎖定。請注意,condition
會在衝突被識別為更新候選者之後最後評估。
請注意,排除約束(exclusion constraints)不支援作為 ON CONFLICT DO UPDATE
的仲裁者。在所有情況下,只有 NOT DEFERRABLE
約束和唯一索引支援作為仲裁者。
帶有 ON CONFLICT DO UPDATE
子句的 INSERT
是一個「確定性的」語句。這表示該命令不允許對任何單一現有列產生一次以上的影響;當發生這種情況時,會引發基數違規(cardinality violation)錯誤。 建議插入的列在仲裁索引或約束所約束的屬性方面不應彼此重複。
請注意,目前不支援將 INSERT
的 ON CONFLICT DO UPDATE
子句應用於分割資料表,以更新衝突列的分割鍵,使其需要將列移動到新的分割區。
通常最好使用唯一索引推斷,而不是使用 ON CONFLICT ON CONSTRAINT
constraint_name
直接命名約束。當基礎索引被另一個或多或少等效的索引以重疊的方式取代時,推斷將繼續正確地工作,例如在使用 CREATE UNIQUE INDEX ... CONCURRENTLY
之前刪除被取代的索引。
成功完成後,INSERT
命令會傳回一個格式如下的命令標籤:
INSERToid
count
count
是插入或更新的列數。oid
始終為 0(以前是OID如果 count
恰好為一,且目標資料表宣告為 WITH OIDS
,則分配給插入列的 OID;否則為 0,但不再支援建立 WITH OIDS
的資料表)。
如果 INSERT
命令包含 RETURNING
子句,則結果將類似於包含 RETURNING
列表中定義的欄位和值的 SELECT
語句的結果,這些欄位和值是透過該命令插入或更新的列計算得出的。
如果指定的資料表是一個分割資料表,則每一列都會被路由到相應的分割區並插入其中。如果指定的資料表是一個分割區,如果其中一個輸入列違反了分割區約束,則會發生錯誤。
您可能還想考慮使用 MERGE
,因為它允許在單一語句中混合 INSERT
、UPDATE
和 DELETE
。請參閱 MERGE。
將單一列插入到資料表 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');
此範例將一些列從與 films
具有相同欄位配置的資料表 tmp_films
插入到資料表 films
中
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07';
此範例插入到陣列欄位中
-- Create an empty 3x3 gameboard for noughts-and-crosses INSERT INTO tictactoe (game, board[1:3][1:3]) VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}'); -- The subscripts in the above example aren't really needed 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;
適當地插入或更新新的經銷商。假設已定義一個唯一索引,該索引約束了 did
欄位中出現的值。請注意,特殊的 excluded
資料表用於參照最初建議插入的值
INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
插入一個經銷商,或者對於在現有的、排除的列(在觸發列插入觸發器之前,具有匹配的受約束欄位或列的列)存在時建議插入的列,不執行任何操作。範例假設已定義一個唯一索引,該索引約束了 did
欄位中出現的值
INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') ON CONFLICT (did) DO NOTHING;
適當地插入或更新新的經銷商。範例假設已定義一個唯一索引,該索引約束了 did
欄位中出現的值。WHERE
子句用於限制實際更新的列(儘管如此,任何未更新的現有列仍將被鎖定)
-- Don't update existing distributors based in a certain ZIP code 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'; -- Name a constraint directly in the statement (uses associated -- index to arbitrate taking the DO NOTHING action) INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;
如果可能,插入新的經銷商;否則 DO NOTHING
。範例假設已定義一個唯一索引,該索引約束了在 is_active
Boolean 欄位評估為 true
的列子集中,did
欄位中出現的值
-- This statement could infer a partial unique index on "did" -- with a predicate of "WHERE is_active", but it could also -- just use a regular unique constraint on "did" INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') ON CONFLICT (did) WHERE is_active DO NOTHING;
INSERT
符合 SQL 標準,但 RETURNING
子句是 PostgreSQL 擴充功能,使用 WITH
與 INSERT
的能力,以及使用 ON CONFLICT
指定替代動作的能力也是如此。此外,省略欄位名稱清單,但並非所有欄位都從 VALUES
子句或 query
填充的情況,標準是不允許的。如果您更喜歡比 ON CONFLICT
更符合 SQL 標準的語句,請參閱 MERGE。
SQL 標準指定只有在始終產生的識別欄位存在時,才能指定 OVERRIDING SYSTEM VALUE
。PostgreSQL 允許在任何情況下使用該子句,如果它不適用,則會忽略它。
query
子句的可能限制記錄在 SELECT 下。
如果您在文件中發現任何不正確、與您使用特定功能的經驗不符或需要進一步澄清的地方,請使用此表單來報告文件問題。