System.Data.SQLite

<p>An UPSERT is an ordinary <a href="lang_insert.html">INSERT</a> 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.  When
the conflict target is omitted, the upsert behavior is triggered by a
violation of any uniqueness constraint on the table of the INSERT.

<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.

<p>The special UPSERT processing happens only for uniqueness constraint on
the table that is receiving the INSERT. A "uniqueness constraint"
is an explicit UNIQUE or PRIMARY KEY constraint within
the CREATE TABLE statement, or a <a href="lang_createindex.html#uniqueidx">unique index</a>.
UPSERT does not intervene for failed NOT NULL or foreign key constraints
or for constraints that are implemented using triggers.

<p>Column names in the expressions of a DO UPDATE refer to the original
unchanged value of the column, before the attempted INSERT.  To use the
value that would have been inserted had the constraint not failed,
add the special "excluded." table qualifier to the column name.

<p>Some examples will help illustrate the difference:

</pre></blockquote>

<p>In this last example, the phonebook2 entry is only
updated if the validDate for the newly inserted value is
newer than the entry already in the table.  If the table already
contains an entry with the same name and a current validDate,
then the WHERE clause causes the DO UPDATE to become a no-op.

<a name="parseambig"></a>

<h3>Parsing Ambiguity</h3>

<p>When the <a href="lang_insert.html">INSERT</a> statement to which the UPSERT is attached
takes its values from a <a href="lang_select.html">SELECT</a> statement, there is a potential
parsing ambiguity.  The parser might not be able to tell if the
"ON" keyword is introducing the UPSERT or if it is the ON clause
of a join.  To work around this, the SELECT statement should always
include a WHERE clause, even if that WHERE clause is just
"WHERE true".

<p>Ambiguous use of ON:

<blockquote><pre>
INSERT INTO t1 SELECT * FROM t2
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
</pre></blockquote>

<p>Ambiguity resolved using a WHERE clause:

<blockquote><pre>
INSERT INTO t1 SELECT * FROM t2 <font color="blue">WHERE true</font>
ON CONFLICT(x) DO UPDATE SET y=excluded.y;
</pre></blockquote>

<h3>Limitations</h3>

<p>UPSERT does not currently work for <a href="vtab.html">virtual tables</a>.

In most cases <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> will not run ANALYZE, but it will occasionally
do so either for tables that have never before been analyzed, or for tables
that have grown significantly since they were last analyzed.</p>

<p>Since the actions of <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> are determined to some extent by
prior queries that have been evaluated on the same database connection, it
is recommended that <a href="pragma.html#pragma_optimize">PRAGMA optimize</a> be deferred until the database connection
is closing and has thus had an opportunity to accumulate as much usage information
as possible.  It is also reasonable to set a timer to run <a href="pragma.html#pragma_optimize">PRAGMA optimize</a>
every few hours, or every few days, for database connections that stay open
for a long time.</p>

<p>Applications that desire more control can run <a href="pragma.html#pragma_optimize">PRAGMA optimize(0x03)</a> to 
obtain a list of ANALYZE commands that SQLite thinks are appropriate to run,
but without actually running those commands.  If the returned set is 

but some keywords may be omitted when SQL language features are
disabled.
Applications can use the
<a href="c3ref/keyword_check.html">sqlite3_keyword_count()</a>, <a href="c3ref/keyword_check.html">sqlite3_keyword_name()</a>, and
<a href="c3ref/keyword_check.html">sqlite3_keyword_check()</a> interfaces to determine the keywords
recognized by SQLite at run-time.
Regardless of the compile-time configuration, any identifier that is not on
the following 140 element
list is not a keyword to the SQL parser in SQLite:
</p>

<ol>
<li>ABORT</li>
<li>ACTION</li>
<li>ADD</li>

<li>DO</li>
<li>DROP</li>
<li>EACH</li>
<li>ELSE</li>
<li>END</li>
<li>ESCAPE</li>
<li>EXCEPT</li>
<li>EXCLUDE</li>
<li>EXCLUSIVE</li>
<li>EXISTS</li>
<li>EXPLAIN</li>
<li>FAIL</li>
<li>FILTER</li>
<li>FOLLOWING</li>
<li>FOR</li>
<li>FOREIGN</li>
<li>FROM</li>
<li>FULL</li>
<li>GLOB</li>
<li>GROUP</li>
<li>GROUPS</li>
<li>HAVING</li>
<li>IF</li>
<li>IGNORE</li>
<li>IMMEDIATE</li>
<li>IN</li>
<li>INDEX</li>
<li>INDEXED</li>

