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
] [, ...] ] ここでconflict_target
は以下のいずれかです。 ( {index_column_name
| (index_expression
) } [ COLLATEcollation
] [opclass
] [, ...] ) [ WHEREindex_predicate
] ON CONSTRAINTconstraint_name
またconflict_action
は以下のいずれかです。 DO NOTHING DO UPDATE SET {column_name
= {expression
| DEFAULT } | (column_name
[, ...] ) = [ ROW ] ( {expression
| DEFAULT } [, ...] ) | (column_name
[, ...] ) = (sub-SELECT
) } [, ...] [ WHEREcondition
]
INSERT
はテーブルに新しい行を挿入します。
値式を使用して行(複数可)を挿入すること、および、問い合わせの結果を使って0行以上の行を挿入することができます。
対象の列名はどのような順番でも指定できます。
列名リストが指定されなかった場合は、テーブル内の全ての列を宣言時の順番に並べたものがデフォルトとして使われます。
また、VALUES
句やquery
でN
列のみが与えられた場合は、先頭のN
列の名前が指定されたものとみなされます。
VALUES
句やquery
で提供される値は、明示的または暗黙的な列リストと左から右への順で関連付けられます
明示的または暗黙的な列リストにない各列にはデフォルト値(デフォルト値が宣言されていればその値、未宣言ならばNULL)が挿入されます。
各列の式が正しいデータ型でない場合は、自動的に型の変換が行われます。
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
はまた、その式あるいはcondition
で読み取られるすべての列についてのSELECT
権限も必要です。
RETURNING
句を使用するには、RETURNING
で使用するすべての列に対するSELECT
権限が必要です。
query
を使用して問い合わせ結果を元に行を挿入する場合は当然ながら、その問い合わせ内で使われる全てのテーブルまたは列に対してSELECT
権限を持っている必要があります。
この節では新しい行を挿入するときにのみ使われるパラメータについて説明します。
ON CONFLICT
句においてのみ使われるパラメータについては、別に説明します。
with_query
WITH
句により、INSERT
問い合わせ内で名前により参照することができる1つ以上の副問い合わせを指定することができます。
詳しくは7.8とSELECTを参照してください。
query
(SELECT
文)でもまた、WITH
句を含めることができます。
こうした場合、with_query
の集合との両方をquery
内で参照することができます。
しかし、第二の問い合わせがより近くにネストされているため優先します。
table_name
既存のテーブルの名前です(スキーマ修飾名も可)。
alias
table_name
の代替名です。
aliasを指定すると、テーブルの実際の名前が完全に隠されます。
これは、excluded
という名前のテーブルをON CONFLICT DO UPDATE
が対象にしている場合、これを指定しなければ、それが挿入で処理される行を表現する特別なテーブルの名前とみなされるため、特に有用となります。
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
として定義されたIDENTITY列に(DEFAULT
以外の)明示的な値を指定するとエラーになります。
この句はその制限よりも優先します。
OVERRIDING USER VALUE
この句が指定されると、GENERATED BY DEFAULT
として定義されたIDENTITY列について指定された値はすべて無視されて、シーケンスが生成したデフォルト値が適用されます。
この句は例えばテーブル間で値をコピーする時に有用です。
INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1
とすると、tbl1
の列でtbl2
のIDENTITY列でないものがすべてコピーされる一方、tbl2
のIDENTITY列の値は、tbl2
に紐付けられたシーケンスによって生成されます。
DEFAULT VALUES
全ての列に、それぞれのデフォルト値が設定されます。
(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
は一意インデックスの推定を実行することができます。
推定を実行するとき、それは1つ以上のindex_column_name
列、またはindex_expression
式、あるいはその両方、およびオプションでindex_predicate
から構成されます。
table_name
の一意インデックスでconflict_target
で指定された列と式を(順序は関係なく)正確に含むものは、すべて競合解決インデックスとして推定されます(選ばれます)。
index_predicate
が指定されている場合は、推定のさらなる条件として、それは競合解決インデックスを満たさなければなりません。
これは、部分インデックスでない一意インデックス(述語のない一意インデックス)は、それが他のすべての条件を満たすのであれば推定される(従ってON CONFLICT
で使用される)ことを意味することに注意して下さい。
推定に失敗した時は、エラーが発生します。
ON CONFLICT DO UPDATE
はINSERT
またはUPDATE
の原子的な結果を保証します。
無関係のエラーが発生しなければ、多数の同時実行がある状況においてさえも、それら2つの結果のうちの1つになります。
これは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
テーブルを使ってアクセスできます。
excluded
の列を読み取るときには、対象テーブルの対応する列のSELECT
権限が必要です。
すべての行レベルの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
部分一意インデックスの推定を可能にします。
述語を満たすすべてのインデックス(実際に部分インデックスである必要はありません)は推定可能になります。
CREATE INDEX
の形式に従います。
index_predicate
に現れるすべての列についてSELECT
権限が必要です。
constraint_name
競合解決の制約を制約やインデックスの推定によるのではなく、明示的に名前で指定します。
condition
boolean
型の値を返す式です。
この式がtrue
を返す行のみが更新されます。
ただし、ON CONFLICT DO UPDATE
の動作が行われるときは、すべての行がロックされます。
condition
は最後に評価される、競合が更新対象候補として特定された後であることに注意して下さい。
排他制約はON CONFLICT DO UPDATE
の競合解決としてはサポートされないことに注意して下さい。
すべての場合について、NOT DEFERRABLE
である制約と一意インデックスのみが競合解決としてサポートされます。
ON CONFLICT DO UPDATE
句のあるINSERT
は「決定論的な」文です。
これは、そのコマンドが既存のどの行に対しても、2回以上影響を与えることが許されない、ということを意味します。
これに反する状況が発生した時は、カーディナリティ違反のエラーが発生します。
挿入されようとする行は、競合解決インデックスあるいは制約により制限される属性の観点で、複製されてはなりません。
パーティションテーブルに適用されたINSERT
のON CONFLICT DO UPDATE
句に対しては、その行を新しいパーティションに移動する必要のあるような競合する行のパーティションキーを更新することは現在サポートされていないことに注意してください。
ON CONFLICT ON CONSTRAINT
constraint_name
を使って制約を直接指定するより、一意インデックスの推定を使う方が望ましいことが多いです。
背景にあるインデックスが、他のほぼ同等のインデックスと重なり合う形で置換されるとき、推定は正しく動作し続けます。
例えば、置換されるインデックスを削除する前にCREATE UNIQUE INDEX ... CONCURRENTLY
を使う場合です。
正常に終了すると、INSERT
は以下のようなコマンドタグを返します。
INSERToid
count
count
は挿入または更新された行数です。
count
が正確に1であり、対象のテーブルがOIDを持つ場合、oid
は挿入された行に割り当てられたOIDです。
その1行は、更新ではなく挿入された行です。
その他の場合、oid
は0となります。
INSERT
コマンドがRETURNING
句を持つ場合、その結果は、RETURNING
リストで定義した列と値を持ち、そのコマンドで挿入または更新された行全体に対して計算を行うSELECT
文の結果と似たものになるでしょう。
指定したテーブルがパーティションテーブルの場合、各行は適切なパーティションに回され、そちらに挿入されます。 指定したテーブルがパーティションの場合、挿入行にパーティションの制約に違反するものがあれば、エラーが発生します。
films
テーブルに1行を挿入します。
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';
次の例では、配列型の列に挿入します。
-- 三目並べ用の3×3マスのゲーム盤を作成します。 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社の顧客を担当する営業担当者の売り上げ数を増やし、ログテーブルに更新行全体と更新時刻を記録します。
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;
新しい販売店(distributors)を適切に挿入または更新します。
did
列に現れる値を制限する一意インデックスが定義されているものとします。
元々挿入されようとしていた値を参照するために、特別なexcluded
テーブルが使用されていることに注意して下さい。
INSERT INTO distributors (did, dname) VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
販売店を挿入するか、あるいは挿入しようとした行について既存の除外行(before insertの行トリガを実行した後で制約列にマッチした行)がある場合は何もしません。
例ではdid
列に現れる値を制限する一意インデックスがあるものとしています。
INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') ON CONFLICT (did) DO NOTHING;
新しい販売店を適切に挿入または更新します。
例ではdid
列に現れる値を制限する一意インデックスがあるものとしています。
実際に更新される行を制限するためにWHERE
句が使われています(ただし、更新されない既存の行もすべてロックされます)。
-- 特定の郵便番号については既存の販売店を更新しません 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;
可能であれば新しい販売店を挿入しますが、できないときはDO NOTHING
とします。
この例では、is_active
というブーリアン列がtrue
である行という条件で、did
列に一意インデックスが定義されているものとしています。
-- この文は"WHERE is_active"という述語を使って、部分インデックスを -- 推定できますが、単に"did"上の通常の一意制約を使うこともできます INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') ON CONFLICT (did) WHERE is_active DO NOTHING;
INSERT
は標準SQLに準拠します。
ただし、RETURNING
句、INSERT
でWITH
が可能であること、ON CONFLICT
で代替の動作を指定できることはPostgreSQLの拡張です。
また、標準SQLでは、列名リストが省略された時に、VALUES
句またはquery
で一部の列のみを指定することはできません。
標準SQLでは、必ず値を生成するIDENTITY列が存在する場合にのみOVERRIDING SYSTEM VALUE
を指定できるとしています。
PostgreSQLではこの句はどのような場合でも指定でき、それが適用できないときには無視します。
query
句の制限については、SELECTにて記述されています。