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