Documentation Source Text

                signed-number
                literal-value
                {line ( expr )}
            }
         }
         {line COLLATE /collation-name}
         {line foreign-key-clause}
         {line {optx GENERATED ALWAYS} AS ( expr )
            {or nil STORED VIRTUAL}}
      }
  }
  signed-number {
     line {or nil + -} /numeric-literal
  }
  table-constraint {
     stack

set nChng 0
proc chng {date desc {options {}}} {
  global nChng aChng xrefChng
  set aChng($nChng) [list $date $desc $options]
  set xrefChng($date) $nChng
  incr nChng
}

chng {2019-12-31 (3.31.0)} {
<li>Add support for [generated columns].
<li>Faster response to [sqlite3_interrupt()].
<li>Added the [https://sqlite.org/src/file/ext/misc/uuid.c|uuid.c] extension module
    implementing functions for processing RFC-4122 UUIDs.
}

chng {2019-10-11 (3.30.1)} {
<li> Fix a bug in the [query flattener] that might cause a segfault
for nested queries that use the new 
[FILTER clause on aggregate functions].
Ticket [https://www.sqlite.org/src/info/1079ad19993d13fa|1079ad19993d13fa]
<li> Cherrypick fixes for other obscure problems found since the 3.30.0

#    ORDER BY mtime DESC;
#
# A small amount of manual editing and de-duplication followed.
#
# Manually edit the list for each subsequent release.
#      
foreach line [split {
xxxxxxxxxx|pending|Version 3.31.0
18db032d05|2019-10-11|Version 3.30.1
c20a353364|2019-10-04|Version 3.30.0
fc82b73eaa|2019-07-10|Version 3.29.0
884b4b7e50|2019-04-16|Version 3.28.0
bd49a8271d|2019-02-25|Version 3.27.2
0eca3dd3d3|2019-02-08|Version 3.27.1
97744701c3|2019-02-07|Version 3.27.0

<title>Generated Columns</title>
<tcl>
hd_keywords {generated columns} {computed columns} {generated column}
</tcl>
<fancy_format>

<h1>Introduction</h1>

<p>Generated columns (also sometimes called "computed columns")
are columns of a table whose values are a function of other columns
in the same row.
Generated columns can be read, but their values can not be directly
written.  The only way to change the value of a generated columns is to
modify the values of the other columns used to calculate
the generated column.

<h1>Syntax</h1>

<p>Syntactically, generated columns are designated using a
"GENERATED ALWAYS" [column-constraint].  For example:

<codeblock>
CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
   e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
);
</codeblock>

<p>The statement above has three ordinary columns, "a" (the PRIMARY KEY),
"b", and "c", and two generated columns "d" and "e".

<p>The "GENERATED ALWAYS" keywords at the beginning of the constraint
and the "VIRTUAL" or "STORED" keyword at the end are all optional.
Only the "AS" keyword and the parenthesized expression are required.
If the trailing "VIRTUAL" or "STORED" keyword is omitted, then
VIRTUAL is the default.  Hence, the example statement above could
be simplified to just:

<codeblock>
CREATE TABLE t1(
   a INTEGER PRIMARY KEY,
   b INT,
   c TEXT,
   d INT AS (a*abs(b)),
   e TEXT AS (substr(c,b,b+1)) STORED
);
</codeblock>

<h2>VIRTUAL versus STORED columns</h2>

<p>Generated columns can be either VIRTUAL or STORED.  The value of
a VIRTUAL columns is computed when read, whereas the value of a STORED
column is computed when the row is written.  STORED columns take up space
in the database file, whereas VIRTUAL columns use more CPU cycles when
being read.

<p>From the point of view of SQL, STORED and VIRTUAL columns are almost
exactly the same.  Queries against either class of generated column
produce the same results.  The only functional difference is that
one cannot add new STORED columns using the
[ALTER TABLE ADD COLUMN] command.  Only VIRTUAL tables can be added
using ALTER TABLE.

<h2>Restrictions And Limitations</h2>

<ol>
<li><p>
Generated columns may not have a [default value] (they may not use the
"DEFAULT" clause).  The value of a generated columns is always the value
specified by the expression that follows the "AS" keyword.

<li><p>
Generated columns may not be used as part of the [PRIMARY KEY].
(Future versions of SQLite might relax this constraint for STORED columns.)

<li><p>
The expression of a generated column has the same restrictions as the
expression of a [CHECK constraint]: The expression may only reference
constant literals and columns within the same row, and may only use
scalar [deterministic functions].  The expression may not use subqueries,
aggregate functions, window functions, or table-valued functions.

<li><p>
The expression of a generated column may refer to other generated columns
in the same row, but no generated column can depend upon itself, either
directly or indirectly.

<li><p>
Every table must have at least one non-VIRTUAL column.
</ol>

<h1>Compatibility</h1>

<p>Generated column support was added with SQLite version 3.31.0
([dateof:3.31.0]).  If an earlier version of SQLite attempts to read
a database file that contains a generated column in its schema, then
that earlier version will perceive the generated column syntax as an
error and will report that the database schema is corrupt.

<p>
SQLite [https://sqlite.org/src|source code]
is in the [public-domain] and is free to 
everyone to use for any purpose.

<h3>Latest Release</h3>
<a href="releaselog/3_31_0.html">Version 3.31.0</a> ([dateof:3.31.0]).
<a class="button" href="download.html">Download</a>
<a class="button" href="chronology.html">Prior Releases</a>

<div class="mobileonly">
<h3>Common Links</h3>
<tcl>common_links</tcl>
</div>

<li>^The column may not have a default value of CURRENT_TIME, CURRENT_DATE, 
    CURRENT_TIMESTAMP, or an expression in parentheses.</li>
<li>^If a NOT NULL constraint is specified, then the column must have a
    default value other than NULL.
<li>^If [foreign key constraints] are [foreign_keys pragma | enabled] and
    a column with a [foreign-key-clause | REFERENCES clause]
    is added, the column must have a default value of NULL.
<li>^The column may not be [generated columns|GENERATED ALWAYS ... STORED],
though VIRTUAL columns are allowed.
</ul>

<p>^Note also that when adding a [CHECK constraint], the CHECK constraint
is not tested against preexisting rows of the table.
^This can result in a table that contains data that
is in violation of the CHECK constraint.  Future versions of SQLite might
change to validate CHECK constraints as they are added.</p>

  <li><p> A default collation sequence to use with each column.

  <li><p> Optionally, a PRIMARY KEY for the table. Both single column and
       composite (multiple column) primary keys are supported.

  <li><p> A set of SQL constraints for each table. SQLite supports UNIQUE, NOT
       NULL, CHECK and FOREIGN KEY constraints.

  <li><p> Optionally, a [generated column] constraint.

  <li><p> Whether the table is a [WITHOUT ROWID] table.
</ul>

<p>Every CREATE TABLE statement must specify a name for the new table.
  ^(Table names that begin with "sqlite_" are reserved for internal use. It
  is an error to attempt to create a table with a name that starts with

name of the column, optionally followed by the declared type of the column,
then one or more optional [column-constraint|column constraints]. Included in
the definition of "column constraints" for the purposes of the previous
statement are the COLLATE and DEFAULT clauses, even though these are not really
constraints in the sense that they do not restrict the data that the table may
contain. The other constraints - NOT NULL, CHECK, UNIQUE, PRIMARY KEY and
FOREIGN KEY constraints - impose restrictions on the tables data, and are are
described under [constraints|SQL Data Constraints] below. The
[generated column] syntax is supported since SQLite 3.31.0 ([dateof:3.31.0]).

<p>^Unlike most SQL databases, SQLite does not restrict the type of data that
may be inserted into a column based on the columns declared type. Instead,
SQLite uses [dynamic typing]. ^The declared type of a column is used to
determine the [affinity] of the column only.

<tcl>hd_fragment dfltval {default column value} {default value}</tcl>