<li>NOTNULL</li>
<li>NULL</li>
<li>OF</li>
<li>OFFSET</li>
<li>ON</li>
<li>OR</li>
<li>ORDER</li>
<li>OTHERS</li>
<li>OUTER</li>
<li>OVER</li>
<li>PARTITION</li>
<li>PLAN</li>
<li>PRAGMA</li>
<li>PRECEDING</li>
<li>PRIMARY</li>

<li>SAVEPOINT</li>
<li>SELECT</li>
<li>SET</li>
<li>TABLE</li>
<li>TEMP</li>
<li>TEMPORARY</li>
<li>THEN</li>
<li>TIES</li>
<li>TO</li>
<li>TRANSACTION</li>
<li>TRIGGER</li>
<li>UNBOUNDED</li>
<li>UNION</li>
<li>UNIQUE</li>
<li>UPDATE</li>

The filename in the INTO clause can be an arbitrary SQL expression
that evaluates to a string.
The file named by the INTO clause must not previously exist, or
else it must be an empty file, or the VACUUM INTO command will
fail with an error.

<p>
The argument to INTO can be a <a href="uri.html">URI filename</a> if URI filenames
are enabled.
URL filenames are enabled if any of the following are true:
<ul>
<li> The SQLite library was compiled with <a href="compile.html#use_uri">-DSQLITE_USE_URI=1</a>.
<li> The <a href="c3ref/config.html">sqlite3_config</a>(<a href="c3ref/c_config_covering_index_scan.html#sqliteconfiguri">SQLITE_CONFIG_URI</a>,1) interfaces was
     invoked at start-time.
<li> The <a href="c3ref/sqlite3.html">database connection</a> that is running the VACUUM INTO

named on the INTO clause in place of the temporary database and omits the
step of copying the vacuumed database back over top of the original database.

<p>The VACUUM command may change the <a href="lang_createtable.html#rowid">ROWIDs</a> of entries in any
tables that do not have an explicit <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a>.
</p>

<p>A VACUUM will fail if there is an open transaction on the database
connection that is attempting to run the VACUUM.  Unfinalized SQL
statements typically hold a read transaction open, so the VACUUM
might fail if there are unfinalized SQL statements on the same connection.
VACUUM (but not VACUUM INTO) is a write operation and so if another
database connection is holding a lock that prevents writes, then
the VACUUM will fail.

<p>An alternative to using the VACUUM command to
reclaim space after data has been deleted is auto-vacuum mode, enabled using
the <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> pragma. When <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> is enabled for a database
free pages may be reclaimed after deleting data, causing the file to shrink,
without rebuilding the entire database using VACUUM.  However, using
<a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a> can lead to extra database file fragmentation.  And <a href="pragma.html#pragma_auto_vacuum">auto_vacuum</a>
does not compact partially filled pages of the database as VACUUM does.

</p>

