Documentation Source Text

Check-in [74564b6da7]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Preliminary documentation for upsert.
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA3-256:74564b6da7b7dc9668c19c14d466faebe2876a4811658784db3f79defe653a5e
User & Date: drh 2018-04-19 13:28:28
Context
2018-04-20
00:00
Document the fact that UPSERTs count INSERT operations but not UPDATEs. check-in: fcfd6e8be6 user: drh tags: trunk
2018-04-19
13:28
Preliminary documentation for upsert. check-in: 74564b6da7 user: drh tags: trunk
10:24
Start the change log for 3.24.0 check-in: 1f4c6d050e user: drh tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to art/syntax/alter-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/analyze-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/attach-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/begin-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/bubble-generator-data.tcl.

317
318
319
320
321
322
323

324
325
326
327
328

329









330
331
332
333
334
335
336
...
487
488
489
490
491
492
493

494
495
496
497
498
499
500
501
502
503
504
505
506
507
              {line INSERT OR ABORT}
              {line INSERT OR FAIL}
              {line INSERT OR IGNORE}
          }
          INTO
       }
       {line {optx /schema-name .} /table-name

             {optx ( {loop /column-name ,} )}}
       {or
         {line VALUES {loop {line ( {loop expr ,} )} ,}}
         select-stmt
         {line DEFAULT VALUES}

       }









  }
  pragma-stmt {
     line PRAGMA {optx /schema-name .} /pragma-name
          {or
              nil
              {line = pragma-value}
              {line ( pragma-value )}
................................................................................
            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT expr {optx {or OFFSET ,} expr}}
            }
        }
  }
  qualified-table-name {

     line {optx /schema-name .} /table-name
          {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM {opt /schema-name}
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}
  }
}







>

|
|
|
|
>
|
>
>
>
>
>
>
>
>
>







 







>
|
|












317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
...
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
              {line INSERT OR ABORT}
              {line INSERT OR FAIL}
              {line INSERT OR IGNORE}
          }
          INTO
       }
       {line {optx /schema-name .} /table-name
             {opt AS /alias}
             {optx ( {loop /column-name ,} )}}
       {line {or
           {line VALUES {loop {line ( {loop expr ,} )} ,}}
           select-stmt
           {line DEFAULT VALUES}
         } {opt upsert-clause}}
  }
  upsert-clause {
    stack
      {line ON CONFLICT {opt ( {loop indexed-column ,} ) {opt WHERE expr} } DO } 
      {or
         NOTHING
         {line UPDATE SET 
              {loop {line {or /column-name column-name-list} = expr} ,}
              {optx WHERE expr}}
      }
  }
  pragma-stmt {
     line PRAGMA {optx /schema-name .} /pragma-name
          {or
              nil
              {line = pragma-value}
              {line ( pragma-value )}
................................................................................
            {stack
              {optx ORDER BY {loop ordering-term ,}}
              {line LIMIT expr {optx {or OFFSET ,} expr}}
            }
        }
  }
  qualified-table-name {
     stack
       {line {optx /schema-name .} /table-name {opt AS /alias}}
       {or nil {line INDEXED BY /index-name} {line NOT INDEXED}}
  }
  vacuum-stmt {
      line VACUUM {opt /schema-name}
  }
  comment-syntax {
    or
      {line -- {loop nil /anything-except-newline} 
           {or /newline /end-of-input}}
      {line /* {loop nil /anything-except-*/}
           {or */ /end-of-input}}
  }
}

Changes to art/syntax/column-constraint.gif.

cannot compute difference between binary files

Changes to art/syntax/column-def.gif.

cannot compute difference between binary files

Changes to art/syntax/column-name-list.gif.

cannot compute difference between binary files

Changes to art/syntax/comment-syntax.gif.

cannot compute difference between binary files

Changes to art/syntax/commit-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/common-table-expression.gif.

cannot compute difference between binary files

Changes to art/syntax/compound-operator.gif.

cannot compute difference between binary files

Changes to art/syntax/compound-select-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/conflict-clause.gif.

cannot compute difference between binary files

Changes to art/syntax/create-index-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-trigger-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-view-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/create-virtual-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/cte-table-name.gif.

cannot compute difference between binary files

Changes to art/syntax/delete-stmt-limited.gif.

cannot compute difference between binary files

Changes to art/syntax/delete-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/detach-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-index-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-table-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-trigger-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/drop-view-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/expr.gif.

