| 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>Query Result Formatting In The CLI</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 |
Query Result Formatting In The CLI |
| 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-toc2"><a href="#summary_of_enhancements">1.1. Summary of enhancements</a></div> |
| 127 |
<div class="fancy-toc2"><a href="#a_few_quick_examples_">1.2. A few quick examples:</a></div> |
| 128 |
<div class="fancy-toc1"><a href="#default_output_formats">2. Default Output Formats</a></div> |
| 129 |
<div class="fancy-toc1"><a href="#changing_output_formats">3. Changing Output Formats</a></div> |
| 130 |
<div class="fancy-toc2"><a href="#output_mode_list_">3.1. Output mode "list"</a></div> |
| 131 |
<div class="fancy-toc2"><a href="#output_mode_quote_">3.2. Output mode "quote"</a></div> |
| 132 |
<div class="fancy-toc2"><a href="#other_list_like_output_modes">3.3. Other list-like output modes</a></div> |
| 133 |
<div class="fancy-toc2"><a href="#output_mode_line_">3.4. Output mode "line"</a></div> |
| 134 |
<div class="fancy-toc2"><a href="#tabular_output_modes">3.5. Tabular output modes</a></div> |
| 135 |
<div class="fancy-toc2"><a href="#line_wrapping">3.6. Line wrapping</a></div> |
| 136 |
<div class="fancy-toc2"><a href="#output_mode_insert_">3.7. Output mode "insert"</a></div> |
| 137 |
<div class="fancy-toc2"><a href="#output_mode_split_">3.8. Output mode "split"</a></div> |
| 138 |
<div class="fancy-toc2"><a href="#output_modes_count_and_off_">3.9. Output modes "count" and "off"</a></div> |
| 139 |
<div class="fancy-toc2"><a href="#output_modes_batch_and_tty_">3.10. Output modes "batch" and "tty"</a></div> |
| 140 |
<div class="fancy-toc2"><a href="#user_defined_output_modes">3.11. User Defined Output Modes</a></div> |
| 141 |
<div class="fancy-toc1"><a href="#escaping_control_characters">4. Escaping Control Characters</a></div> |
| 142 |
<div class="fancy-toc1"><a href="#line_endings">5. Line Endings</a></div> |
| 143 |
<div class="fancy-toc1"><a href="#details_on_mode_syntax_and_options">6. Details On .mode Syntax And Options</a></div> |
| 144 |
<div class="fancy-toc2"><a href="#_align_string">6.1. --align STRING</a></div> |
| 145 |
<div class="fancy-toc2"><a href="#_blob_quote_arg">6.2. --blob-quote ARG</a></div> |
| 146 |
<div class="fancy-toc2"><a href="#_charlimit_n">6.3. --charlimit N</a></div> |
| 147 |
<div class="fancy-toc2"><a href="#_border_auto_off_on">6.4. --border auto|off|on</a></div> |
| 148 |
<div class="fancy-toc2"><a href="#_colsep_string">6.5. --colsep STRING</a></div> |
| 149 |
<div class="fancy-toc2"><a href="#_escape_esc">6.6. --escape ESC</a></div> |
| 150 |
<div class="fancy-toc2"><a href="#_linelimit_n">6.7. --linelimit N</a></div> |
| 151 |
<div class="fancy-toc2"><a href="#_limits_l_c_t">6.8. --limits L,C,T</a></div> |
| 152 |
<div class="fancy-toc2"><a href="#_list">6.9. --list</a></div> |
| 153 |
<div class="fancy-toc2"><a href="#_multiinsert_n">6.10. --multiinsert N</a></div> |
| 154 |
<div class="fancy-toc2"><a href="#_null_string">6.11. --null STRING</a></div> |
| 155 |
<div class="fancy-toc2"><a href="#_once">6.12. --once</a></div> |
| 156 |
<div class="fancy-toc2"><a href="#_quote_arg">6.13. --quote ARG</a></div> |
| 157 |
<div class="fancy-toc2"><a href="#_reset">6.14. --reset</a></div> |
| 158 |
<div class="fancy-toc2"><a href="#_rowsep_string">6.15. --rowsep STRING</a></div> |
| 159 |
<div class="fancy-toc2"><a href="#_screenwidth_n">6.16. --screenwidth N</a></div> |
| 160 |
<div class="fancy-toc2"><a href="#_tablename_name">6.17. --tablename NAME</a></div> |
| 161 |
<div class="fancy-toc2"><a href="#_tag_name">6.18. --tag NAME</a></div> |
| 162 |
<div class="fancy-toc2"><a href="#_textjsonb_boolean">6.19. --textjsonb BOOLEAN</a></div> |
| 163 |
<div class="fancy-toc2"><a href="#_title_arg">6.20. --title ARG</a></div> |
| 164 |
<div class="fancy-toc2"><a href="#_titlelimit_nbsp_n">6.21. --titlelimit N</a></div> |
| 165 |
<div class="fancy-toc2"><a href="#_verbose">6.22. --verbose</a></div> |
| 166 |
<div class="fancy-toc2"><a href="#_widths_list">6.23. --widths LIST</a></div> |
| 167 |
<div class="fancy-toc2"><a href="#_wordwrap_boolean">6.24. --wordwrap BOOLEAN</a></div> |
| 168 |
<div class="fancy-toc2"><a href="#_wrap_n">6.25. --wrap N</a></div> |
| 169 |
<div class="fancy-toc2"><a href="#_ww">6.26. --ww</a></div> |
| 170 |
</div> |
| 171 |
</details> |
| 172 |
</div> |
| 173 |
|
| 174 |
|
| 175 |
|
| 176 |
|
| 177 |
<h1 id="introduction"><span>1. </span>Introduction</h1> |
| 178 |
|
| 179 |
<p>In <a href="cli.html">Command Line Interface</a> (or <a href="cli.html">CLI</a>) for SQLite <a href="releaselog/3_52_0.html">version 3.52.0</a> |
| 180 |
(2026-03-06), the "<tt>.mode</tt>" <a href="cli.html#dotcmd">dot-command</a> was enhanced |
| 181 |
to provide more flexibility in rendering query results. But with |
| 182 |
flexibiliity comes complexity. For that reason, the operation of the |
| 183 |
".mode" command is now broken out into this separate document. |
| 184 |
|
| 185 |
</p><h2 id="summary_of_enhancements"><span>1.1. </span>Summary of enhancements</h2> |
| 186 |
|
| 187 |
<ul> |
| 188 |
<li><p> |
| 189 |
The ".mode" command now takes many new options that control |
| 190 |
the output format. Some of these new options |
| 191 |
replace settings that were formerly handled by separate |
| 192 |
dot-commands. For example, the new <a href="climode.html#opt-width">"--width" option</a> now |
| 193 |
replaces the legacy ".width" dot-command. (The legacy ".width" |
| 194 |
command is retained for compatibility but is no longer documented |
| 195 |
and is deprecated.) |
| 196 |
</p></li><li><p> |
| 197 |
The default mode for interactive sessions is changed to "qbox" |
| 198 |
modified with additional options |
| 199 |
<a href="climode.html#opt-quote">"--quote relaxed"</a>, |
| 200 |
<a href="climode.html#opt-limits">"--limits 5,300,20"</a>, |
| 201 |
<a href="climode.html#opt-textjsonb">"--textjsonb on"</a> |
| 202 |
and <a href="climode.html#opt-sw">"--sw auto"</a>. |
| 203 |
The legacy "list" mode is still the default for scripts, for |
| 204 |
backwards compatibility. |
| 205 |
</p></li><li><p> |
| 206 |
New modes "<tt>tty</tt>" and "<tt>batch</tt>" set the display mode |
| 207 |
to the default for interactive sessions and for batch sessions, |
| 208 |
respectively. |
| 209 |
</p></li><li><p> |
| 210 |
New mode "<tt>psql</tt>" makes the output look very similar to the |
| 211 |
output from PostgreSQL's "psql" command-line utility. |
| 212 |
</p></li><li><p> |
| 213 |
There are many new formatting possibilities. See details below. |
| 214 |
</p></li></ul> |
| 215 |
|
| 216 |
<h2 id="a_few_quick_examples_"><span>1.2. </span>A few quick examples:</h2> |
| 217 |
|
| 218 |
|
| 219 |
|
| 220 |
<p> To see the current output mode, use ".mode" with no arguments. |
| 221 |
|
| 222 |
</p><div class="codeblock"><pre>sqlite> <b>.mode</b> |
| 223 |
.mode qbox --limits on --quote relaxed --sw auto --textjsonb on |
| 224 |
sqlite> |
| 225 |
</pre></div> |
| 226 |
|
| 227 |
<p> Add "-v" for more detail: |
| 228 |
|
| 229 |
</p><div class="codeblock"><pre>sqlite> <b>.mode -v</b> |
| 230 |
.mode qbox --align "" --border on --blob-quote auto --colsep "" --escape auto |
| 231 |
--limits on --null "NULL" --quote relaxed --rowsep "" --sw auto --tablename "" |
| 232 |
--textjsonb on --titles on --widths "" --wordwrap off |
| 233 |
sqlite> |
| 234 |
</pre></div> |
| 235 |
|
| 236 |
<p> Use ".mode --list" to see a list of all available output modes: |
| 237 |
|
| 238 |
</p><div class="codeblock"><pre>sqlite> <b>.mode --list</b> |
| 239 |
available modes: ascii box c column count csv html insert jatom jobject json |
| 240 |
line list markdown off psql qbox quote table tabs tcl batch tty |
| 241 |
sqlite> |
| 242 |
</pre></div> |
| 243 |
|
| 244 |
<p> Change to "column" mode with no limits on the length |
| 245 |
of individual fields, but squeezing the width of all columns so that the |
| 246 |
table fits on the screen. |
| 247 |
|
| 248 |
</p><div class="codeblock"><pre>sqlite> <b>.mode column -limits off -sw auto </b> |
| 249 |
</pre></div> |
| 250 |
|
| 251 |
<p> Set up the display to be ASCII-art boxes (similar to MySQL) with text |
| 252 |
shown unquoted, with NULL value appearing as "NULL", with BLOB values |
| 253 |
appearing as hexadecimal, and with length limits set to show only the |
| 254 |
first 10 lines or first 1000 characters of any value. |
| 255 |
|
| 256 |
</p><div class="codeblock"><pre>sqlite> <b>.mode table -quote off -null NULL -blob hex -limits 10,1000 </b> |
| 257 |
</pre></div> |
| 258 |
|
| 259 |
<p> Use split-column mode ("split") but |
| 260 |
only for a single SQL input (due to the <a href="climode.html#opt-once">"--once" option</a>). |
| 261 |
|
| 262 |
</p><div class="codeblock"><pre>sqlite> <b>.mode --once split</b> |
| 263 |
sqlite> <b>SELECT name FROM pragma_function_list</b> |
| 264 |
...> <b>WHERE name GLOB 'json_*'</b> |
| 265 |
...> <b>ORDER BY name;</b> |
| 266 |
json_array json_group_array json_patch json_replace json_valid |
| 267 |
json_array_length json_group_object json_pretty json_set |
| 268 |
json_array_length json_insert json_pretty json_type |
| 269 |
json_error_position json_object json_quote json_type |
| 270 |
json_extract json_parse json_remove json_valid |
| 271 |
sqlite3> |
| 272 |
</pre></div> |
| 273 |
|
| 274 |
<p> Show usage hints about the ".mode" command: |
| 275 |
</p><div class="codeblock"><pre>sqlite> <b>.help mode </b> |
| 276 |
</pre></div> |
| 277 |
|
| 278 |
<h1 id="default_output_formats"><span>2. </span>Default Output Formats</h1> |
| 279 |
|
| 280 |
<p>As of version 3.52.0 (2026-03-06) the default |
| 281 |
format is: |
| 282 |
|
| 283 |
</p><ul> |
| 284 |
<li> Show query results in a table composed from Unicode |
| 285 |
box drawing symbols. ("box") |
| 286 |
</li><li> Quote all output as SQL literals, except do not quote |
| 287 |
text literals when it is not necessary to disambiguate them. |
| 288 |
("--quote relaxed") |
| 289 |
</li><li> Attempt to automatically squeeze the output table so |
| 290 |
that it fits within the width of the display. |
| 291 |
("--screenwidth auto") |
| 292 |
</li><li> Truncate multi-line values to show only the first 5 lines. |
| 293 |
("--linelimit 5") |
| 294 |
</li><li> Truncate long values to show only the first 300 characters. |
| 295 |
("--charlimit 300") |
| 296 |
</li><li> Truncate column titles to 20 characters. (--titlelimit 20) |
| 297 |
</li><li> When displaying a BLOB that is really JSONB, show the |
| 298 |
equivalent JSON text rather than the BLOB value. |
| 299 |
("--textjsonb on") |
| 300 |
</li></ul> |
| 301 |
|
| 302 |
<p>The above is the default when the CLI is connected to a terminal |
| 303 |
or console window. |
| 304 |
When run as a batch script (when both standard input and |
| 305 |
standard output are connected to a file or pipe rather than a keyboard |
| 306 |
or terminal window) the default format is the same as it always |
| 307 |
has been for SQLite version 3.51.0 and earlier: |
| 308 |
|
| 309 |
</p><ul> |
| 310 |
<li> Values appear as they are in the database, without quoting. |
| 311 |
</li><li> All columns for each row appear on a single line separated by "|". |
| 312 |
</li><li> Rows are terminated by a newline (U+000a). |
| 313 |
</li><li> There are no limits on the length of result values or output lines. |
| 314 |
</li></ul> |
| 315 |
|
| 316 |
<p>This "batch" mode is for legacy compatibility. You can force batch |
| 317 |
mode by launching the CLI with the "<tt>--batch</tt>" command-line option, |
| 318 |
or by issuing the "<tt>.mode batch</tt>" dot-command. |
| 319 |
|
| 320 |
</p><p>To see how the CLI is currently configured for result rendering, |
| 321 |
you can run the command "<tt>.mode</tt>" with no arguments, or |
| 322 |
run "<tt>.mode -v</tt>" for more detail. |
| 323 |
|
| 324 |
</p><h1 id="changing_output_formats"><span>3. </span>Changing Output Formats</h1> |
| 325 |
|
| 326 |
<p>There are many different output "modes". The default for terminals, |
| 327 |
where individual values are quoted and the whole result is displayed |
| 328 |
in a matrix composed from Unicode box-drawing characters, is called |
| 329 |
"qbox". The default for batch mode is called "list". You can get |
| 330 |
a complete list of all available modes by running the command |
| 331 |
"<tt>.mode --list</tt>" |
| 332 |
|
| 333 |
</p><p>To change output modes, run a command like |
| 334 |
"<tt>.mode </tt><i>NAME</i>" where <i>NAME</i> is one |
| 335 |
of the built-in modes named in the output of the |
| 336 |
"<tt>.mode --list</tt>" command. There are also many options |
| 337 |
available to fine-tune how results are displayed. You can probably |
| 338 |
discern many of those options by examining the output of |
| 339 |
the "<tt>.mode -v</tt>" command. |
| 340 |
|
| 341 |
</p><h2 id="output_mode_list_"><span>3.1. </span>Output mode "list"</h2> |
| 342 |
|
| 343 |
<p>The default output mode for scripts (and for all instances |
| 344 |
of the CLI prior to 3.52.0) is "list". In |
| 345 |
list mode, each row of a query result is written on one line of |
| 346 |
output and each column within that row is separated by a specific |
| 347 |
separator string. The default separator is a pipe symbol ("|"). |
| 348 |
List mode is especially useful when you are going to send the output |
| 349 |
of a query to another program (such as AWK) for additional processing.</p> |
| 350 |
|
| 351 |
<div class="codeblock"><pre>sqlite> <b>.mode list</b> |
| 352 |
sqlite> <b>CREATE TABLE tbl1(one,two);</b> |
| 353 |
sqlite> <b>INSERT INTO tbl1 VALUES('hello!',10),('goodbye',20);</b> |
| 354 |
sqlite> <b>SELECT * FROM tbl1;</b> |
| 355 |
hello!|10 |
| 356 |
goodbye|20 |
| 357 |
sqlite> |
| 358 |
</pre></div> |
| 359 |
|
| 360 |
<p>Use the "<tt>--colsep</tt> <i>STRING</i>" option on the |
| 361 |
"<tt>.mode</tt>" command to select a different column separator |
| 362 |
string. |
| 363 |
For example, to change the separator to a comma and |
| 364 |
a space, you could do this:</p> |
| 365 |
|
| 366 |
<div class="codeblock"><pre>sqlite> <b>.mode list --colsep ", "</b> |
| 367 |
sqlite> <b>select * from tbl1;</b> |
| 368 |
hello!, 10 |
| 369 |
goodbye, 20 |
| 370 |
sqlite> |
| 371 |
</pre></div> |
| 372 |
|
| 373 |
<p>Changing to a different mode might reset the column separator, or |
| 374 |
various other settings, back to a default value for the new mode. |
| 375 |
So you might need to repeat the "--colsep" option whenever you |
| 376 |
change modes if you want to continue using a non-standard separator. |
| 377 |
|
| 378 |
<a name="dotmodequote"></a> |
| 379 |
|
| 380 |
</p><h2 id="output_mode_quote_"><span>3.2. </span>Output mode "quote"</h2> |
| 381 |
|
| 382 |
<p>In "quote" mode, the output is formatted as SQL literals. Strings are |
| 383 |
enclosed in single-quotes and internal single-quotes are escaped by doubling. |
| 384 |
Blobs are displayed in hexadecimal blob literal notation (Ex: x'abcd'). |
| 385 |
Numbers are displayed as ASCII text and NULL values are shown as "NULL". |
| 386 |
All columns are separated from each other by a comma. |
| 387 |
|
| 388 |
</p><div class="codeblock"><pre>sqlite> <b>.mode quote</b> |
| 389 |
sqlite> <b>select * from tbl1;</b> |
| 390 |
'hello!',10 |
| 391 |
'goodbye',20 |
| 392 |
sqlite> |
| 393 |
</pre></div> |
| 394 |
|
| 395 |
<p>Quote mode is really an alias for list mode with some setting |
| 396 |
changes. The following two .mode commands give the same end result: |
| 397 |
|
| 398 |
</p><div class="codeblock"><pre>sqlite> <b>.mode quote</b> |
| 399 |
sqlite> <b>.mode list -quote on -colsep ","</b> |
| 400 |
</pre></div> |
| 401 |
|
| 402 |
<h2 id="other_list_like_output_modes"><span>3.3. </span>Other list-like output modes</h2> |
| 403 |
|
| 404 |
<p>Several output modes are convenience aliases for "list" |
| 405 |
with alternative values for the "<tt>--quote</tt>", "<tt>--colsep</tt>", |
| 406 |
"<tt>--rowsep</tt>", and "<tt>--null</tt>". Here is a complete |
| 407 |
list: |
| 408 |
|
| 409 |
</p><blockquote> |
| 410 |
<table border="1" cellpadding="5" cellspacing="0"> |
| 411 |
<tr> |
| 412 |
<th>Mode |
| 413 |
</th><th>--quote |
| 414 |
</th><th>--colsep |
| 415 |
</th><th>--rowsep |
| 416 |
</th><th>--null |
| 417 |
</th></tr> |
| 418 |
<tr> |
| 419 |
<td>ascii |
| 420 |
</td><td>csv |
| 421 |
</td><td>"\037" |
| 422 |
</td><td>"\036" |
| 423 |
</td><td>"" |
| 424 |
</td></tr> |
| 425 |
<tr> |
| 426 |
<td>c |
| 427 |
</td><td>tcl |
| 428 |
</td><td>, |
| 429 |
</td><td>"\n" |
| 430 |
</td><td>NULL |
| 431 |
</td></tr> |
| 432 |
<tr> |
| 433 |
<td>csv |
| 434 |
</td><td>csv |
| 435 |
</td><td>, |
| 436 |
</td><td>"\r\n" |
| 437 |
</td><td>"" |
| 438 |
</td></tr> |
| 439 |
<tr> |
| 440 |
<td>jatom |
| 441 |
</td><td>json |
| 442 |
</td><td>, |
| 443 |
</td><td>"\n" |
| 444 |
</td><td>null |
| 445 |
</td></tr> |
| 446 |
<tr> |
| 447 |
<td>list |
| 448 |
</td><td>off |
| 449 |
</td><td>| |
| 450 |
</td><td>"\n" |
| 451 |
</td><td>"" |
| 452 |
</td></tr> |
| 453 |
<tr> |
| 454 |
<td>quote |
| 455 |
</td><td>on |
| 456 |
</td><td>, |
| 457 |
</td><td>"\n" |
| 458 |
</td><td>NULL |
| 459 |
</td></tr> |
| 460 |
<tr> |
| 461 |
<td>tables |
| 462 |
</td><td>csv |
| 463 |
</td><td>"\t" |
| 464 |
</td><td>"\n" |
| 465 |
</td><td>"" |
| 466 |
</td></tr> |
| 467 |
<tr> |
| 468 |
<td>tcl |
| 469 |
</td><td>tcl |
| 470 |
</td><td>, |
| 471 |
</td><td>"\n" |
| 472 |
</td><td>"" |
| 473 |
</td></tr> |
| 474 |
</table> |
| 475 |
</blockquote> |
| 476 |
|
| 477 |
<a name="linemode"></a> |
| 478 |
|
| 479 |
<h2 id="output_mode_line_"><span>3.4. </span>Output mode "line"</h2> |
| 480 |
|
| 481 |
<p>In "line" mode, each column in a row of the database |
| 482 |
is shown on a line by itself. Each line consists of the column |
| 483 |
name, a colon, and the column data. Successive records are |
| 484 |
separated by a blank line. Here is an example of line mode |
| 485 |
output:</p> |
| 486 |
|
| 487 |
<div class="codeblock"><pre>sqlite> <b>.mode line</b> |
| 488 |
sqlite> <b>select * from tbl1;</b> |
| 489 |
one: hello! |
| 490 |
two: 10 |
| 491 |
|
| 492 |
one: goodbye |
| 493 |
two: 20 |
| 494 |
sqlite> |
| 495 |
</pre></div> |
| 496 |
|
| 497 |
<p>The "line" mode is particularly useful when displaying results |
| 498 |
that have a large number of columns, too many columns to fit on a |
| 499 |
single line of your display. Line mode avoids overflowing lines |
| 500 |
of your display and thus makes the output easier to read. |
| 501 |
|
| 502 |
</p><p>Before version 3.52.0, the separator was an equal sign rather |
| 503 |
than a colon. In other words, the first line of the example above |
| 504 |
used to say "one = hello!" instead of |
| 505 |
"one: hello!". You can restore the pre-3.52.0 |
| 506 |
formatting using the "--colsep <i>TEXT</i>" option to the |
| 507 |
".mode" command, where <i>TEXT</i> is the desired separator. |
| 508 |
Hence, to get the legacy formatting, you say: |
| 509 |
|
| 510 |
</p><div class="codeblock"><pre>sqlite> <b>.mode line --colsep " = "</b> |
| 511 |
</pre></div> |
| 512 |
|
| 513 |
<a name="clmnr"></a> |
| 514 |
|
| 515 |
<h2 id="tabular_output_modes"><span>3.5. </span>Tabular output modes</h2> |
| 516 |
|
| 517 |
<p>In tabular modes, each record is shown on a separate line with the |
| 518 |
data aligned in columns. The "column" mode is an example:</p> |
| 519 |
|
| 520 |
<div class="codeblock"><pre>sqlite> <b>.mode column</b> |
| 521 |
sqlite> <b>select * from tbl1;</b> |
| 522 |
one two |
| 523 |
-------- --- |
| 524 |
hello! 10 |
| 525 |
goodbye 20 |
| 526 |
sqlite> |
| 527 |
</pre></div> |
| 528 |
|
| 529 |
<p>In "column" mode (and also in "box", "psql", "qbox", "table", and |
| 530 |
"markdown") the width of columns adjusts automatically. You can override |
| 531 |
this, providing a specified width for each column using the "--width" option |
| 532 |
to the ".mode" command. |
| 533 |
The argument to "--width" is a list of space- or comma-separated |
| 534 |
integers which are the number characters to devote to each column. |
| 535 |
Negative numbers mean right-justify. A value of 0 means the |
| 536 |
width of the column automatically adjusts to its content. The default value |
| 537 |
for "--width" is 0 and 0 is also the default for any column to the |
| 538 |
right of those that are specified using "--width". |
| 539 |
Thus:</p> |
| 540 |
|
| 541 |
<div class="codeblock"><pre>sqlite> <b>.mode --width 12,-6</b> |
| 542 |
sqlite> <b>select * from tbl1;</b> |
| 543 |
one two |
| 544 |
------------ ------ |
| 545 |
hello! 10 |
| 546 |
goodbye 20 |
| 547 |
sqlite> |
| 548 |
</pre></div> |
| 549 |
|
| 550 |
<p>The command "<tt>.mode --width 0</tt>" resets all column |
| 551 |
widths back to zero and hence causes all column widths to be determined |
| 552 |
automatically. |
| 553 |
|
| 554 |
</p><p>Here are examples of other tabular modes: |
| 555 |
|
| 556 |
</p><div class="codeblock"><pre>sqlite> <b>.mode box --widths 0</b> |
| 557 |
sqlite> <b>select * from tbl1;</b> |
| 558 |
╭─────────┬─────╮ |
| 559 |
│ one │ two │ |
| 560 |
╞═════════╪═════╡ |
| 561 |
│ hello! │ 10 │ |
| 562 |
│ goodbye │ 20 │ |
| 563 |
╰─────────┴─────╯ |
| 564 |
sqlite> <b>.mode markdown</b> |
| 565 |
sqlite> <b>select * from tbl1;</b> |
| 566 |
| one | two | |
| 567 |
|---------|-----| |
| 568 |
| hello! | 10 | |
| 569 |
| goodbye | 20 | |
| 570 |
sqlite> <b>.mode psql</b> |
| 571 |
sqlite> <b>select * from tbl1;</b> |
| 572 |
one | two |
| 573 |
---------+----- |
| 574 |
hello! | 10 |
| 575 |
goodbye | 20 |
| 576 |
sqlite> <b>.mode qbox</b> |
| 577 |
sqlite> <b>select * from tbl1;</b> |
| 578 |
╭───────────┬─────╮ |
| 579 |
│ one │ two │ |
| 580 |
╞═══════════╪═════╡ |
| 581 |
│ 'hello!' │ 10 │ |
| 582 |
│ 'goodbye' │ 20 │ |
| 583 |
╰───────────┴─────╯ |
| 584 |
sqlite> <b>.mode table</b> |
| 585 |
sqlite> <b>select * from tbl1;</b> |
| 586 |
+---------+-----+ |
| 587 |
| one | two | |
| 588 |
+---------+-----+ |
| 589 |
| hello! | 10 | |
| 590 |
| goodbye | 20 | |
| 591 |
+---------+-----+ |
| 592 |
sqlite> |
| 593 |
</pre></div> |
| 594 |
|
| 595 |
<p>There "qbox" mode is really just a convenience variant |
| 596 |
of "box" that automatically enables <a href="climode.html#opt-quote">"--quote on"</a>. |
| 597 |
Similarly "psql" (so named because it formats results in a |
| 598 |
manner similar to the "psql" command line utility of PostgreSQL) |
| 599 |
is really just "table" mode with the addition of the |
| 600 |
<a href="climode.html#opt-border">"--border off" option</a>. |
| 601 |
|
| 602 |
<a name="wrap1"></a> |
| 603 |
|
| 604 |
</p><h2 id="line_wrapping"><span>3.6. </span>Line wrapping</h2> |
| 605 |
|
| 606 |
<p>The tabular modes accept some additional options to control formatting. |
| 607 |
The "--wrap <i>N</i>" option (where <i>N</i> is an integer) causes columns |
| 608 |
to wrap text that is longer than N characters. Wrapping is disabled if |
| 609 |
N is zero. |
| 610 |
|
| 611 |
</p><div class="codeblock"><pre>sqlite> <b>insert into tbl1 values('The quick fox jumps over a lazy brown dog.',90);</b> |
| 612 |
sqlite> <b>.mode box --wrap 30</b> |
| 613 |
sqlite> <b>select * from tbl1 where two>50;</b> |
| 614 |
╭────────────────────────────────┬─────╮ |
| 615 |
│ one │ two │ |
| 616 |
╞════════════════════════════════╪═════╡ |
| 617 |
│ The quick fox jumps over a laz │ 90 │ |
| 618 |
│ y brown dog. │ │ |
| 619 |
╰────────────────────────────────┴─────╯ |
| 620 |
sqlite> |
| 621 |
</pre></div> |
| 622 |
|
| 623 |
<p>Wrapping happens after exactly <i>N</i> characters, |
| 624 |
which might be in the middle of a word. |
| 625 |
To wrap at a word boundary, add the "--wordwrap on" option |
| 626 |
(or just "-ww" for short): |
| 627 |
|
| 628 |
</p><div class="codeblock"><pre>sqlite> <b>.mode box --wrap 30 -ww</b> |
| 629 |
sqlite> <b>select * from tbl1 where two>50;</b> |
| 630 |
╭────────────────────────────────┬─────╮ |
| 631 |
│ one │ two │ |
| 632 |
╞════════════════════════════════╪═════╡ |
| 633 |
│ The quick fox jumps over a │ 90 │ |
| 634 |
│ lazy brown dog. │ │ |
| 635 |
╰────────────────────────────────┴─────╯ |
| 636 |
sqlite> |
| 637 |
</pre></div> |
| 638 |
|
| 639 |
<a name="qbox"></a> |
| 640 |
|
| 641 |
<p>The command ".mode box --wrap 60 --quote on" is so useful for general-purpose |
| 642 |
database queries that it is given its own alias. Instead of typing out |
| 643 |
that whole 30-character command, you can just say ".mode qbox". |
| 644 |
|
| 645 |
</p><h2 id="output_mode_insert_"><span>3.7. </span>Output mode "insert"</h2> |
| 646 |
|
| 647 |
<p>Another useful output mode is "insert". In insert mode, the output |
| 648 |
is formatted to look like SQL INSERT statements. Use insert |
| 649 |
mode to generate text that can later be used to input data into a |
| 650 |
different database.</p> |
| 651 |
|
| 652 |
<p>When specifying insert mode, you have to give an extra argument |
| 653 |
which is the name of the table to be inserted into. For example:</p> |
| 654 |
|
| 655 |
<div class="codeblock"><pre>sqlite> <b>.mode insert new_table</b> |
| 656 |
sqlite> <b>select * from tbl1 where two<50;</b> |
| 657 |
INSERT INTO new_table VALUES('hello',10), |
| 658 |
('goodbye',20); |
| 659 |
sqlite> |
| 660 |
</pre></div> |
| 661 |
|
| 662 |
<p>Multiple rows will be added as additional tuples onto the end of |
| 663 |
the INSERT statement until the size of the INSERT statement (in bytes) |
| 664 |
exceeds the <a href="climode.html#opt-multiinsert">"--multiinsert" setting</a> (default: 3000). To force every |
| 665 |
row to be on a separate INSERT statement set --multiinsert to 0. |
| 666 |
|
| 667 |
</p><p>If the <tt>--titles on</tt> option is included in the |
| 668 |
<tt>.mode</tt> command, then the INSERT output will |
| 669 |
include that list of column names:</p> |
| 670 |
|
| 671 |
<div class="codeblock"><pre>sqlite> <b>.mode insert mytable --titles on</b> |
| 672 |
sqlite> <b>select 1 a, 2 b, 3 c;</b> |
| 673 |
INSERT INTO mytable(a,b,c) VALUES(1,2,3); |
| 674 |
sqlite> |
| 675 |
</pre></div> |
| 676 |
|
| 677 |
<a name="split"></a> |
| 678 |
|
| 679 |
<h2 id="output_mode_split_"><span>3.8. </span>Output mode "split"</h2> |
| 680 |
|
| 681 |
<p>The column-split output mode, called use "split" and invoked using |
| 682 |
"<tt>.mode split</tt>", is a special case of the tabular "column" mode. |
| 683 |
The "split" mode only works on queries that return a single column and |
| 684 |
when the <a href="climode.html#opt-sw">"--screenwidth" option</a> is not "off". Instead of displaying |
| 685 |
the results in a single vertical column, the "split" mode tries to |
| 686 |
wrap that column vertically so that the final output is two or more |
| 687 |
columns. The CLI creates as many columns as will fit on the screen. |
| 688 |
|
| 689 |
</p><p>The following example demonstrates using the "split" mode |
| 690 |
to list all built-in SQL functions whose names begin with "c" |
| 691 |
on a screen that is 55 characters wide: |
| 692 |
|
| 693 |
</p><div class="codeblock"><pre>sqlite> <b>.mode --once split --sw 55</b> |
| 694 |
sqlite> <b>SELECT name FROM pragma_function_list</b> |
| 695 |
...> <b>WHERE name GLOB 'c*'</b> |
| 696 |
...> <b>ORDER BY name;</b> |
| 697 |
ceil coalesce cosh current_date |
| 698 |
ceiling concat count current_time |
| 699 |
changes concat_ws count current_timestamp |
| 700 |
char cos cume_dist |
| 701 |
sqlite3> |
| 702 |
</pre></div> |
| 703 |
|
| 704 |
<p>The CLI uses "split" mode internally to help it implement |
| 705 |
dot-commands like <a href="cli.html#dtables">.tables</a> |
| 706 |
|
| 707 |
</p><h2 id="output_modes_count_and_off_"><span>3.9. </span>Output modes "count" and "off"</h2> |
| 708 |
|
| 709 |
<p>The output modes "count" and "off" do not show any actual |
| 710 |
query results. Instead they show just the number of rows of |
| 711 |
results, or nothing at all, respectively. These modes are |
| 712 |
useful for running timing tests on queries that generate a lot |
| 713 |
of output where you do not care what the output is but only |
| 714 |
how long it takes to compute. |
| 715 |
|
| 716 |
<a name="mbatch"></a> |
| 717 |
|
| 718 |
</p><h2 id="output_modes_batch_and_tty_"><span>3.10. </span>Output modes "batch" and "tty"</h2> |
| 719 |
|
| 720 |
<p>Two aliases "batch" and "tty" mean the default output mode |
| 721 |
for when the CLI is started as a script ("batch") and when the |
| 722 |
CLI is started from a terminal or console ("tty"). |
| 723 |
|
| 724 |
<a name="usermode"></a> |
| 725 |
|
| 726 |
</p><h2 id="user_defined_output_modes"><span>3.11. </span>User Defined Output Modes</h2> |
| 727 |
|
| 728 |
<p>The <a href="climode.html#opt-tag">"--tag" option</a> allows new customized output modes to be |
| 729 |
defined and named. The argument to --tag is the name of a new mode. |
| 730 |
All settings defined before the --tag are captured in that name and |
| 731 |
can be restored at a later time using "<tt>.mode </tt><i>TAGNAME</i>". |
| 732 |
|
| 733 |
<a name="ctrlchr"></a> |
| 734 |
|
| 735 |
</p><h1 id="escaping_control_characters"><span>4. </span>Escaping Control Characters</h1> |
| 736 |
|
| 737 |
<p> |
| 738 |
By default (since version 3.50.0), the CLI avoids |
| 739 |
displaying most control characters in the range of U+0001 through U+001f |
| 740 |
even when such characters are within content strings that the CLI is |
| 741 |
trying to output. This is done to avoid problems when the output |
| 742 |
is viewed on a device that interprets |
| 743 |
<a href="https://en.wikipedia.org/wiki/ANSI_escape_code">ANSI escape codes</a>. |
| 744 |
The CLI avoids showing <em>most</em> control characters, but makes |
| 745 |
the following exceptions: TAB (0x09), LF (0x0a) and CRLF (0x0d,0x0a). |
| 746 |
The exceptions are passed through into the output unchanged. |
| 747 |
|
| 748 |
</p><p> |
| 749 |
The default behavior is for the CLI to display most control characters |
| 750 |
the same way the "cat -A" command does in Linux: For control character X, |
| 751 |
display two characters "^Y" where Y is X+0x40. For example, |
| 752 |
the "ESC" character (0x1b) is rendered as "^[" and |
| 753 |
the backspace character (0x08) is rendered as "^H". |
| 754 |
|
| 755 |
</p><p> |
| 756 |
The rendering of control characters is governed by the "--escape T" |
| 757 |
command-line option to the CLI, or to the ".mode" dot-command, where T is |
| 758 |
the control-character handling algorithm. The default |
| 759 |
T is "ascii". If you change it to "--escape symbol" then control |
| 760 |
characters are mapped into printable unicode values in the |
| 761 |
range of U+2401 through U+241f. For example, ESC is rendered as "␛" |
| 762 |
and backspace becomes "␈". Setting "--escape off" turns off all |
| 763 |
control character mapping so that the characters output by the CLI are |
| 764 |
exactly what are in the database. |
| 765 |
|
| 766 |
|
| 767 |
<a name="crlf"></a> |
| 768 |
|
| 769 |
</p><h1 id="line_endings"><span>5. </span>Line Endings</h1> |
| 770 |
|
| 771 |
<p>The default line ending on Windows can be either "\r\n" (CRLF) |
| 772 |
or "\n" NL. Even though it is closely related to output formatting, |
| 773 |
the line ending choice is controlled by a completely separate |
| 774 |
subsystem of the CLI implementation and thus has its own |
| 775 |
".crlf" dot-command, separate from ".mode". |
| 776 |
|
| 777 |
</p><p>Use ".crlf on" to set the CRLF line ending and |
| 778 |
".crlf off" for NL. |
| 779 |
As is traditional for Windows, CRLF is the default. However, this |
| 780 |
causes some outputs to be different than on non-Windows platforms due |
| 781 |
to the added "\r" characters. To cause the CLI to output results on |
| 782 |
Windows that are identical to the results on all other systems, |
| 783 |
run ".crlf off". |
| 784 |
|
| 785 |
</p><p>On non-Windows platforms, the ".crlf" command is a no-op and the |
| 786 |
crlf mode is always "off". For <a href="cli.html#csvout">CSV output</a>, the line ending is always |
| 787 |
"\r\n" regardless of the .crlf setting, due to requirements of |
| 788 |
RFC-4180. |
| 789 |
|
| 790 |
</p><h1 id="details_on_mode_syntax_and_options"><span>6. </span>Details On .mode Syntax And Options</h1> |
| 791 |
|
| 792 |
<p>The mode command works by processing its arguments from left to right. |
| 793 |
Order can be important. For example, settings to the left of |
| 794 |
the <a href="climode.html#opt-reset">"--reset" option</a> will likely be rolled back because the reset is |
| 795 |
processed after those settings. But settings to the right will survive |
| 796 |
because the settings are changed after the reset. |
| 797 |
|
| 798 |
</p><p>Use the ".help mode" to get a quick summary of the options available |
| 799 |
on the ".mode" command. Keep reading below for details. |
| 800 |
|
| 801 |
<a name="opt-align"></a> |
| 802 |
|
| 803 |
</p><h2 id="_align_string"><span>6.1. </span><tt>--align</tt> <i>STRING</i></h2> |
| 804 |
|
| 805 |
<p> |
| 806 |
The alignment value is a string composed of characters |
| 807 |
'L', 'C', and 'R' that determine how text is justified |
| 808 |
in tabular modes. 'L' means left (the default), 'C' |
| 809 |
means centered, and 'R' means right. Each letter in the |
| 810 |
string corresponds to a single column in the tabular |
| 811 |
output. If the output has more columns than there are |
| 812 |
letters in the alignment string, then the remaining columns |
| 813 |
are all left-justified. |
| 814 |
|
| 815 |
<a name="opt-blob"></a> |
| 816 |
|
| 817 |
</p><h2 id="_blob_quote_arg"><span>6.2. </span><tt>--blob-quote <i>ARG</i></tt></h2> |
| 818 |
|
| 819 |
<p> |
| 820 |
The --blob-quote option (which can be abbreviated as just "--blob") |
| 821 |
determines how BLOB values are displayed. The argument can be |
| 822 |
one of "auto", "text", "sql", "hex", "tcl", "json", or "size". |
| 823 |
|
| 824 |
</p><p> |
| 825 |
The default --blob-quote setting is "auto" which means that the system |
| 826 |
selects an appropriate method of displaying BLOB values based on the |
| 827 |
current mode and/or the current <a href="climode.html#opt-quote">"--quote" setting</a>. The default is |
| 828 |
"auto" and that works best in most circumstances. |
| 829 |
|
| 830 |
</p><p> |
| 831 |
If ARG is "text", that means that the bytes of the BLOB value are |
| 832 |
interpreted as UTF-8 text and displayed as text. "sql" means that |
| 833 |
the BLOB is rendered as an SQL BLOB literal. "hex" means the BLOB |
| 834 |
value is displayed as hexadecimal. "tcl" means that the BLOB value |
| 835 |
is rendered as a double-quoted string where each byte is represented |
| 836 |
by traditional C-language backslash octal notation. For example, |
| 837 |
a BLOB value of x'424c4f42' would be rendered as |
| 838 |
<tt>"\102\114\117\102"</tt>. The "json" encoding is similar except |
| 839 |
that the individual bytes use JSON escape: |
| 840 |
<tt>"\u0042\u004c\u004f\u0042"</tt>. The "size" encoding does not |
| 841 |
try to show the BLOB content at all, but instead just shows the |
| 842 |
size of the BLOB as text, like this: <tt>(482-byte blob)</tt> |
| 843 |
|
| 844 |
<a name="opt-charlimit"></a> |
| 845 |
|
| 846 |
</p><h2 id="_charlimit_n"><span>6.3. </span><tt>--charlimit</tt> <i>N</i></h2> |
| 847 |
|
| 848 |
<p> |
| 849 |
Limit the number of characters to display for a single value in |
| 850 |
the output. If the output text is longer than N characters, it |
| 851 |
is truncated and an ellipsis are added at the end. If N is zero, |
| 852 |
then the character limit is disabled. |
| 853 |
|
| 854 |
</p><p> |
| 855 |
The character limit works in all output modes. |
| 856 |
|
| 857 |
</p><p> |
| 858 |
See also the <a href="climode.html#opt-linelimit">"--linelimit" option</a>, the <a href="climode.html#opt-limits">"--limits" option</a>, |
| 859 |
and the <a href="climode.html#opt-tlim">"--titlelimit" option</a>. |
| 860 |
</p><p> |
| 861 |
|
| 862 |
<a name="opt-border"></a> |
| 863 |
|
| 864 |
</p><h2 id="_border_auto_off_on"><span>6.4. </span><tt>--border</tt> auto|off|on</h2> |
| 865 |
|
| 866 |
<p>The --border setting control whether or not a box appears around |
| 867 |
the outside of the entire result, or if only the interior divider |
| 868 |
lines are shown. This setting only affects "box" and "table" modes. |
| 869 |
The value of this setting is "off" then the border is omitted. |
| 870 |
Otherwise the border is shown. The default behavior is to show |
| 871 |
the outer border. |
| 872 |
|
| 873 |
The "psql" mode is an alias for the "table" mode but with |
| 874 |
the --border setting changed to "off". |
| 875 |
|
| 876 |
<a name="opt-colsep"></a> |
| 877 |
|
| 878 |
</p><h2 id="_colsep_string"><span>6.5. </span><tt>--colsep</tt> <i>STRING</i></h2> |
| 879 |
|
| 880 |
<p> |
| 881 |
Set the column-separator to <i>STRING</i>. Use double-quotes and |
| 882 |
backslash-escapes to use spaces and special characters like tab (\t) |
| 883 |
as part of the column-separator. |
| 884 |
|
| 885 |
|
| 886 |
<a name="opt-escape"></a> |
| 887 |
|
| 888 |
</p><h2 id="_escape_esc"><span>6.6. </span><tt>--escape</tt> <i>ESC</i></h2> |
| 889 |
|
| 890 |
<p> |
| 891 |
Control how control characters are escaped in all output. |
| 892 |
Possible values for <i>ESC</i> are "off", "ascii", and "symbol". |
| 893 |
The "ascii" value is the default and is recommended. |
| 894 |
|
| 895 |
<a name="opt-linelimit"></a> |
| 896 |
|
| 897 |
</p><h2 id="_linelimit_n"><span>6.7. </span><tt>--linelimit</tt> <i>N</i></h2> |
| 898 |
|
| 899 |
<p> |
| 900 |
Do not display more than the first <i>N</i> lines of text for any |
| 901 |
single value. Lines can be split due to the text containing |
| 902 |
newline characters (U+000a) or due to column wrapping. Either way, |
| 903 |
this option causes the first <i>N</i> lines to be shown and then |
| 904 |
shows ellipsis for the <i>N+1</i>-th line. Set N to zero to |
| 905 |
disable the line limiter. |
| 906 |
|
| 907 |
</p><p> |
| 908 |
The line limiter only works for tabular modes and for "line" mode. |
| 909 |
It is ignored in all other modes. |
| 910 |
|
| 911 |
</p><p> |
| 912 |
See also the <a href="climode.html#opt-charlimit">"--charlimit" option</a>, the <a href="climode.html#opt-limits">"--limits" option</a>, |
| 913 |
and the <a href="climode.html#opt-tlim">"--titlelimit" option</a> |
| 914 |
|
| 915 |
<a name="opt-limits"></a> |
| 916 |
|
| 917 |
</p><h2 id="_limits_l_c_t"><span>6.8. </span><tt>--limits</tt> <i>L,C,T</i></h2> |
| 918 |
|
| 919 |
<p> |
| 920 |
This option is shorthand for |
| 921 |
"<tt>--linelimit L --charlimit C --titlelimit T</tt>". |
| 922 |
In other words, when the argument is three integers L, C, and T separate |
| 923 |
by commas, then the line limit, the character limit, and the title |
| 924 |
limit are set to L, C, and T, respectively. If the ",T" is omitted, |
| 925 |
then only the line limit and character limit are changed and the title |
| 926 |
limit is unchanged. The argument to --limits can also |
| 927 |
be "off" which is an alias for "0,0,0" or "on" which sets all |
| 928 |
limits to reasonable built-in defaults. |
| 929 |
|
| 930 |
<a name="opt-list"></a> |
| 931 |
|
| 932 |
</p><h2 id="_list"><span>6.9. </span><tt>--list</tt></h2> |
| 933 |
|
| 934 |
<p> |
| 935 |
The "--list" option takes no arguments. It causes the ".mode" command |
| 936 |
to print a list of all available modes, including user-defined modes |
| 937 |
created using the <a href="climode.html#opt-tag">"--tag" option</a>. |
| 938 |
|
| 939 |
<a name="opt-multiinsert"></a> |
| 940 |
|
| 941 |
</p><h2 id="_multiinsert_n"><span>6.10. </span><tt>--multiinsert</tt> <i>N</i></h2> |
| 942 |
|
| 943 |
<p> |
| 944 |
The <tt>--multiinsert</tt> setting influences the output when |
| 945 |
in "insert" mode. The argument N governs how many rows of result |
| 946 |
are included in each INSERT statement. When N is positive, multiple |
| 947 |
rows are added as extra tuples in an INSERT statement until the total |
| 948 |
size of the INSERT statement exceeds N bytes. Subsequent rows will |
| 949 |
go into a new INSERT statement. |
| 950 |
|
| 951 |
</p><p> |
| 952 |
Setting <tt>--multiinsert</tt> to two or three thousand can make |
| 953 |
".mode insert" output easier to read, and also faster to parse when |
| 954 |
moving the content to a different database engine. |
| 955 |
|
| 956 |
<a name="opt-null"></a> |
| 957 |
|
| 958 |
</p><h2 id="_null_string"><span>6.11. </span><tt>--null</tt> <i>STRING</i></h2> |
| 959 |
|
| 960 |
<p> |
| 961 |
The STRING argument becomes the text that the CLI displays for |
| 962 |
NULL values. Common choices are an empty string (which must be |
| 963 |
quoted as <tt>""</tt>), "null", and "NULL". |
| 964 |
|
| 965 |
<a name="opt-once"></a> |
| 966 |
|
| 967 |
</p><h2 id="_once"><span>6.12. </span><tt>--once</tt></h2> |
| 968 |
|
| 969 |
<p> |
| 970 |
The "--once" option means all setting changes to the right |
| 971 |
are transient and will rollback after the next SQL statement |
| 972 |
is run. |
| 973 |
|
| 974 |
</p><p> In the previous sentence, "next SQL statement" means the |
| 975 |
all SQL statements up to and including the next complete line |
| 976 |
of SQL input that ends with a semicolon. If two or more SQL |
| 977 |
queries occur on the same input line, all of them will be run |
| 978 |
before the mode is rolled back. |
| 979 |
So if you do (for example) |
| 980 |
"<tt>.mode --once split</tt>" then enter two SELECT statements |
| 981 |
on the next line of input, both of those SELECTs will be processed |
| 982 |
using "split" mode. |
| 983 |
|
| 984 |
<a name="opt-quote"></a> |
| 985 |
|
| 986 |
</p><h2 id="_quote_arg"><span>6.13. </span><tt>--quote</tt> <i>ARG</i></h2> |
| 987 |
|
| 988 |
<p> |
| 989 |
The --quote option determines how text values are quoted prior to rendered. |
| 990 |
Possible arguments are: |
| 991 |
"off", "on", "sql", "relaxed", "csv", "html", "tcl", or "json". |
| 992 |
|
| 993 |
</p><p> |
| 994 |
When --quote is "off" the text is displayed as it appears in the |
| 995 |
database with no escapes. |
| 996 |
|
| 997 |
</p><p> |
| 998 |
The --quote value of "sql" means to display text as SQL text literals |
| 999 |
within single-quotes. A value of "relaxed" is similar, except that |
| 1000 |
"relaxed" tries to avoid adding extra quoting syntax in cases where it |
| 1001 |
is not needed. |
| 1002 |
A value of "csv" means to quote text according to the rules specified |
| 1003 |
for CSV file in RFC-4180. A value of "html" means to escape text for |
| 1004 |
display in HTML, so that "<" characters are rendered as "&lt;", and |
| 1005 |
so forth. "tcl" means to put the text inside double-quotes and use |
| 1006 |
backslash escape, as if for TCL, C, Perl, and other common programming |
| 1007 |
languages. "json" is similar to "tcl" except that it uses only |
| 1008 |
backslash escapes that are valid JSON. |
| 1009 |
|
| 1010 |
</p><p> |
| 1011 |
The value of "on" is similar to "sql" except that "on" |
| 1012 |
also changes the <a href="climode.html#opt-null">"--null" option</a> to be "NULL". |
| 1013 |
|
| 1014 |
<a name="opt-reset"></a> |
| 1015 |
|
| 1016 |
</p><h2 id="_reset"><span>6.14. </span><tt>--reset</tt></h2> |
| 1017 |
|
| 1018 |
<p> |
| 1019 |
The --reset option leaves the current mode unchanged, but it sets |
| 1020 |
all the other various settings (--colsep, --null, --rowsep, and so |
| 1021 |
forth) back to whatever the default is for that particular mode. |
| 1022 |
|
| 1023 |
<a name="opt-rowsep"></a> |
| 1024 |
|
| 1025 |
</p><h2 id="_rowsep_string"><span>6.15. </span><tt>--rowsep</tt> <i>STRING</i></h2> |
| 1026 |
|
| 1027 |
<p> |
| 1028 |
Use STRING as the row separator. |
| 1029 |
|
| 1030 |
<a name="opt-sw"></a> |
| 1031 |
|
| 1032 |
</p><h2 id="_screenwidth_n"><span>6.16. </span><tt>--screenwidth</tt> <i>N</i></h2> |
| 1033 |
|
| 1034 |
<p> |
| 1035 |
This setting tells the CLI that the display device is N characters |
| 1036 |
wide. The CLI then attempts to squeeze tabular and "line" mode |
| 1037 |
outputs to fit within that many characters. If the argument is 0 |
| 1038 |
or "off", that means the screenwidth is ignored an no attempt is made |
| 1039 |
to squeeze the results down to any particular width. |
| 1040 |
|
| 1041 |
</p><p> |
| 1042 |
The default value is "auto" which causes the CLI interrogate the display |
| 1043 |
device to determine its actual width before each query is run. With the |
| 1044 |
"auto" value, if you are running the CLI in a terminal or console window |
| 1045 |
and you drag the window wider, the screenwidth setting is adjusted |
| 1046 |
automatically. |
| 1047 |
|
| 1048 |
</p><p> |
| 1049 |
The "--sw N" setting is a shorthand for "--screenwidth N". |
| 1050 |
|
| 1051 |
</p><p> |
| 1052 |
The screenwidth is only used by <a href="climode.html#clmnr">tabular modes</a> and by the <a href="climode.html#linemode">line mode</a>. |
| 1053 |
Screenwidth limiting is "best effort" in the sense that the CLI works |
| 1054 |
hard to squeeze the output to fit tha specified width, but might exceed |
| 1055 |
the specified screenwidth if there is no way around it. For example, |
| 1056 |
if the --screenwidth is 40 and you attempt to display a result with 50 |
| 1057 |
different columns, there is clearly no way that will fit, and the screenwidth |
| 1058 |
will be exceeded in that case. |
| 1059 |
|
| 1060 |
<a name="opt-tabname"></a> |
| 1061 |
|
| 1062 |
</p><h2 id="_tablename_name"><span>6.17. </span><tt>--tablename</tt> <i>NAME</i></h2> |
| 1063 |
|
| 1064 |
<p> |
| 1065 |
Use "NAME" as the name of the table to insert into for "insert" mode. |
| 1066 |
|
| 1067 |
<a name="opt-tag"></a> |
| 1068 |
|
| 1069 |
</p><h2 id="_tag_name"><span>6.18. </span><tt>--tag</tt> <i>NAME</i></h2> |
| 1070 |
|
| 1071 |
<p> |
| 1072 |
This option creates a new mode called NAME. After using this |
| 1073 |
tag, you can say ".mode NAME" and all mode settings that were |
| 1074 |
in effect when the --tag option was first executed will be |
| 1075 |
restored. |
| 1076 |
|
| 1077 |
</p><p> |
| 1078 |
The order of options is important here. The new mode will consist |
| 1079 |
of all mode settings that are in effect at the moment that the |
| 1080 |
"--tag" option is countered. Hence changes to the left of "--tag" |
| 1081 |
are part of the new mode, but changes to the right of "--tag" are not. |
| 1082 |
|
| 1083 |
<a name="opt-textjsonb"></a> |
| 1084 |
|
| 1085 |
</p><h2 id="_textjsonb_boolean"><span>6.19. </span><tt>--textjsonb</tt> <i>BOOLEAN</i></h2> |
| 1086 |
|
| 1087 |
<p> |
| 1088 |
If --testjsonb is enabled, then when displaying a BLOB value, the |
| 1089 |
CLI first checks to see if the value is really a <a href="json1.html#jsonbx">JSONB</a> blob, and |
| 1090 |
if it is, it displays the equivalent text for that JSONB using |
| 1091 |
whatever quoting mechanism is specified by the <a href="climode.html#opt-quote">"--quote" option</a>. |
| 1092 |
If the BLOB to be displayed is not JSONB, then it is rendered using |
| 1093 |
the current <a href="climode.html#opt-blob">"--blob-quote" option</a>. |
| 1094 |
|
| 1095 |
<a name="opt-title"></a> |
| 1096 |
|
| 1097 |
</p><h2 id="_title_arg"><span>6.20. </span><tt>--title</tt> <i>ARG</i></h2> |
| 1098 |
|
| 1099 |
<p> |
| 1100 |
The --title option determines whether or not to render column |
| 1101 |
headers, and if so what text encoding to use for column headers. |
| 1102 |
Whether or not to show column headers, and if so |
| 1103 |
how to encode them. ARG can be "off", "on", |
| 1104 |
"sql", "csv", "html", "tcl", or "json". |
| 1105 |
|
| 1106 |
<a name="opt-tlim"></a> |
| 1107 |
|
| 1108 |
</p><h2 id="_titlelimit_nbsp_n"><span>6.21. </span><tt>--titlelimit</tt> <i>N</i></h2> |
| 1109 |
|
| 1110 |
<p> |
| 1111 |
The --titlelimit option specifies a maximum width for the column |
| 1112 |
titles in tabular and "line" modes. (Other modes are not currently |
| 1113 |
affected by --titlelimit, but that could change in future releases.) |
| 1114 |
If a column title is longer than the specified |
| 1115 |
number of characters, then the title is truncated. |
| 1116 |
Also, newline and tab characters within |
| 1117 |
the title or changed into ordinary spaces. |
| 1118 |
|
| 1119 |
</p><p> |
| 1120 |
The --titlelimit feature is useful to prevent oversize column titles |
| 1121 |
when entering queries that use subqueries or complex expressions as |
| 1122 |
result columns. Without an AS clause, the titles for such columns |
| 1123 |
default to the complete text of the subquery or expression. That |
| 1124 |
can result in unweldy tabular and/or line-mode output. |
| 1125 |
Setting --titlelimit to a positive number (usually between about 10 |
| 1126 |
or 20) can ensure more reasonable looking output. |
| 1127 |
If --titlelimit is zero, then no limits on the length of the title |
| 1128 |
are imposed. |
| 1129 |
|
| 1130 |
</p><p> |
| 1131 |
See also the <a href="climode.html#opt-charlimit">"--charlimit option</a>, the <a href="climode.html#opt-linelimit">"--linelimit" option</a>, |
| 1132 |
and the <a href="climode.html#opt-limits">"--limits" option</a>. |
| 1133 |
|
| 1134 |
|
| 1135 |
<a name="opt-v"></a> |
| 1136 |
|
| 1137 |
</p><h2 id="_verbose"><span>6.22. </span><tt>--verbose</tt></h2> |
| 1138 |
|
| 1139 |
<p> |
| 1140 |
If enabled, then the display of the current mode and settings |
| 1141 |
shows every possible setting, even settings that currently hold |
| 1142 |
their default value. |
| 1143 |
|
| 1144 |
</p><p> |
| 1145 |
The "-v" option is shorthand for "--verbose". |
| 1146 |
|
| 1147 |
<a name="opt-width"></a> |
| 1148 |
|
| 1149 |
</p><h2 id="_widths_list"><span>6.23. </span><tt>--widths</tt> <i>LIST</i></h2> |
| 1150 |
|
| 1151 |
<p> |
| 1152 |
Set the columns widths for tabular modes. The |
| 1153 |
argument is a list of integers, one for each |
| 1154 |
column. A "0" width means use a dynamic width |
| 1155 |
based on the actual width of data. If there are |
| 1156 |
fewer entries in LIST than columns, "0" is used |
| 1157 |
for the unspecified widths. |
| 1158 |
|
| 1159 |
<a name="opt-wwrap"></a> |
| 1160 |
|
| 1161 |
</p><h2 id="_wordwrap_boolean"><span>6.24. </span><tt>--wordwrap</tt> <i>BOOLEAN</i></h2> |
| 1162 |
|
| 1163 |
<p> |
| 1164 |
When the <a href="climode.html#opt-wrap">"--wrap" option</a> requires longer texts to be wrapped, |
| 1165 |
this option determine whether wrapping occurs at a word boundary |
| 1166 |
or in the middle of a word. A value of true means that the |
| 1167 |
CLI attempts to find a suitable word boundary at which to wrap. |
| 1168 |
|
| 1169 |
</p><p> |
| 1170 |
For really long words, it might not possible find a word boundry, |
| 1171 |
in which case the wrapping might still occur mid-word. |
| 1172 |
|
| 1173 |
<a name="opt-wrap"></a> |
| 1174 |
|
| 1175 |
</p><h2 id="_wrap_n"><span>6.25. </span><tt>--wrap</tt> <i>N</i></h2> |
| 1176 |
|
| 1177 |
<p> |
| 1178 |
In tabular modes, this setting prevents any column from growing |
| 1179 |
wider than N characters. If the text to be displayed is wider |
| 1180 |
than N character, then the text wraps. An N value of 0 means |
| 1181 |
there is no limit and table columns can be as wide as necessary |
| 1182 |
to accomodate the text. |
| 1183 |
|
| 1184 |
<a name="opt-ww"></a> |
| 1185 |
|
| 1186 |
</p><h2 id="_ww"><span>6.26. </span><tt>--ww</tt></h2> |
| 1187 |
|
| 1188 |
<p> |
| 1189 |
The "--ww" option, which does not take an argument, is |
| 1190 |
simply a shorthand notation for "--wordwrap on". |
| 1191 |
</p><p align="center"><small><i>This page was last updated on 2026-04-30 13:28:23Z </small></i></p> |
| 1192 |
|