Debian Codegraph @

www/gencol.html

raw
1 <!DOCTYPE html>
2 <html><head>
3 <meta name="viewport" content="width=device-width, initial-scale=1.0">
4 <meta http-equiv="content-type" content="text/html; charset=UTF-8">
5 <link href="sqlite.css" rel="stylesheet">
6 <title>Generated Columns</title>
7 <!-- path= -->
8 </head>
9 <body>
10 <div class=nosearch>
11 <a href="index.html">
12 <img class="logo" src="images/sqlite370_banner.svg" alt="SQLite" border="0">
13 </a>
14 <div><!-- IE hack to prevent disappearing logo --></div>
15 <div class="tagline desktoponly">
16 Small. Fast. Reliable.<br>Choose any three.
17 </div>
18 <div class="menu mainmenu">
19 <ul>
20 <li><a href="index.html">Home</a>
21 <li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a>
22 <li class='wideonly'><a href='about.html'>About</a>
23 <li class='desktoponly'><a href="docs.html">Documentation</a>
24 <li class='desktoponly'><a href="download.html">Download</a>
25 <li class='wideonly'><a href='copyright.html'>License</a>
26 <li class='desktoponly'><a href="support.html">Support</a>
27 <li class='desktoponly'><a href="prosupport.html">Purchase</a>
28 <li class='search' id='search_menubutton'>
29 <a href="javascript:void(0)" onclick='toggle_search()'>Search</a>
30 </ul>
31 </div>
32 <div class="menu submenu" id="submenu">
33 <ul>
34 <li><a href='about.html'>About</a>
35 <li><a href='docs.html'>Documentation</a>
36 <li><a href='download.html'>Download</a>
37 <li><a href='support.html'>Support</a>
38 <li><a href='prosupport.html'>Purchase</a>
39 </ul>
40 </div>
41 <div class="searchmenu" id="searchmenu">
42 <form method="GET" action="search">
43 <select name="s" id="searchtype">
44 <option value="d">Search Documentation</option>
45 <option value="c">Search Changelog</option>
46 </select>
47 <input type="text" name="q" id="searchbox" value="">
48 <input type="submit" value="Go">
49 </form>
50 </div>
51 </div>
52 <script>
53 function toggle_div(nm) {
54 var w = document.getElementById(nm);
55 if( w.style.display=="block" ){
56 w.style.display = "none";
57 }else{
58 w.style.display = "block";
59 }
60 }
61 function toggle_search() {
62 var w = document.getElementById("searchmenu");
63 if( w.style.display=="block" ){
64 w.style.display = "none";
65 } else {
66 w.style.display = "block";
67 setTimeout(function(){
68 document.getElementById("searchbox").focus()
69 }, 30);
70 }
71 }
72 function div_off(nm){document.getElementById(nm).style.display="none";}
73 window.onbeforeunload = function(e){div_off("submenu");}
74 /* Disable the Search feature if we are not operating from CGI, since */
75 /* Search is accomplished using CGI and will not work without it. */
76 if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){
77 document.getElementById("search_menubutton").style.display = "none";
78 }
79 /* Used by the Hide/Show button beside syntax diagrams, to toggle the */
80 function hideorshow(btn,obj){
81 var x = document.getElementById(obj);
82 var b = document.getElementById(btn);
83 if( x.style.display!='none' ){
84 x.style.display = 'none';
85 b.innerHTML='show';
86 }else{
87 x.style.display = '';
88 b.innerHTML='hide';
89 }
90 return false;
91 }
92 var antiRobot = 0;
93 function antiRobotGo(){
94 if( antiRobot!=3 ) return;
95 antiRobot = 7;
96 var j = document.getElementById("mtimelink");
97 if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href");
98 }
99 function antiRobotDefense(){
100 document.body.onmousedown=function(){
101 antiRobot |= 2;
102 antiRobotGo();
103 document.body.onmousedown=null;
104 }
105 document.body.onmousemove=function(){
106 antiRobot |= 2;
107 antiRobotGo();
108 document.body.onmousemove=null;
109 }
110 setTimeout(function(){
111 antiRobot |= 1;
112 antiRobotGo();
113 }, 100)
114 antiRobotGo();
115 }
116 antiRobotDefense();
117 </script>
118 <div class=fancy>
119 <div class=nosearch>
120 <div class="fancy_title">
121 Generated Columns
122 </div>
123 <details class="fancy_toc">
124 <summary>Table Of Contents</summary>
125 <div id="toc_sub"><div class="fancy-toc1"><a href="#introduction">1. Introduction</a></div>
126 <div class="fancy-toc1"><a href="#syntax">2. Syntax</a></div>
127 <div class="fancy-toc2"><a href="#virtual_versus_stored_columns">2.1. VIRTUAL versus STORED columns</a></div>
128 <div class="fancy-toc2"><a href="#capabilities">2.2. Capabilities</a></div>
129 <div class="fancy-toc2"><a href="#limitations">2.3. Limitations</a></div>
130 <div class="fancy-toc1"><a href="#compatibility">3. Compatibility</a></div>
131 </div>
132 </details>
133 </div>
134
135
136
137
138 <h1 id="introduction"><span>1. </span>Introduction</h1>
139
140 <p>Generated columns (also sometimes called "computed columns")
141 are columns of a table whose values are a function of other columns
142 in the same row.
143 Generated columns can be read, but their values can not be directly
144 written. The only way to change the value of a generated column is to
145 modify the values of the other columns used to calculate
146 the generated column.
147
148 </p><h1 id="syntax"><span>2. </span>Syntax</h1>
149
150 <p>Syntactically, generated columns are designated using a
151 "GENERATED ALWAYS" <a href="syntax/column-constraint.html">column-constraint</a>. For example:
152
153 </p><div class="codeblock"><pre>CREATE TABLE t1(
154 a INTEGER PRIMARY KEY,
155 b INT,
156 c TEXT,
157 d INT GENERATED ALWAYS AS (a*abs(b)) VIRTUAL,
158 e TEXT GENERATED ALWAYS AS (substr(c,b,b+1)) STORED
159 );
160 </pre></div>
161
162 <p>The statement above has three ordinary columns, "a" (the PRIMARY KEY),
163 "b", and "c", and two generated columns "d" and "e".
164
165 </p><p>The "GENERATED ALWAYS" keywords at the beginning of the constraint
166 and the "VIRTUAL" or "STORED" keyword at the end are all optional.
167 Only the "AS" keyword and the parenthesized expression are required.
168 If the trailing "VIRTUAL" or "STORED" keyword is omitted, then
169 VIRTUAL is the default. Hence, the example statement above could
170 be simplified to just:
171
172 </p><div class="codeblock"><pre>CREATE TABLE t1(
173 a INTEGER PRIMARY KEY,
174 b INT,
175 c TEXT,
176 d INT AS (a*abs(b)),
177 e TEXT AS (substr(c,b,b+1)) STORED
178 );
179 </pre></div>
180
181 <a name="vgc*"></a>
182
183 <h2 id="virtual_versus_stored_columns"><span>2.1. </span>VIRTUAL versus STORED columns</h2>
184
185 <p>Generated columns can be either VIRTUAL or STORED. The value of
186 a VIRTUAL column is computed when read, whereas the value of a STORED
187 column is computed when the row is written. STORED columns take up space
188 in the database file, whereas VIRTUAL columns use more CPU cycles when
189 being read.
190
191 </p><p>From the point of view of SQL, STORED and VIRTUAL columns are almost
192 the same. Queries against either class of generated column
193 produce the same results. The only functional difference is that
194 one cannot add new STORED columns using the
195 <a href="lang_altertable.html#altertabaddcol">ALTER TABLE ADD COLUMN</a> command. Only VIRTUAL columns can be added
196 using ALTER TABLE.
197
198 </p><h2 id="capabilities"><span>2.2. </span>Capabilities</h2>
199
200 <ol>
201 <li><p>
202 Generated columns can have a datatype. SQLite attempts to transform
203 the result of the generating expression into that datatype using the
204 same <a href="datatype3.html#affinity">affinity</a> rules as for ordinary columns.
205
206 </p></li><li><p>
207 Generated columns may have NOT NULL, CHECK, and UNIQUE constraints,
208 and foreign key constraints, just like ordinary columns.
209
210 </p></li><li><p>
211 Generated columns can participate in indexes, just like ordinary
212 columns. An index that uses STORED generated columns is just an
213 ordinary index, but an index that uses one or more VIRTUAL generated
214 columns is an <a href="expridx.html">expression index</a>.
215
216 </p></li><li><p>
217 The expression of a generated column can refer to any of the
218 other declared columns in the table, including other generated columns,
219 as long as the expression does not directly or indirectly refer back
220 to itself.
221
222 </p></li><li><p>
223 Generated columns can occur anywhere in the table definition. Generated
224 columns can be interspersed among ordinary columns. It is not necessary
225 to put generated columns at the end of the list of columns in the
226 table definition, as is shown in the examples above.
227 </p></li></ol>
228
229
230 <h2 id="limitations"><span>2.3. </span>Limitations</h2>
231
232 <ol>
233 <li><p>
234 Generated columns may not have a <a href="lang_createtable.html#dfltval">default value</a> (they may not use the
235 "DEFAULT" clause). The value of a generated column is always the value
236 specified by the expression that follows the "AS" keyword.
237
238 </p></li><li><p>
239 Generated columns may not be used as part of the <a href="lang_createtable.html#primkeyconst">PRIMARY KEY</a>.
240 (Future versions of SQLite might relax this constraint for STORED columns.)
241
242 </p></li><li><p>
243 The expression of a generated column may only reference
244 constant literals and columns within the same row, and may only use
245 scalar <a href="deterministic.html">deterministic functions</a>. The expression may not use subqueries,
246 aggregate functions, window functions, or table-valued functions.
247
248 </p></li><li><p>
249 The expression of a generated column may refer to other generated columns
250 in the same row, but no generated column can depend upon itself, either
251 directly or indirectly.
252
253 </p></li><li><p>The expression of a generated column may not directly reference
254 the <a href="lang_createtable.html#rowid">ROWID</a>, though it can reference the <a href="lang_createtable.html#rowid">INTEGER PRIMARY KEY</a> column,
255 which is often the same thing.
256
257 </p></li><li><p>
258 Every table must have at least one non-generated column.
259
260 </p></li><li><p>
261 It is not possible to <a href="lang_altertable.html#altertabaddcol">ALTER TABLE ADD COLUMN</a> a STORED column.
262 One can add a VIRTUAL column, however.
263
264 </p></li><li><p>
265 The datatype and <a href="datatype3.html#collation">collating sequence</a> of the generated column are determined
266 only by the datatype and <a href="lang_createtable.html#collateclause">COLLATE clause</a> on the column definition.
267 The datatype and collating sequence of the GENERATED ALWAYS AS expression
268 have no effect on the datatype and collating sequence of the column itself.
269
270 </p></li><li><p>
271 Generated columns are not included in the list of columns provided by
272 the <a href="pragma.html#pragma_table_info">PRAGMA table_info</a> statement. But they are included in the output of
273 the newer <a href="pragma.html#pragma_table_xinfo">PRAGMA table_xinfo</a> statement.
274 </p></li></ol>
275
276 <h1 id="compatibility"><span>3. </span>Compatibility</h1>
277
278 <p>Generated column support was added with SQLite version 3.31.0
279 (2020-01-22). If an earlier version of SQLite attempts to read
280 a database file that contains a generated column in its schema, then
281 that earlier version will perceive the generated column syntax as an
282 error and will report that the database schema is corrupt.
283
284 </p><p>To clarify: SQLite version 3.31.0 can read and write any database
285 created by any prior version of SQLite going back to
286 SQLite 3.0.0 (2004-06-18). And, earlier versions of SQLite,
287 prior to 3.31.0, can read and write databases created by SQLite
288 version 3.31.0 and later as long
289 as the database schema does not contain features, such as
290 generated columns, that are not understood by the earlier version.
291 Problems only arise if you create a new database that contains
292 generated columns, using SQLite version 3.31.0 or later, and then
293 try to read or write that database file using an earlier version of
294 SQLite that does not understand generated columns.
295 </p><p align="center"><small><i>This page was last updated on 2026-05-14 15:13:28Z </small></i></p>
296