| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/insert.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-insert"> |
| 7 |
<indexterm zone="sql-insert"> |
| 8 |
<primary>INSERT</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>INSERT</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>INSERT</refname> |
| 19 |
<refpurpose>create new rows in a table</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 24 |
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] |
| 25 |
INSERT INTO <replaceable class="parameter">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ] [ ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ] |
| 26 |
[ OVERRIDING { SYSTEM | USER } VALUE ] |
| 27 |
{ DEFAULT VALUES | VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="parameter">query</replaceable> } |
| 28 |
[ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ] |
| 29 |
[ RETURNING [ WITH ( { OLD | NEW } AS <replaceable class="parameter">output_alias</replaceable> [, ...] ) ] |
| 30 |
{ * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] } [, ...] ] |
| 31 |
|
| 32 |
<phrase>where <replaceable class="parameter">conflict_target</replaceable> can be one of:</phrase> |
| 33 |
|
| 34 |
( { <replaceable class="parameter">index_column_name</replaceable> | ( <replaceable class="parameter">index_expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) [ WHERE <replaceable class="parameter">index_predicate</replaceable> ] |
| 35 |
ON CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> |
| 36 |
|
| 37 |
<phrase>and <replaceable class="parameter">conflict_action</replaceable> is one of:</phrase> |
| 38 |
|
| 39 |
DO NOTHING |
| 40 |
DO UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | |
| 41 |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = [ ROW ] ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | |
| 42 |
( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( <replaceable class="parameter">sub-SELECT</replaceable> ) |
| 43 |
} [, ...] |
| 44 |
[ WHERE <replaceable class="parameter">condition</replaceable> ] |
| 45 |
</synopsis> |
| 46 |
</refsynopsisdiv> |
| 47 |
|
| 48 |
<refsect1> |
| 49 |
<title>Description</title> |
| 50 |
|
| 51 |
<para> |
| 52 |
<command>INSERT</command> inserts new rows into a table. |
| 53 |
One can insert one or more rows specified by value expressions, |
| 54 |
or zero or more rows resulting from a query. |
| 55 |
</para> |
| 56 |
|
| 57 |
<para> |
| 58 |
The target column names can be listed in any order. If no list of |
| 59 |
column names is given at all, the default is all the columns of the |
| 60 |
table in their declared order; or the first <replaceable>N</replaceable> column |
| 61 |
names, if there are only <replaceable>N</replaceable> columns supplied by the |
| 62 |
<literal>VALUES</literal> clause or <replaceable>query</replaceable>. The values |
| 63 |
supplied by the <literal>VALUES</literal> clause or <replaceable>query</replaceable> are |
| 64 |
associated with the explicit or implicit column list left-to-right. |
| 65 |
</para> |
| 66 |
|
| 67 |
<para> |
| 68 |
Each column not present in the explicit or implicit column list will be |
| 69 |
filled with a default value, either its declared default value |
| 70 |
or null if there is none. |
| 71 |
</para> |
| 72 |
|
| 73 |
<para> |
| 74 |
If the expression for any column is not of the correct data type, |
| 75 |
automatic type conversion will be attempted. |
| 76 |
</para> |
| 77 |
|
| 78 |
<para> |
| 79 |
<command>INSERT</command> into tables that lack unique indexes will |
| 80 |
not be blocked by concurrent activity. Tables with unique indexes |
| 81 |
might block if concurrent sessions perform actions that lock or modify |
| 82 |
rows matching the unique index values being inserted; the details |
| 83 |
are covered in <xref linkend="index-unique-checks"/>. |
| 84 |
<literal>ON CONFLICT</literal> can be used to specify an alternative |
| 85 |
action to raising a unique constraint or exclusion constraint |
| 86 |
violation error. (See <xref linkend="sql-on-conflict"/> below.) |
| 87 |
</para> |
| 88 |
|
| 89 |
<para> |
| 90 |
The optional <literal>RETURNING</literal> clause causes <command>INSERT</command> |
| 91 |
to compute and return value(s) based on each row actually inserted |
| 92 |
(or updated, if an <literal>ON CONFLICT DO UPDATE</literal> clause was |
| 93 |
used). This is primarily useful for obtaining values that were |
| 94 |
supplied by defaults, such as a serial sequence number. However, |
| 95 |
any expression using the table's columns is allowed. The syntax of |
| 96 |
the <literal>RETURNING</literal> list is identical to that of the output |
| 97 |
list of <command>SELECT</command>. Only rows that were successfully |
| 98 |
inserted or updated will be returned. For example, if a row was |
| 99 |
locked but not updated because an <literal>ON CONFLICT DO UPDATE |
| 100 |
... WHERE</literal> clause <replaceable |
| 101 |
class="parameter">condition</replaceable> was not satisfied, the |
| 102 |
row will not be returned. |
| 103 |
</para> |
| 104 |
|
| 105 |
<para> |
| 106 |
You must have <literal>INSERT</literal> privilege on a table in |
| 107 |
order to insert into it. If <literal>ON CONFLICT DO UPDATE</literal> is |
| 108 |
present, <literal>UPDATE</literal> privilege on the table is also |
| 109 |
required. |
| 110 |
</para> |
| 111 |
|
| 112 |
<para> |
| 113 |
If a column list is specified, you only need |
| 114 |
<literal>INSERT</literal> privilege on the listed columns. |
| 115 |
Similarly, when <literal>ON CONFLICT DO UPDATE</literal> is specified, you |
| 116 |
only need <literal>UPDATE</literal> privilege on the column(s) that are |
| 117 |
listed to be updated. However, all forms of <literal>ON CONFLICT</literal> |
| 118 |
also require <literal>SELECT</literal> privilege on any column whose values |
| 119 |
are read. This includes any column mentioned in |
| 120 |
<replaceable>conflict_target</replaceable> (including columns referred to |
| 121 |
by the arbiter constraint), and any column mentioned in an |
| 122 |
<literal>ON CONFLICT DO UPDATE</literal> <replaceable>expression</replaceable>, |
| 123 |
or a <literal>WHERE</literal> clause <replaceable>condition</replaceable>. |
| 124 |
</para> |
| 125 |
|
| 126 |
<para> |
| 127 |
Use of the <literal>RETURNING</literal> clause requires <literal>SELECT</literal> |
| 128 |
privilege on all columns mentioned in <literal>RETURNING</literal>. |
| 129 |
If you use the <replaceable |
| 130 |
class="parameter">query</replaceable> clause to insert rows from a |
| 131 |
query, you of course need to have <literal>SELECT</literal> privilege on |
| 132 |
any table or column used in the query. |
| 133 |
</para> |
| 134 |
</refsect1> |
| 135 |
|
| 136 |
<refsect1> |
| 137 |
<title>Parameters</title> |
| 138 |
|
| 139 |
<refsect2> |
| 140 |
<title>Inserting</title> |
| 141 |
|
| 142 |
<para> |
| 143 |
This section covers parameters that may be used when only |
| 144 |
inserting new rows. Parameters <emphasis>exclusively</emphasis> |
| 145 |
used with the <literal>ON CONFLICT</literal> clause are described |
| 146 |
separately. |
| 147 |
</para> |
| 148 |
|
| 149 |
<variablelist> |
| 150 |
<varlistentry> |
| 151 |
<term><replaceable class="parameter">with_query</replaceable></term> |
| 152 |
<listitem> |
| 153 |
<para> |
| 154 |
The <literal>WITH</literal> clause allows you to specify one or more |
| 155 |
subqueries that can be referenced by name in the <command>INSERT</command> |
| 156 |
query. See <xref linkend="queries-with"/> and <xref linkend="sql-select"/> |
| 157 |
for details. |
| 158 |
</para> |
| 159 |
<para> |
| 160 |
It is possible for the <replaceable class="parameter">query</replaceable> |
| 161 |
(<command>SELECT</command> statement) |
| 162 |
to also contain a <literal>WITH</literal> clause. In such a case both |
| 163 |
sets of <replaceable>with_query</replaceable> can be referenced within |
| 164 |
the <replaceable class="parameter">query</replaceable>, but the |
| 165 |
second one takes precedence since it is more closely nested. |
| 166 |
</para> |
| 167 |
</listitem> |
| 168 |
</varlistentry> |
| 169 |
|
| 170 |
<varlistentry> |
| 171 |
<term><replaceable class="parameter">table_name</replaceable></term> |
| 172 |
<listitem> |
| 173 |
<para> |
| 174 |
The name (optionally schema-qualified) of an existing table. |
| 175 |
</para> |
| 176 |
</listitem> |
| 177 |
</varlistentry> |
| 178 |
|
| 179 |
<varlistentry> |
| 180 |
<term><replaceable class="parameter">alias</replaceable></term> |
| 181 |
<listitem> |
| 182 |
<para> |
| 183 |
A substitute name for <replaceable |
| 184 |
class="parameter">table_name</replaceable>. When an alias is |
| 185 |
provided, it completely hides the actual name of the table. |
| 186 |
This is particularly useful when <literal>ON CONFLICT DO UPDATE</literal> |
| 187 |
targets a table named <varname>excluded</varname>, since that will otherwise |
| 188 |
be taken as the name of the special table representing the row proposed |
| 189 |
for insertion. |
| 190 |
</para> |
| 191 |
</listitem> |
| 192 |
</varlistentry> |
| 193 |
|
| 194 |
|
| 195 |
<varlistentry> |
| 196 |
<term><replaceable class="parameter">column_name</replaceable></term> |
| 197 |
<listitem> |
| 198 |
<para> |
| 199 |
The name of a column in the table named by <replaceable |
| 200 |
class="parameter">table_name</replaceable>. The column name |
| 201 |
can be qualified with a subfield name or array subscript, if |
| 202 |
needed. (Inserting into only some fields of a composite |
| 203 |
column leaves the other fields null.) When referencing a |
| 204 |
column with <literal>ON CONFLICT DO UPDATE</literal>, do not include |
| 205 |
the table's name in the specification of a target column. For |
| 206 |
example, <literal>INSERT INTO table_name ... ON CONFLICT DO UPDATE |
| 207 |
SET table_name.col = 1</literal> is invalid (this follows the general |
| 208 |
behavior for <command>UPDATE</command>). |
| 209 |
</para> |
| 210 |
</listitem> |
| 211 |
</varlistentry> |
| 212 |
|
| 213 |
<varlistentry> |
| 214 |
<term><literal>OVERRIDING SYSTEM VALUE</literal></term> |
| 215 |
<listitem> |
| 216 |
<para> |
| 217 |
If this clause is specified, then any values supplied for identity |
| 218 |
columns will override the default sequence-generated values. |
| 219 |
</para> |
| 220 |
|
| 221 |
<para> |
| 222 |
For an identity column defined as <literal>GENERATED ALWAYS</literal>, |
| 223 |
it is an error to insert an explicit value (other than |
| 224 |
<literal>DEFAULT</literal>) without specifying either |
| 225 |
<literal>OVERRIDING SYSTEM VALUE</literal> or <literal>OVERRIDING USER |
| 226 |
VALUE</literal>. (For an identity column defined as |
| 227 |
<literal>GENERATED BY DEFAULT</literal>, <literal>OVERRIDING SYSTEM |
| 228 |
VALUE</literal> is the normal behavior and specifying it does nothing, |
| 229 |
but <productname>PostgreSQL</productname> allows it as an extension.) |
| 230 |
</para> |
| 231 |
</listitem> |
| 232 |
</varlistentry> |
| 233 |
|
| 234 |
<varlistentry> |
| 235 |
<term><literal>OVERRIDING USER VALUE</literal></term> |
| 236 |
<listitem> |
| 237 |
<para> |
| 238 |
If this clause is specified, then any values supplied for identity |
| 239 |
columns are ignored and the default sequence-generated values are |
| 240 |
applied. |
| 241 |
</para> |
| 242 |
|
| 243 |
<para> |
| 244 |
This clause is useful for example when copying values between tables. |
| 245 |
Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM |
| 246 |
tbl1</literal> will copy from <literal>tbl1</literal> all columns that |
| 247 |
are not identity columns in <literal>tbl2</literal> while values for |
| 248 |
the identity columns in <literal>tbl2</literal> will be generated by |
| 249 |
the sequences associated with <literal>tbl2</literal>. |
| 250 |
</para> |
| 251 |
</listitem> |
| 252 |
</varlistentry> |
| 253 |
|
| 254 |
<varlistentry> |
| 255 |
<term><literal>DEFAULT VALUES</literal></term> |
| 256 |
<listitem> |
| 257 |
<para> |
| 258 |
All columns will be filled with their default values, as if |
| 259 |
<literal>DEFAULT</literal> were explicitly specified for each column. |
| 260 |
(An <literal>OVERRIDING</literal> clause is not permitted in this |
| 261 |
form.) |
| 262 |
</para> |
| 263 |
</listitem> |
| 264 |
</varlistentry> |
| 265 |
|
| 266 |
<varlistentry> |
| 267 |
<term><replaceable class="parameter">expression</replaceable></term> |
| 268 |
<listitem> |
| 269 |
<para> |
| 270 |
An expression or value to assign to the corresponding column. |
| 271 |
</para> |
| 272 |
</listitem> |
| 273 |
</varlistentry> |
| 274 |
|
| 275 |
<varlistentry> |
| 276 |
<term><literal>DEFAULT</literal></term> |
| 277 |
<listitem> |
| 278 |
<para> |
| 279 |
The corresponding column will be filled with its default value. An |
| 280 |
identity column will be filled with a new value generated by the |
| 281 |
associated sequence. For a generated column, specifying this is |
| 282 |
permitted but merely specifies the normal behavior of computing the |
| 283 |
column from its generation expression. |
| 284 |
</para> |
| 285 |
</listitem> |
| 286 |
</varlistentry> |
| 287 |
|
| 288 |
<varlistentry> |
| 289 |
<term><replaceable class="parameter">query</replaceable></term> |
| 290 |
<listitem> |
| 291 |
<para> |
| 292 |
A query (<command>SELECT</command> statement) that supplies the |
| 293 |
rows to be inserted. Refer to the |
| 294 |
<xref linkend="sql-select"/> |
| 295 |
statement for a description of the syntax. |
| 296 |
</para> |
| 297 |
</listitem> |
| 298 |
</varlistentry> |
| 299 |
|
| 300 |
<varlistentry> |
| 301 |
<term><replaceable class="parameter">output_alias</replaceable></term> |
| 302 |
<listitem> |
| 303 |
<para> |
| 304 |
An optional substitute name for <literal>OLD</literal> or |
| 305 |
<literal>NEW</literal> rows in the <literal>RETURNING</literal> list. |
| 306 |
</para> |
| 307 |
|
| 308 |
<para> |
| 309 |
By default, old values from the target table can be returned by writing |
| 310 |
<literal>OLD.<replaceable class="parameter">column_name</replaceable></literal> |
| 311 |
or <literal>OLD.*</literal>, and new values can be returned by writing |
| 312 |
<literal>NEW.<replaceable class="parameter">column_name</replaceable></literal> |
| 313 |
or <literal>NEW.*</literal>. When an alias is provided, these names are |
| 314 |
hidden and the old or new rows must be referred to using the alias. |
| 315 |
For example <literal>RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*</literal>. |
| 316 |
</para> |
| 317 |
</listitem> |
| 318 |
</varlistentry> |
| 319 |
|
| 320 |
<varlistentry> |
| 321 |
<term><replaceable class="parameter">output_expression</replaceable></term> |
| 322 |
<listitem> |
| 323 |
<para> |
| 324 |
An expression to be computed and returned by the |
| 325 |
<command>INSERT</command> command after each row is inserted or |
| 326 |
updated. The expression can use any column names of the table |
| 327 |
named by <replaceable |
| 328 |
class="parameter">table_name</replaceable>. Write |
| 329 |
<literal>*</literal> to return all columns of the inserted or updated |
| 330 |
row(s). |
| 331 |
</para> |
| 332 |
|
| 333 |
<para> |
| 334 |
A column name or <literal>*</literal> may be qualified using |
| 335 |
<literal>OLD</literal> or <literal>NEW</literal>, or the corresponding |
| 336 |
<replaceable class="parameter">output_alias</replaceable> for |
| 337 |
<literal>OLD</literal> or <literal>NEW</literal>, to cause old or new |
| 338 |
values to be returned. An unqualified column name, or |
| 339 |
<literal>*</literal>, or a column name or <literal>*</literal> |
| 340 |
qualified using the target table name or alias will return new values. |
| 341 |
</para> |
| 342 |
|
| 343 |
<para> |
| 344 |
For a simple <command>INSERT</command>, all old values will be |
| 345 |
<literal>NULL</literal>. However, for an <command>INSERT</command> |
| 346 |
with an <literal>ON CONFLICT DO UPDATE</literal> clause, the old |
| 347 |
values may be non-<literal>NULL</literal>. |
| 348 |
</para> |
| 349 |
</listitem> |
| 350 |
</varlistentry> |
| 351 |
|
| 352 |
<varlistentry> |
| 353 |
<term><replaceable class="parameter">output_name</replaceable></term> |
| 354 |
<listitem> |
| 355 |
<para> |
| 356 |
A name to use for a returned column. |
| 357 |
</para> |
| 358 |
</listitem> |
| 359 |
</varlistentry> |
| 360 |
</variablelist> |
| 361 |
</refsect2> |
| 362 |
|
| 363 |
<refsect2 id="sql-on-conflict" xreflabel="ON CONFLICT Clause"> |
| 364 |
<title><literal>ON CONFLICT</literal> Clause</title> |
| 365 |
<indexterm zone="sql-insert"> |
| 366 |
<primary>UPSERT</primary> |
| 367 |
</indexterm> |
| 368 |
<indexterm zone="sql-insert"> |
| 369 |
<primary>ON CONFLICT</primary> |
| 370 |
</indexterm> |
| 371 |
<para> |
| 372 |
The optional <literal>ON CONFLICT</literal> clause specifies an |
| 373 |
alternative action to raising a unique violation or exclusion |
| 374 |
constraint violation error. For each individual row proposed for |
| 375 |
insertion, either the insertion proceeds, or, if an |
| 376 |
<emphasis>arbiter</emphasis> constraint or index specified by |
| 377 |
<parameter>conflict_target</parameter> is violated, the |
| 378 |
alternative <parameter>conflict_action</parameter> is taken. |
| 379 |
<literal>ON CONFLICT DO NOTHING</literal> simply avoids inserting |
| 380 |
a row as its alternative action. <literal>ON CONFLICT DO |
| 381 |
UPDATE</literal> updates the existing row that conflicts with the |
| 382 |
row proposed for insertion as its alternative action. |
| 383 |
</para> |
| 384 |
|
| 385 |
<para> |
| 386 |
<parameter>conflict_target</parameter> can perform |
| 387 |
<emphasis>unique index inference</emphasis>. When performing |
| 388 |
inference, it consists of one or more <replaceable |
| 389 |
class="parameter">index_column_name</replaceable> columns and/or |
| 390 |
<replaceable class="parameter">index_expression</replaceable> |
| 391 |
expressions, and an optional <replaceable class="parameter">index_predicate</replaceable>. All <replaceable |
| 392 |
class="parameter">table_name</replaceable> unique indexes that, |
| 393 |
without regard to order, contain exactly the |
| 394 |
<parameter>conflict_target</parameter>-specified |
| 395 |
columns/expressions are inferred (chosen) as arbiter indexes. If |
| 396 |
an <replaceable class="parameter">index_predicate</replaceable> is |
| 397 |
specified, it must, as a further requirement for inference, |
| 398 |
satisfy arbiter indexes. Note that this means a non-partial |
| 399 |
unique index (a unique index without a predicate) will be inferred |
| 400 |
(and thus used by <literal>ON CONFLICT</literal>) if such an index |
| 401 |
satisfying every other criteria is available. If an attempt at |
| 402 |
inference is unsuccessful, an error is raised. |
| 403 |
</para> |
| 404 |
|
| 405 |
<para> |
| 406 |
<literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic |
| 407 |
<command>INSERT</command> or <command>UPDATE</command> outcome; |
| 408 |
provided there is no independent error, one of those two outcomes |
| 409 |
is guaranteed, even under high concurrency. This is also known as |
| 410 |
<firstterm>UPSERT</firstterm> — <quote>UPDATE or |
| 411 |
INSERT</quote>. |
| 412 |
</para> |
| 413 |
|
| 414 |
<variablelist> |
| 415 |
<varlistentry> |
| 416 |
<term><replaceable class="parameter">conflict_target</replaceable></term> |
| 417 |
<listitem> |
| 418 |
<para> |
| 419 |
Specifies which conflicts <literal>ON CONFLICT</literal> takes |
| 420 |
the alternative action on by choosing <firstterm>arbiter |
| 421 |
indexes</firstterm>. Either performs <emphasis>unique index |
| 422 |
inference</emphasis>, or names a constraint explicitly. For |
| 423 |
<literal>ON CONFLICT DO NOTHING</literal>, it is optional to |
| 424 |
specify a <parameter>conflict_target</parameter>; when |
| 425 |
omitted, conflicts with all usable constraints (and unique |
| 426 |
indexes) are handled. For <literal>ON CONFLICT DO |
| 427 |
UPDATE</literal>, a <parameter>conflict_target</parameter> |
| 428 |
<emphasis>must</emphasis> be provided. |
| 429 |
</para> |
| 430 |
</listitem> |
| 431 |
</varlistentry> |
| 432 |
|
| 433 |
<varlistentry> |
| 434 |
<term><replaceable class="parameter">conflict_action</replaceable></term> |
| 435 |
<listitem> |
| 436 |
<para> |
| 437 |
<parameter>conflict_action</parameter> specifies an |
| 438 |
alternative <literal>ON CONFLICT</literal> action. It can be |
| 439 |
either <literal>DO NOTHING</literal>, or a <literal>DO |
| 440 |
UPDATE</literal> clause specifying the exact details of the |
| 441 |
<literal>UPDATE</literal> action to be performed in case of a |
| 442 |
conflict. The <literal>SET</literal> and |
| 443 |
<literal>WHERE</literal> clauses in <literal>ON CONFLICT DO |
| 444 |
UPDATE</literal> have access to the existing row using the |
| 445 |
table's name (or an alias), and to the row proposed for insertion |
| 446 |
using the special <varname>excluded</varname> table. |
| 447 |
<literal>SELECT</literal> privilege is required on any column in the |
| 448 |
target table where corresponding <varname>excluded</varname> |
| 449 |
columns are read. |
| 450 |
</para> |
| 451 |
<para> |
| 452 |
Note that the effects of all per-row <literal>BEFORE |
| 453 |
INSERT</literal> triggers are reflected in |
| 454 |
<varname>excluded</varname> values, since those effects may |
| 455 |
have contributed to the row being excluded from insertion. |
| 456 |
</para> |
| 457 |
</listitem> |
| 458 |
</varlistentry> |
| 459 |
|
| 460 |
<varlistentry> |
| 461 |
<term><replaceable class="parameter">index_column_name</replaceable></term> |
| 462 |
<listitem> |
| 463 |
<para> |
| 464 |
The name of a <replaceable |
| 465 |
class="parameter">table_name</replaceable> column. Used to |
| 466 |
infer arbiter indexes. Follows <command>CREATE |
| 467 |
INDEX</command> format. <literal>SELECT</literal> privilege on |
| 468 |
<replaceable class="parameter">index_column_name</replaceable> |
| 469 |
is required. |
| 470 |
</para> |
| 471 |
</listitem> |
| 472 |
</varlistentry> |
| 473 |
|
| 474 |
<varlistentry> |
| 475 |
<term><replaceable class="parameter">index_expression</replaceable></term> |
| 476 |
<listitem> |
| 477 |
<para> |
| 478 |
Similar to <replaceable |
| 479 |
class="parameter">index_column_name</replaceable>, but used to |
| 480 |
infer expressions on <replaceable |
| 481 |
class="parameter">table_name</replaceable> columns appearing |
| 482 |
within index definitions (not simple columns). Follows |
| 483 |
<command>CREATE INDEX</command> format. <literal>SELECT</literal> |
| 484 |
privilege on any column appearing within <replaceable |
| 485 |
class="parameter">index_expression</replaceable> is required. |
| 486 |
</para> |
| 487 |
</listitem> |
| 488 |
</varlistentry> |
| 489 |
|
| 490 |
<varlistentry> |
| 491 |
<term><replaceable class="parameter">collation</replaceable></term> |
| 492 |
<listitem> |
| 493 |
<para> |
| 494 |
When specified, mandates that corresponding <replaceable |
| 495 |
class="parameter">index_column_name</replaceable> or |
| 496 |
<replaceable class="parameter">index_expression</replaceable> |
| 497 |
use a particular collation in order to be matched during |
| 498 |
inference. Typically this is omitted, as collations usually |
| 499 |
do not affect whether or not a constraint violation occurs. |
| 500 |
Follows <command>CREATE INDEX</command> format. |
| 501 |
</para> |
| 502 |
</listitem> |
| 503 |
</varlistentry> |
| 504 |
|
| 505 |
<varlistentry> |
| 506 |
<term><replaceable class="parameter">opclass</replaceable></term> |
| 507 |
<listitem> |
| 508 |
<para> |
| 509 |
When specified, mandates that corresponding <replaceable |
| 510 |
class="parameter">index_column_name</replaceable> or |
| 511 |
<replaceable class="parameter">index_expression</replaceable> |
| 512 |
use particular operator class in order to be matched during |
| 513 |
inference. Typically this is omitted, as the |
| 514 |
<emphasis>equality</emphasis> semantics are often equivalent |
| 515 |
across a type's operator classes anyway, or because it's |
| 516 |
sufficient to trust that the defined unique indexes have the |
| 517 |
pertinent definition of equality. Follows <command>CREATE |
| 518 |
INDEX</command> format. |
| 519 |
</para> |
| 520 |
</listitem> |
| 521 |
</varlistentry> |
| 522 |
|
| 523 |
<varlistentry> |
| 524 |
<term><replaceable class="parameter">index_predicate</replaceable></term> |
| 525 |
<listitem> |
| 526 |
<para> |
| 527 |
Used to allow inference of partial unique indexes. Any |
| 528 |
indexes that satisfy the predicate (which need not actually be |
| 529 |
partial indexes) can be inferred. Follows <command>CREATE |
| 530 |
INDEX</command> format. <literal>SELECT</literal> privilege on any |
| 531 |
column appearing within <replaceable |
| 532 |
class="parameter">index_predicate</replaceable> is required. |
| 533 |
</para> |
| 534 |
</listitem> |
| 535 |
</varlistentry> |
| 536 |
|
| 537 |
<varlistentry> |
| 538 |
<term><replaceable class="parameter">constraint_name</replaceable></term> |
| 539 |
<listitem> |
| 540 |
<para> |
| 541 |
Explicitly specifies an arbiter |
| 542 |
<emphasis>constraint</emphasis> by name, rather than inferring |
| 543 |
a constraint or index. |
| 544 |
</para> |
| 545 |
</listitem> |
| 546 |
</varlistentry> |
| 547 |
|
| 548 |
<varlistentry> |
| 549 |
<term><replaceable class="parameter">condition</replaceable></term> |
| 550 |
<listitem> |
| 551 |
<para> |
| 552 |
An expression that returns a value of type |
| 553 |
<type>boolean</type>. Only rows for which this expression |
| 554 |
returns <literal>true</literal> will be updated, although all |
| 555 |
rows will be locked when the <literal>ON CONFLICT DO UPDATE</literal> |
| 556 |
action is taken. Note that |
| 557 |
<replaceable>condition</replaceable> is evaluated last, after |
| 558 |
a conflict has been identified as a candidate to update. |
| 559 |
</para> |
| 560 |
</listitem> |
| 561 |
</varlistentry> |
| 562 |
</variablelist> |
| 563 |
<para> |
| 564 |
Note that exclusion constraints are not supported as arbiters with |
| 565 |
<literal>ON CONFLICT DO UPDATE</literal>. In all cases, only |
| 566 |
<literal>NOT DEFERRABLE</literal> constraints and unique indexes |
| 567 |
are supported as arbiters. |
| 568 |
</para> |
| 569 |
|
| 570 |
<para> |
| 571 |
<command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</literal> |
| 572 |
clause is a <quote>deterministic</quote> statement. This means |
| 573 |
that the command will not be allowed to affect any single existing |
| 574 |
row more than once; a cardinality violation error will be raised |
| 575 |
when this situation arises. Rows proposed for insertion should |
| 576 |
not duplicate each other in terms of attributes constrained by an |
| 577 |
arbiter index or constraint. |
| 578 |
</para> |
| 579 |
|
| 580 |
<para> |
| 581 |
Note that it is currently not supported for the |
| 582 |
<literal>ON CONFLICT DO UPDATE</literal> clause of an |
| 583 |
<command>INSERT</command> applied to a partitioned table to update the |
| 584 |
partition key of a conflicting row such that it requires the row be moved |
| 585 |
to a new partition. |
| 586 |
</para> |
| 587 |
<tip> |
| 588 |
<para> |
| 589 |
It is often preferable to use unique index inference rather than |
| 590 |
naming a constraint directly using <literal>ON CONFLICT ON |
| 591 |
CONSTRAINT</literal> <replaceable class="parameter"> |
| 592 |
constraint_name</replaceable>. Inference will continue to work |
| 593 |
correctly when the underlying index is replaced by another more |
| 594 |
or less equivalent index in an overlapping way, for example when |
| 595 |
using <literal>CREATE UNIQUE INDEX ... CONCURRENTLY</literal> |
| 596 |
before dropping the index being replaced. |
| 597 |
</para> |
| 598 |
</tip> |
| 599 |
|
| 600 |
<warning> |
| 601 |
<para> |
| 602 |
While <command>CREATE INDEX CONCURRENTLY</command> or <command>REINDEX |
| 603 |
CONCURRENTLY</command> is running on a unique index, <command>INSERT |
| 604 |
... ON CONFLICT</command> statements on the same table may unexpectedly |
| 605 |
fail with a unique violation. |
| 606 |
</para> |
| 607 |
</warning> |
| 608 |
|
| 609 |
</refsect2> |
| 610 |
</refsect1> |
| 611 |
|
| 612 |
<refsect1> |
| 613 |
<title>Outputs</title> |
| 614 |
|
| 615 |
<para> |
| 616 |
On successful completion, an <command>INSERT</command> command returns a command |
| 617 |
tag of the form |
| 618 |
<screen> |
| 619 |
INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable> |
| 620 |
</screen> |
| 621 |
The <replaceable class="parameter">count</replaceable> is the number of |
| 622 |
rows inserted or updated. <replaceable>oid</replaceable> is always 0 (it |
| 623 |
used to be the <acronym>OID</acronym> assigned to the inserted row if |
| 624 |
<replaceable>count</replaceable> was exactly one and the target table was |
| 625 |
declared <literal>WITH OIDS</literal> and 0 otherwise, but creating a table |
| 626 |
<literal>WITH OIDS</literal> is not supported anymore). |
| 627 |
</para> |
| 628 |
|
| 629 |
<para> |
| 630 |
If the <command>INSERT</command> command contains a <literal>RETURNING</literal> |
| 631 |
clause, the result will be similar to that of a <command>SELECT</command> |
| 632 |
statement containing the columns and values defined in the |
| 633 |
<literal>RETURNING</literal> list, computed over the row(s) inserted or |
| 634 |
updated by the command. |
| 635 |
</para> |
| 636 |
</refsect1> |
| 637 |
|
| 638 |
<refsect1> |
| 639 |
<title>Notes</title> |
| 640 |
|
| 641 |
<para> |
| 642 |
If the specified table is a partitioned table, each row is routed to |
| 643 |
the appropriate partition and inserted into it. If the specified table |
| 644 |
is a partition, an error will occur if one of the input rows violates |
| 645 |
the partition constraint. |
| 646 |
</para> |
| 647 |
|
| 648 |
<para> |
| 649 |
You may also wish to consider using <command>MERGE</command>, since that |
| 650 |
allows mixing <command>INSERT</command>, <command>UPDATE</command>, and |
| 651 |
<command>DELETE</command> within a single statement. |
| 652 |
See <xref linkend="sql-merge"/>. |
| 653 |
</para> |
| 654 |
</refsect1> |
| 655 |
|
| 656 |
<refsect1> |
| 657 |
<title>Examples</title> |
| 658 |
|
| 659 |
<para> |
| 660 |
Insert a single row into table <literal>films</literal>: |
| 661 |
|
| 662 |
<programlisting> |
| 663 |
INSERT INTO films VALUES |
| 664 |
('UA502', 'Bananas', 105, '1971-07-13', 'Comedy', '82 minutes'); |
| 665 |
</programlisting> |
| 666 |
</para> |
| 667 |
|
| 668 |
<para> |
| 669 |
In this example, the <literal>len</literal> column is |
| 670 |
omitted and therefore it will have the default value: |
| 671 |
|
| 672 |
<programlisting> |
| 673 |
INSERT INTO films (code, title, did, date_prod, kind) |
| 674 |
VALUES ('T_601', 'Yojimbo', 106, '1961-06-16', 'Drama'); |
| 675 |
</programlisting> |
| 676 |
</para> |
| 677 |
|
| 678 |
<para> |
| 679 |
This example uses the <literal>DEFAULT</literal> clause for |
| 680 |
the date columns rather than specifying a value: |
| 681 |
|
| 682 |
<programlisting> |
| 683 |
INSERT INTO films VALUES |
| 684 |
('UA502', 'Bananas', 105, DEFAULT, 'Comedy', '82 minutes'); |
| 685 |
INSERT INTO films (code, title, did, date_prod, kind) |
| 686 |
VALUES ('T_601', 'Yojimbo', 106, DEFAULT, 'Drama'); |
| 687 |
</programlisting> |
| 688 |
</para> |
| 689 |
|
| 690 |
<para> |
| 691 |
To insert a row consisting entirely of default values: |
| 692 |
|
| 693 |
<programlisting> |
| 694 |
INSERT INTO films DEFAULT VALUES; |
| 695 |
</programlisting> |
| 696 |
</para> |
| 697 |
|
| 698 |
<para> |
| 699 |
To insert multiple rows using the multirow <command>VALUES</command> syntax: |
| 700 |
|
| 701 |
<programlisting> |
| 702 |
INSERT INTO films (code, title, did, date_prod, kind) VALUES |
| 703 |
('B6717', 'Tampopo', 110, '1985-02-10', 'Comedy'), |
| 704 |
('HG120', 'The Dinner Game', 140, DEFAULT, 'Comedy'); |
| 705 |
</programlisting> |
| 706 |
</para> |
| 707 |
|
| 708 |
<para> |
| 709 |
This example inserts some rows into table |
| 710 |
<literal>films</literal> from a table <literal>tmp_films</literal> |
| 711 |
with the same column layout as <literal>films</literal>: |
| 712 |
|
| 713 |
<programlisting> |
| 714 |
INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < '2004-05-07'; |
| 715 |
</programlisting> |
| 716 |
</para> |
| 717 |
|
| 718 |
<para> |
| 719 |
This example inserts into array columns: |
| 720 |
|
| 721 |
<programlisting> |
| 722 |
-- Create an empty 3x3 gameboard for noughts-and-crosses |
| 723 |
INSERT INTO tictactoe (game, board[1:3][1:3]) |
| 724 |
VALUES (1, '{{" "," "," "},{" "," "," "},{" "," "," "}}'); |
| 725 |
-- The subscripts in the above example aren't really needed |
| 726 |
INSERT INTO tictactoe (game, board) |
| 727 |
VALUES (2, '{{X," "," "},{" ",O," "},{" ",X," "}}'); |
| 728 |
</programlisting> |
| 729 |
</para> |
| 730 |
|
| 731 |
<para> |
| 732 |
Insert a single row into table <literal>distributors</literal>, returning |
| 733 |
the sequence number generated by the <literal>DEFAULT</literal> clause: |
| 734 |
|
| 735 |
<programlisting> |
| 736 |
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') |
| 737 |
RETURNING did; |
| 738 |
</programlisting> |
| 739 |
</para> |
| 740 |
|
| 741 |
<para> |
| 742 |
Increment the sales count of the salesperson who manages the |
| 743 |
account for Acme Corporation, and record the whole updated row |
| 744 |
along with current time in a log table: |
| 745 |
<programlisting> |
| 746 |
WITH upd AS ( |
| 747 |
UPDATE employees SET sales_count = sales_count + 1 WHERE id = |
| 748 |
(SELECT sales_person FROM accounts WHERE name = 'Acme Corporation') |
| 749 |
RETURNING * |
| 750 |
) |
| 751 |
INSERT INTO employees_log SELECT *, current_timestamp FROM upd; |
| 752 |
</programlisting> |
| 753 |
</para> |
| 754 |
<para> |
| 755 |
Insert or update new distributors as appropriate. Assumes a unique |
| 756 |
index has been defined that constrains values appearing in the |
| 757 |
<literal>did</literal> column. Note that the special |
| 758 |
<varname>excluded</varname> table is used to reference values originally |
| 759 |
proposed for insertion: |
| 760 |
<programlisting> |
| 761 |
INSERT INTO distributors (did, dname) |
| 762 |
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') |
| 763 |
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; |
| 764 |
</programlisting> |
| 765 |
</para> |
| 766 |
<para> |
| 767 |
Insert or update new distributors as above, returning information |
| 768 |
about any existing values that were updated, together with the new data |
| 769 |
inserted. Note that the returned values for <literal>old_did</literal> |
| 770 |
and <literal>old_dname</literal> will be <literal>NULL</literal> for |
| 771 |
non-conflicting rows: |
| 772 |
<programlisting> |
| 773 |
INSERT INTO distributors (did, dname) |
| 774 |
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') |
| 775 |
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname |
| 776 |
RETURNING old.did AS old_did, old.dname AS old_dname, |
| 777 |
new.did AS new_did, new.dname AS new_dname; |
| 778 |
</programlisting> |
| 779 |
</para> |
| 780 |
<para> |
| 781 |
Insert a distributor, or do nothing for rows proposed for insertion |
| 782 |
when an existing, excluded row (a row with a matching constrained |
| 783 |
column or columns after before row insert triggers fire) exists. |
| 784 |
Example assumes a unique index has been defined that constrains |
| 785 |
values appearing in the <literal>did</literal> column: |
| 786 |
<programlisting> |
| 787 |
INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') |
| 788 |
ON CONFLICT (did) DO NOTHING; |
| 789 |
</programlisting> |
| 790 |
</para> |
| 791 |
<para> |
| 792 |
Insert or update new distributors as appropriate. Example assumes |
| 793 |
a unique index has been defined that constrains values appearing in |
| 794 |
the <literal>did</literal> column. <literal>WHERE</literal> clause is |
| 795 |
used to limit the rows actually updated (any existing row not |
| 796 |
updated will still be locked, though): |
| 797 |
<programlisting> |
| 798 |
-- Don't update existing distributors based in a certain ZIP code |
| 799 |
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution') |
| 800 |
ON CONFLICT (did) DO UPDATE |
| 801 |
SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')' |
| 802 |
WHERE d.zipcode <> '21201'; |
| 803 |
|
| 804 |
-- Name a constraint directly in the statement (uses associated |
| 805 |
-- index to arbitrate taking the DO NOTHING action) |
| 806 |
INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') |
| 807 |
ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING; |
| 808 |
</programlisting> |
| 809 |
</para> |
| 810 |
<para> |
| 811 |
Insert new distributor if possible; otherwise |
| 812 |
<literal>DO NOTHING</literal>. Example assumes a unique index has been |
| 813 |
defined that constrains values appearing in the |
| 814 |
<literal>did</literal> column on a subset of rows where the |
| 815 |
<literal>is_active</literal> Boolean column evaluates to |
| 816 |
<literal>true</literal>: |
| 817 |
<programlisting> |
| 818 |
-- This statement could infer a partial unique index on "did" |
| 819 |
-- with a predicate of "WHERE is_active", but it could also |
| 820 |
-- just use a regular unique constraint on "did" |
| 821 |
INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') |
| 822 |
ON CONFLICT (did) WHERE is_active DO NOTHING; |
| 823 |
</programlisting></para> |
| 824 |
</refsect1> |
| 825 |
|
| 826 |
<refsect1> |
| 827 |
<title>Compatibility</title> |
| 828 |
|
| 829 |
<para> |
| 830 |
<command>INSERT</command> conforms to the SQL standard, except that |
| 831 |
the <literal>RETURNING</literal> clause is a |
| 832 |
<productname>PostgreSQL</productname> extension, as is the ability |
| 833 |
to use <literal>WITH</literal> with <command>INSERT</command>, and the ability to |
| 834 |
specify an alternative action with <literal>ON CONFLICT</literal>. |
| 835 |
Also, the case in |
| 836 |
which a column name list is omitted, but not all the columns are |
| 837 |
filled from the <literal>VALUES</literal> clause or <replaceable>query</replaceable>, |
| 838 |
is disallowed by the standard. If you prefer a more SQL standard |
| 839 |
conforming statement than <literal>ON CONFLICT</literal>, see |
| 840 |
<xref linkend="sql-merge"/>. |
| 841 |
</para> |
| 842 |
|
| 843 |
<para> |
| 844 |
The SQL standard specifies that <literal>OVERRIDING SYSTEM VALUE</literal> |
| 845 |
can only be specified if an identity column that is generated always |
| 846 |
exists. PostgreSQL allows the clause in any case and ignores it if it is |
| 847 |
not applicable. |
| 848 |
</para> |
| 849 |
|
| 850 |
<para> |
| 851 |
Possible limitations of the <replaceable |
| 852 |
class="parameter">query</replaceable> clause are documented under |
| 853 |
<xref linkend="sql-select"/>. |
| 854 |
</para> |
| 855 |
</refsect1> |
| 856 |
</refentry> |