cannot compute difference between binary files

Changes to art/syntax/factored-select-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/foreign-key-clause.gif.

cannot compute difference between binary files

Changes to art/syntax/indexed-column.gif.

cannot compute difference between binary files

Changes to art/syntax/insert-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/join-clause.gif.

cannot compute difference between binary files

Changes to art/syntax/join-constraint.gif.

cannot compute difference between binary files

Changes to art/syntax/join-operator.gif.

cannot compute difference between binary files

Changes to art/syntax/numeric-literal.gif.

cannot compute difference between binary files

Changes to art/syntax/ordering-term.gif.

cannot compute difference between binary files

Changes to art/syntax/pragma-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/pragma-value.gif.

cannot compute difference between binary files

Changes to art/syntax/qualified-table-name.gif.

cannot compute difference between binary files

Changes to art/syntax/raise-function.gif.

cannot compute difference between binary files

Changes to art/syntax/recursive-cte.gif.

cannot compute difference between binary files

Changes to art/syntax/reindex-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/release-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/result-column.gif.

cannot compute difference between binary files

Changes to art/syntax/rollback-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/savepoint-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/select-core.gif.

cannot compute difference between binary files

Changes to art/syntax/select-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/signed-number.gif.

cannot compute difference between binary files

Changes to art/syntax/simple-select-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/sql-stmt-list.gif.

cannot compute difference between binary files

Changes to art/syntax/sql-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/syntax_linkage.tcl.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
..
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
..
52
53
54
55
56
57
58

59
60
61
set syntax_linkage(alter-table-stmt) {column-def sql-stmt}
set syntax_linkage(analyze-stmt) {{} sql-stmt}
set syntax_linkage(attach-stmt) {expr sql-stmt}
set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(column-name-list) {{} {update-stmt update-stmt-limited}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(common-table-expression) {select-stmt {compound-select-stmt factored-select-stmt select-stmt simple-select-stmt}}
set syntax_linkage(compound-operator) {{} {factored-select-stmt select-stmt}}
set syntax_linkage(compound-select-stmt) {{common-table-expression expr ordering-term select-core} {}}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {{expr indexed-column} sql-stmt}
................................................................................
set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt indexed-column insert-stmt join-constraint ordering-term result-column select-core select-stmt simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited}}
set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint}}
set syntax_linkage(insert-stmt) {{expr select-stmt with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery}}
set syntax_linkage(join-constraint) {expr join-clause}
set syntax_linkage(join-operator) {{} join-clause}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt select-stmt simple-select-stmt update-stmt-limited}}
set syntax_linkage(pragma-stmt) {pragma-value sql-stmt}
................................................................................
set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{column-name-list expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{column-name-list expr ordering-term qualified-table-name with-clause} sql-stmt}

set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause cte-table-name recursive-cte common-table-expression delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}






|







 







|


|
|







 







>