<hr /><a name="toc"></a>
<h2>List Of PRAGMAs</h2>
<style>
#listtab34 tr td {vertical-align:top;}
</style>
<table id='listtab34' width='100%'></table>
<script>
var listitems34 = [{"u":"pragma.html#pragma_locking_mode","x":"EXCLUSIVE locking mode","s":0},
{"u":"pragma.html#pragma_application_id","x":"application_id","s":0},
{"u":"pragma.html#pragma_auto_vacuum","x":"auto_vacuum","s":0},
{"u":"pragma.html#pragma_automatic_index","x":"automatic_index","s":0},
{"u":"pragma.html#pragma_busy_timeout","x":"busy_timeout","s":0},
{"u":"pragma.html#pragma_cache_size","x":"cache_size","s":0},
{"u":"pragma.html#pragma_cache_spill","x":"cache_spill","s":0},
{"u":"pragma.html#pragma_case_sensitive_like","x":"case_sensitive_like","s":0},
{"u":"pragma.html#pragma_cell_size_check","x":"cell_size_check","s":0},
{"u":"pragma.html#pragma_checkpoint_fullfsync","x":"checkpoint_fullfsync","s":0},
{"u":"pragma.html#pragma_collation_list","x":"collation_list","s":0},
{"u":"pragma.html#pragma_compile_options","x":"compile_options","s":0},
{"u":"pragma.html#pragma_count_changes","x":"count_changes","s":3},
{"u":"pragma.html#pragma_data_store_directory","x":"data_store_directory","s":3},
{"u":"pragma.html#pragma_data_version","x":"data_version","s":0},
{"u":"pragma.html#pragma_database_list","x":"database_list","s":0},
{"u":"pragma.html#pragma_default_cache_size","x":"default_cache_size","s":3},
{"u":"pragma.html#pragma_defer_foreign_keys","x":"defer_foreign_keys","s":0},
{"u":"pragma.html#pragma_empty_result_callbacks","x":"empty_result_callbacks","s":3},
{"u":"pragma.html#pragma_encoding","x":"encoding","s":0},
{"u":"pragma.html#pragma_locking_mode","x":"exclusive locking mode","s":0},
{"u":"pragma.html#pragma_foreign_key_check","x":"foreign_key_check","s":0},
{"u":"pragma.html#pragma_foreign_key_list","x":"foreign_key_list","s":0},
{"u":"pragma.html#pragma_foreign_keys","x":"foreign_keys","s":0},
{"u":"pragma.html#pragma_freelist_count","x":"freelist_count","s":0},
{"u":"pragma.html#pragma_full_column_names","x":"full_column_names","s":3},
{"u":"pragma.html#pragma_fullfsync","x":"fullfsync","s":0},
{"u":"pragma.html#pragma_function_list","x":"function_list","s":4},

{"u":"pragma.html#pragma_max_page_count","x":"max_page_count","s":0},
{"u":"pragma.html#pragma_mmap_size","x":"mmap_size","s":0},
{"u":"pragma.html#pragma_module_list","x":"module_list","s":4},
{"u":"pragma.html#pragma_optimize","x":"optimize","s":0},
{"u":"pragma.html#pragma_page_count","x":"page_count","s":0},
{"u":"pragma.html#pragma_page_size","x":"page_size","s":0},
{"u":"pragma.html#pragma_parser_trace","x":"parser_trace","s":4},
{"u":"pragma.html#pragma_journal_mode","x":"persistent journal mode","s":0},
{"u":"pragma.html#pragma_pragma_list","x":"pragma_list","s":4},
{"u":"pragma.html#pragma_query_only","x":"query_only","s":0},
{"u":"pragma.html#pragma_quick_check","x":"quick_check","s":0},
{"u":"pragma.html#pragma_read_uncommitted","x":"read_uncommitted","s":0},
{"u":"pragma.html#pragma_recursive_triggers","x":"recursive_triggers","s":0},
{"u":"pragma.html#pragma_reverse_unordered_selects","x":"reverse_unordered_selects","s":0},
{"u":"pragma.html#pragma_schema_version","x":"schema_version","s":5},

    is limited to 2048000 bytes of memory.
    The default suggested cache size can be altered using the
    <a href="compile.html#default_cache_size">SQLITE_DEFAULT_CACHE_SIZE</a> compile-time options.
    The TEMP database has a default suggested cache size of 0 pages.</p>

    <p>If the argument N is positive then the suggested cache size is set 
    to N. If the argument N is negative, then the
    number of cache pages is adjusted to be a number of pages that would
    use approximately abs(N*1024) bytes of memory based on the current
    page size.  SQLite remembers the number of pages in the page cache,
    not the amount of memory used.  So if you set the cache size using
    a negative number and subsequently change the page size (using the
    <a href="pragma.html#pragma_page_size">PRAGMA page_size</a> command) then the maximum amount of cache
    memory will go up or down in proportion to the change in page size.

    <p><i>Backwards compatibility note:</i>
    The behavior of cache_size with a negative N
    was different prior to <a href="releaselog/3_7_10.html">version 3.7.10</a> (2012-01-16).  In
    earlier versions, the number of pages in the cache was set
    to the absolute value of N.</p>

    <p>When you change the cache size using the cache_size pragma, the
    change only endures for the current session.  The cache size reverts
    to the default value when the database is closed and reopened.</p>

    <p>The default page cache implemention does not allocate
    the full amount of cache memory all at once.  Cache memory
    is allocated in smaller chunks on an as-needed basis.  The page_cache
    setting is a (suggested) upper bound on the amount of memory that the
    cache can use, not the amount of memory it will use all of the time.
    This is the behavior of the default page cache implementation, but an
    <a href="c3ref/pcache_methods2.html">applicaction defined page cache</a> is free
    to behave differently if it wants.
<a name="pragma_cache_spill"></a>
<h _id=pragma_cache_spill style="display:none"> PRAGMA cache_spill</h><hr>
    <p><b>PRAGMA cache_spill;
         <br>PRAGMA cache_spill=</b><i>boolean</i><b>;
         <br>PRAGMA </b><i>schema.</i><b>cache_spill=<i>N</i>;</b></p>

    <p>The cache_spill pragma enables or disables the ability of the pager