|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
..
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
..
52
53
54
55
56
57
58
59
60
61
62
set syntax_linkage(alter-table-stmt) {column-def sql-stmt}
set syntax_linkage(analyze-stmt) {{} sql-stmt}
set syntax_linkage(attach-stmt) {expr sql-stmt}
set syntax_linkage(begin-stmt) {{} sql-stmt}
set syntax_linkage(column-constraint) {{conflict-clause expr foreign-key-clause literal-value signed-number} column-def}
set syntax_linkage(column-def) {{column-constraint type-name} {alter-table-stmt create-table-stmt}}
set syntax_linkage(column-name-list) {{} {update-stmt update-stmt-limited upsert-clause}}
set syntax_linkage(comment-syntax) {{} {}}
set syntax_linkage(commit-stmt) {{} sql-stmt}
set syntax_linkage(common-table-expression) {select-stmt {compound-select-stmt factored-select-stmt select-stmt simple-select-stmt}}
set syntax_linkage(compound-operator) {{} {factored-select-stmt select-stmt}}
set syntax_linkage(compound-select-stmt) {{common-table-expression expr ordering-term select-core} {}}
set syntax_linkage(conflict-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(create-index-stmt) {{expr indexed-column} sql-stmt}
................................................................................
set syntax_linkage(delete-stmt) {{expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(delete-stmt-limited) {{expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(detach-stmt) {{} sql-stmt}
set syntax_linkage(drop-index-stmt) {{} sql-stmt}
set syntax_linkage(drop-table-stmt) {{} sql-stmt}
set syntax_linkage(drop-trigger-stmt) {{} sql-stmt}
set syntax_linkage(drop-view-stmt) {{} sql-stmt}
set syntax_linkage(expr) {{literal-value raise-function select-stmt type-name} {attach-stmt column-constraint compound-select-stmt create-index-stmt create-trigger-stmt delete-stmt delete-stmt-limited factored-select-stmt indexed-column insert-stmt join-constraint ordering-term result-column select-core select-stmt simple-select-stmt table-constraint table-or-subquery update-stmt update-stmt-limited upsert-clause}}
set syntax_linkage(factored-select-stmt) {{common-table-expression compound-operator expr ordering-term select-core} {}}
set syntax_linkage(foreign-key-clause) {{} {column-constraint table-constraint}}
set syntax_linkage(indexed-column) {expr {create-index-stmt table-constraint upsert-clause}}
set syntax_linkage(insert-stmt) {{expr select-stmt upsert-clause with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(join-clause) {{join-constraint join-operator table-or-subquery} {select-core select-stmt table-or-subquery}}
set syntax_linkage(join-constraint) {expr join-clause}
set syntax_linkage(join-operator) {{} join-clause}
set syntax_linkage(literal-value) {{} {column-constraint expr}}
set syntax_linkage(numeric-literal) {{} {}}
set syntax_linkage(ordering-term) {expr {compound-select-stmt delete-stmt-limited factored-select-stmt select-stmt simple-select-stmt update-stmt-limited}}
set syntax_linkage(pragma-stmt) {pragma-value sql-stmt}
................................................................................
set syntax_linkage(sql-stmt) {{alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt create-index-stmt create-table-stmt create-trigger-stmt create-view-stmt create-virtual-table-stmt delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt insert-stmt pragma-stmt reindex-stmt release-stmt rollback-stmt savepoint-stmt select-stmt update-stmt update-stmt-limited vacuum-stmt} sql-stmt-list}
set syntax_linkage(sql-stmt-list) {sql-stmt {}}
set syntax_linkage(table-constraint) {{conflict-clause expr foreign-key-clause indexed-column} create-table-stmt}
set syntax_linkage(table-or-subquery) {{expr join-clause select-stmt} {join-clause select-core select-stmt}}
set syntax_linkage(type-name) {signed-number {column-def expr}}
set syntax_linkage(update-stmt) {{column-name-list expr qualified-table-name with-clause} {create-trigger-stmt sql-stmt}}
set syntax_linkage(update-stmt-limited) {{column-name-list expr ordering-term qualified-table-name with-clause} sql-stmt}
set syntax_linkage(upsert-clause) {{column-name-list expr indexed-column} insert-stmt}
set syntax_linkage(vacuum-stmt) {{} sql-stmt}
set syntax_linkage(with-clause) {{cte-table-name select-stmt} {delete-stmt delete-stmt-limited insert-stmt update-stmt update-stmt-limited}}
set syntax_order {sql-stmt-list sql-stmt alter-table-stmt analyze-stmt attach-stmt begin-stmt commit-stmt rollback-stmt savepoint-stmt release-stmt create-index-stmt indexed-column create-table-stmt column-def type-name column-constraint signed-number table-constraint foreign-key-clause conflict-clause create-trigger-stmt create-view-stmt create-virtual-table-stmt with-clause cte-table-name recursive-cte common-table-expression delete-stmt delete-stmt-limited detach-stmt drop-index-stmt drop-table-stmt drop-trigger-stmt drop-view-stmt expr raise-function literal-value numeric-literal insert-stmt upsert-clause pragma-stmt pragma-value reindex-stmt select-stmt join-clause select-core factored-select-stmt simple-select-stmt compound-select-stmt table-or-subquery result-column join-operator join-constraint ordering-term compound-operator update-stmt column-name-list update-stmt-limited qualified-table-name vacuum-stmt comment-syntax}

Changes to art/syntax/table-constraint.gif.

cannot compute difference between binary files

Changes to art/syntax/table-or-subquery.gif.

cannot compute difference between binary files

Changes to art/syntax/type-name.gif.

cannot compute difference between binary files

Changes to art/syntax/update-stmt-limited.gif.

cannot compute difference between binary files

Changes to art/syntax/update-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/vacuum-stmt.gif.

cannot compute difference between binary files

Changes to art/syntax/with-clause.gif.

cannot compute difference between binary files

Changes to pages/changes.in.

18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-07-00 (3.24.0)} {
<li> Add support for PostgreSQL-style UPSERT.
<li> Enhance [ALTER TABLE] so that it recognizes "true" and "false" as
     valid arguments to DEFAULT.
<li> Added the sorter-reference optimization as a compile-time option.
     Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
}

chng {2018-04-10 (3.23.1)} {







|







18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2018-07-00 (3.24.0)} {
<li> Add support for PostgreSQL-style [UPSERT].
<li> Enhance [ALTER TABLE] so that it recognizes "true" and "false" as
     valid arguments to DEFAULT.
<li> Added the sorter-reference optimization as a compile-time option.
     Only available if compiled with SQLITE_ENABLE_SORTER_REFERENCES.
}

chng {2018-04-10 (3.23.1)} {

Changes to pages/lang.in.

22
23
24
25
26
27
28

29
30
31
32
33
34
35
....
3304
3305
3306
3307
3308
3309
3310






3311
3312
3313
3314
3315
3316
3317

3318
3319
3320
3321
3322
3323
3324
....
3332
3333
3334
3335
3336
3337
3338










































3339
3340
3341
3342
3343
3344
3345
3346

3347
3348
3349
3350






3351
3352
3353
3354
3355
3356
3357
    {{CREATE TABLE} createtable}
    {{CREATE VIRTUAL TABLE} createvtab}
    {{CREATE INDEX} createindex}
    {VACUUM vacuum}
    {{DROP TABLE} droptable}
    {{DROP INDEX} dropindex}
    {INSERT insert}

    {REPLACE replace}
    {DELETE delete}
    {UPDATE update}
    {SELECT select}
    {{WITH clause} withclause}
    {comment comment}
    {EXPLAIN explain}
................................................................................
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.







<li><p><b>INSERT INTO </b><i>table</i><b> DEFAULT VALUES;</b>
<p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.


</ol>

<p>
^The initial "INSERT" keyword can be replaced by
"REPLACE" or "INSERT OR <i>action</i>" to specify an alternative
constraint [ON CONFLICT|conflict resolution algorithm] to use during 
................................................................................
is supported for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>











































<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

RecursiveBubbleDiagram conflict-clause
</tcl>

<p>The ON CONFLICT clause is not a separate SQL command.  It is a

non-standard clause that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>







<p>^The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE command.  ^For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
the syntax reads more naturally.  For example, instead of
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
The keywords change but the meaning of the clause is the same
either way.</p>







>







 







>
>
>
>
>
>







>







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







|
>
|



>
>
>
>
>
>







22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
....
3305
3306
3307
3308
3309
3310
3311
3312
3313
3314
3315
3316
3317
3318
3319
3320
3321
3322
3323
3324
3325
3326
3327
3328
3329
3330
3331
3332
....
3340
3341
3342
3343
3344
3345
3346
3347
3348
3349
3350
3351
3352
3353
3354
3355
3356
3357
3358
3359
3360
3361
3362
3363
3364
3365
3366
3367
3368
3369
3370
3371
3372
3373
3374
3375
3376
3377
3378
3379
3380
3381
3382
3383
3384
3385
3386
3387
3388
3389
3390
3391
3392
3393
3394
3395
3396
3397
3398
3399
3400
3401
3402
3403
3404
3405
3406
3407
3408
3409
3410
3411
3412
3413
3414
    {{CREATE TABLE} createtable}
    {{CREATE VIRTUAL TABLE} createvtab}
    {{CREATE INDEX} createindex}
    {VACUUM vacuum}
    {{DROP TABLE} droptable}
    {{DROP INDEX} dropindex}
    {INSERT insert}
    {UPSERT upsert}
    {REPLACE replace}
    {DELETE delete}
    {UPDATE update}
    {SELECT select}
    {{WITH clause} withclause}
    {comment comment}
    {EXPLAIN explain}
................................................................................
row of data returned by executing the SELECT statement. ^If a column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of items in the column-list. ^Otherwise, if no column-list is
specified, the number of columns in the result of the SELECT must be the same
as the number of columns in the table. ^Any SELECT statement, including
[compound SELECTs] and SELECT statements with [ORDER BY] and/or [LIMIT] clauses, 
may be used in an INSERT statement of this form.

<p>To avoid a parsing ambiguity, the SELECT statement should always
contain a WHERE clause, even if that clause is simply "WHERE true",
if the [upsert-clause] is present.  Without the WHERE clause, the
parser does not know if the token "ON" is part of a join constraint
on the SELECT, or the beginning of the [upsert-clause].

<li><p><b>INSERT INTO </b><i>table</i><b> DEFAULT VALUES;</b>
<p>The third form of an INSERT statement is with DEFAULT VALUES.
^(The INSERT ... DEFAULT VALUES statement inserts a single new row into the
named table.)^ ^Each column of the new row is populated with its 
[default value], or with a NULL if no default value is specified 
as part of the column definition in the [CREATE TABLE] statement.
The [upsert-clause] is not supported after DEFAULT VALUES.

</ol>

<p>
^The initial "INSERT" keyword can be replaced by
"REPLACE" or "INSERT OR <i>action</i>" to specify an alternative
constraint [ON CONFLICT|conflict resolution algorithm] to use during 
................................................................................
is supported for top-level INSERT statements only.)^  ^The table name must be
unqualified for INSERT statements that occur within [CREATE TRIGGER] statements.
^Similarly, the "DEFAULT VALUES" form of the INSERT statement is supported for
top-level INSERT statements only and not for INSERT statements within
triggers.
<p>

<p>The optional "AS <yyterm>alias</yyterm>" phrase provides an alternative
name for the table into which content is being inserted.  The alias name
can be used within WHERE and SET clauses of the [UPSERT].  If there is no
[upsert-clause], then the <yyterm>alias</yyterm> is pointless, but also
harmless.

<p>See the separate [UPSERT] documentation for the additional trailing
syntax that can cause an INSERT to behave as an UPDATE if the INSERT would
otherwise violate a uniqueness constraint.  The [upsert clause] is not
allowed on an "INSERT ... DEFAULT VALUES".


<tcl>
##############################################################################
Section upsert UPSERT {UPSERT {upsert clause}}

RecursiveBubbleDiagram upsert-clause
</tcl>

<p>UPSERT is a special syntax addition to [INSERT] that causes the
INSERT to behave as an [UPDATE] or a no-op if the INSERT would violate
a uniqueness constraint.
UPSERT is not standard SQL.  UPSERT in SQLite follows the
syntax established by PostgreSQL.
UPSERT syntax was added to SQLite with version 3.24.0 ([dateof:3.24.0]).

<p>An UPSERT is an ordinary [INSERT] statement that is followed by
the special ON CONFLICT clause shown above.

<p>The syntax that occurs in between the "ON CONFLICT" and "DO" keywords
is called the "conflict target".  The conflict target specifies a specific
uniqueness constraint that will trigger the upsert.  The conflict target
is required for DO UPDATE upserts, but is optional for DO NOTHING.
A DO NOTHING upsert without a conflict target works the same as an
[ON CONFLICT|INSERT OR IGNORE].

<p>If the insert operation would cause the uniqueness constraint identified
by the conflict-target clause to fail, then the insert is omitted and
either the DO NOTHING or DO UPDATE operation is performed instead.
In the case of a multi-row insert, this decision is made separately
for each row of the insert.

<tcl>
##############################################################################
Section {ON CONFLICT clause} conflict {{conflict clause} {ON CONFLICT}}

RecursiveBubbleDiagram conflict-clause
</tcl>

<p>The ON CONFLICT clause is a
non-standard extension specific to SQLite 
that can appear in many other SQL commands.
It is given its own section in this document because it is not
part of standard SQL and therefore might not be familiar.</p>

<p>The ON CONFLICT clause described here has been a part of
SQLite since before version 3.0.0 ([dateof:3.0.0]).  The phrase
"ON&nbsp;CONFLICT" is also part of [UPSERT], which is an extension
to [INSERT] added in version 3.24.0 ([dateof:3.24.0]).  Do not
confuse these two separate uses of the "ON&nbsp;CONFLICT" phrase.

<p>^The syntax for the ON CONFLICT clause is as shown above for
the CREATE TABLE command.  ^For the INSERT and
UPDATE commands, the keywords "ON CONFLICT" are replaced by "OR" so that
the syntax reads more naturally.  For example, instead of
"INSERT ON CONFLICT IGNORE" we have "INSERT OR IGNORE".
The keywords change but the meaning of the clause is the same
either way.</p>