Some SQLite things I recently learned a while ago
Posted by jpluimers on 2025/04/30
More on the reason why I learned a few SQLite things soon, but for my link and documentation archive, below is what I learned.
Most commands use the database file C:\temp\History which has no extension as that is how I got the file in the first place (spoiler: it’s a Chrome browser History from one of my user profiles).
Let’s get started:
Getting help
Both the commands sqlite3 --help and sqlite3 -help function to get the help on the command line. Both show the same help (the first line depends on where sqlite3 is installed):
Usage: C:\ProgramData\chocolatey\lib\SQLite\tools\sqlite-tools-win32-x86-3390400\sqlite3.exe [OPTIONS] FILENAME [SQL] FILENAME is the name of an SQLite database. A new database is created if the file does not previously exist. OPTIONS include: -A ARGS... run ".archive ARGS" and exit -append append the database to the end of the file -ascii set output mode to 'ascii' -bail stop after hitting an error -batch force batch I/O -box set output mode to 'box' -column set output mode to 'column' -cmd COMMAND run "COMMAND" before reading stdin -csv set output mode to 'csv' -deserialize open the database using sqlite3_deserialize() -echo print inputs before execution -init FILENAME read/process named file -[no]header turn headers on or off -help show this message -html set output mode to HTML -interactive force interactive I/O -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -lookaside SIZE N use N entries of SZ bytes for lookaside memory -markdown set output mode to 'markdown' -maxsize N maximum size for a --deserialize database -memtrace trace all memory allocations and deallocations -mmap N default mmap size set to N -newline SEP set output row separator. Default: '\n' -nofollow refuse to open symbolic links to database files -nonce STRING set the safe-mode escape nonce -nullvalue TEXT set text string for NULL values. Default '' -pagecache SIZE N use N slots of SZ bytes each for page cache memory -quote set output mode to 'quote' -readonly open the database read-only -safe enable safe-mode -separator SEP set output column separator. Default: '|' -stats print memory stats before each finalize -table set output mode to 'table' -tabs set output mode to 'tabs' -version show SQLite version -vfs NAME use NAME as the default VFS -zip open the file as a ZIP Archive
Various output modes
From the above help output, it is interesting to see there are various output modes. These are only very partially documented at [Wayback/Archive] Command Line Shell For SQLite, so lets try to amend some parts here.
Since I was doing this SQLite work on Windows, I wrote a small command-line that dumps the various permutations of the output modes together with the -header and -noheader option (especially as the defaults are not mentioned).
For that I needed to filter the help output on lines containing “set output mode to“.
That was a tad more difficult than expected as the help output is not sent to stdout, but to stderr, so a redirect is needed and a findstr /C: (for a literal string search):
C:\temp>sqlite3 -help 2>&1 | findstr /C:"set output mode to" -ascii set output mode to 'ascii' -box set output mode to 'box' -column set output mode to 'column' -csv set output mode to 'csv' -html set output mode to HTML -json set output mode to 'json' -line set output mode to 'line' -list set output mode to 'list' -markdown set output mode to 'markdown' -quote set output mode to 'quote' -table set output mode to 'table' -tabs set output mode to 'tabs'
First of all, I was quote surprised with the rich set of output mode options. That is very well done for such a small footprint.
Now let’s permutate.
To keep the example understandable, lets start with echoing the permutations without double quotes (using the ~ trick, see windows – What is the proper way to test if a parameter is empty in a batch file? – Stack Overflow):
@for %m in ("", "-ascii", "-box", "-column", "-csv", "-html", "-json", "-line", "-list", "-markdown", "-quote", "-table", "-tabs") do @for %h in ("", "-header", "-noheader") do echo %~h %~m
This will echo output like this:
C:\temp>C:\temp>echo ECHO is on. C:\temp>echo -header -header C:\temp>echo -noheader -noheader C:\temp>echo -ascii -ascii C:\temp>echo -header -ascii -header -ascii C:\temp>echo -noheader -ascii -noheader -ascii ... C:\temp>echo -tabs -tabs C:\temp>echo -header -tabs -header -tabs C:\temp>echo -noheader -tabs -noheader -tabs
Using sqlite3 you will get this full output showing all permutations:
C:\temp>@for %m in ("", "-ascii", "-box", "-column", "-csv", "-html", "-json", "-line", "-list", "-markdown", "-quote", "-table", "-tabs") do @for %h in ("", "-header", "-noheader") do sqlite3 %~h %~m c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" C:\temp>sqlite3 c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373|10373 C:\temp>sqlite3 -header c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all|unique 10373|10373 C:\temp>sqlite3 -noheader c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373|10373 C:\temp>sqlite3 -ascii c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373▼10373▲ C:\temp>sqlite3 -header -ascii c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all▼unique▲10373▼10373▲ C:\temp>sqlite3 -noheader -ascii c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373▼10373▲ C:\temp>sqlite3 -box c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" ┌───────┬────────┐ │ all │ unique │ ├───────┼────────┤ │ 10373 │ 10373 │ └───────┴────────┘ C:\temp>sqlite3 -header -box c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" ┌───────┬────────┐ │ all │ unique │ ├───────┼────────┤ │ 10373 │ 10373 │ └───────┴────────┘ C:\temp>sqlite3 -noheader -box c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" ┌───────┬────────┐ │ all │ unique │ ├───────┼────────┤ │ 10373 │ 10373 │ └───────┴────────┘ C:\temp>sqlite3 -column c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373 10373 C:\temp>sqlite3 -header -column c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all unique ----- ------ 10373 10373 C:\temp>sqlite3 -noheader -column c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373 10373 C:\temp>sqlite3 -csv c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373,10373 C:\temp>sqlite3 -header -csv c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all,unique 10373,10373 C:\temp>sqlite3 -noheader -csv c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373,10373 C:\temp>sqlite3 -html c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" <TR><TD>10373</TD> <TD>10373</TD> </TR> C:\temp>sqlite3 -header -html c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" <TR><TH>all</TH> <TH>unique</TH> </TR> <TR><TD>10373</TD> <TD>10373</TD> </TR> C:\temp>sqlite3 -noheader -html c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" <TR><TD>10373</TD> <TD>10373</TD> </TR> C:\temp>sqlite3 -json c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" [{"all":10373,"unique":10373}] C:\temp>sqlite3 -header -json c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" [{"all":10373,"unique":10373}] C:\temp>sqlite3 -noheader -json c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" [{"all":10373,"unique":10373}] C:\temp>sqlite3 -line c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all = 10373 unique = 10373 C:\temp>sqlite3 -header -line c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all = 10373 unique = 10373 C:\temp>sqlite3 -noheader -line c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all = 10373 unique = 10373 C:\temp>sqlite3 -list c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373|10373 C:\temp>sqlite3 -header -list c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all|unique 10373|10373 C:\temp>sqlite3 -noheader -list c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373|10373 C:\temp>sqlite3 -markdown c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" | all | unique | |-------|--------| | 10373 | 10373 | C:\temp>sqlite3 -header -markdown c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" | all | unique | |-------|--------| | 10373 | 10373 | C:\temp>sqlite3 -noheader -markdown c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" | all | unique | |-------|--------| | 10373 | 10373 | C:\temp>sqlite3 -quote c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373,10373 C:\temp>sqlite3 -header -quote c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 'all','unique' 10373,10373 C:\temp>sqlite3 -noheader -quote c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373,10373 C:\temp>sqlite3 -table c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" +-------+--------+ | all | unique | +-------+--------+ | 10373 | 10373 | +-------+--------+ C:\temp>sqlite3 -header -table c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" +-------+--------+ | all | unique | +-------+--------+ | 10373 | 10373 | +-------+--------+ C:\temp>sqlite3 -noheader -table c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" +-------+--------+ | all | unique | +-------+--------+ | 10373 | 10373 | +-------+--------+ C:\temp>sqlite3 -tabs c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373 10373 C:\temp>sqlite3 -header -tabs c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" all unique 10373 10373 C:\temp>sqlite3 -noheader -tabs c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373 10373
The above is indeed a lot of output, but shows that some of the permutations are not implemented and a few other peculiarities. I’ll shorten that to these remarks
- the default options are
-headerand-list, so if neither an option for header nor a option for output mode are given thensqlite3will default to-noheader -list - all output mode options put a newline at the end of the last output line, except for output mode
-ascii - output mode
-asciiuses the control codes Unit Separator (C1or hex0x1F) for separating fields and Record Separator (C0or hex0x1E) for separating records. - output mode
-csvuses the comma (,or hex0x2C) for separating fields and Line Feed (LFor hex0x0A) for separating records. - output mode
-tabsuses the control codes Horizontal Tabulation (HTor hex0x09) for separating fields and Record Separator (C0or hex0x1E) for separating records. -columnmeans fixed width text where fields are both aligned and separated by space characters (0x20) and uses CRLF (or hex0x0A 0x0D) for separating records.-linehas all fields on separate lines, each field prepending by the right aligned field name and an equals sign surrounded by spaces; each line ends with CRLF (or hex0x0A 0x0D). There is no record separator.-box,-markdownand-tablecenters each field in the allotted room using space characters (0x20)-noheaderhas no effect for these output mode options:-box-json-line-markdown-table
SQLite error messages sometimes are odd
Given each new SQL database engine has its own gotchas, I of course bumped in a few. For example when trying to get an idea on some counts, bumped into this:
C:\temp>sqlite3 c:\temp\History "select count(*) as all, count(distinct url) as unique from urls" Error: in prepare, near "all": syntax error select count(*) as all, count(distinct url) as unique from urls ^--- error here
followed by:
C:\temp>sqlite3 c:\temp\History "select count(*) as any, count(distinct url) as unique from urls" Error: in prepare, near "unique": syntax error select count(*) as any, count(distinct url) as unique from urls error here ---^
This error message could have been phrased in a much clearer way and let me to search about how to check for keywords. I found [Wayback/Archive] SQL Keyword Checking:
The parser used by SQLite is forgiving. It is often possible to use a keyword as an identifier as long as such use does not result in a parsing ambiguity. For example, the statement “
CREATE TABLE BEGIN(REPLACE,PRAGMA,END);” is accepted by SQLite, and creates a new table named “BEGIN” with three columns named “REPLACE“, “PRAGMA“, and “END“.…
- Put all identifier names inside double-quotes. This is the official SQL way to escape identifier names.
Despite not causing ambiguity in the select clause it looks like all and unique are reserved words. My suspicion is that some keywords are more equal than others and will be regarded as keyword under all circumstances.
With quoting, it is also important to consider the kind of command-line is on. It means the “Put all identifier names inside double-quotes.” is not the best advice. In fact, it is not the first advice from [Wayback/Archive] SQLite Keywords:
If you want to use a keyword as a name, you need to quote it. There are four ways of quoting keywords in SQLite:
‘keyword’ A keyword in single quotes is a string literal. “keyword” A keyword in double-quotes is an identifier. [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility. `keyword` A keyword enclosed in grave accents (ASCII code 96) is an identifier. This is not standard SQL. This quoting mechanism is used by MySQL and is included in SQLite for compatibility.
For example when running on cmd.exe, this example using single-quotes (') for the full query and double-quotes (") for the aliases fails too:
C:\temp>sqlite3 c:\temp\History 'select count(*) as "all", count(distinct url) as "unique" from urls' Error: in prepare, unrecognized token: "'select" 'select ^--- error here
but works under cmd.exe:
C:\temp>sqlite3 c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373|10373
Using PowerShell the first example fails, but with a different error message:
PS C:\temp> sqlite3 c:\temp\History 'select count(*) as "all", count(distinct url) as "unique" from urls' Error: in prepare, near "all": syntax error select count(*) as all, count(distinct url) as unique from urls ^--- error here
Like cmd.exe, this works fine under PowerShell as well:
PS C:\temp> sqlite3 c:\temp\History "select count(*) as 'all', count(distinct url) as 'unique' from urls" 10373|10373
Since backticks (`) on nx are used for executing commands the best options for quoting potential keywords are these:
“keyword” A keyword in double-quotes is an identifier. [keyword] A keyword enclosed in square brackets is an identifier. This is not standard SQL. This quoting mechanism is used by MS Access and SQL Server and is included in SQLite for compatibility.
Personally, I prefer the first. People coming from a Microsoft database background probably the second.
SQLite keywords: always quote any identifiers
Based on my assumption above, I looked what keywords SQLite has. Plenty, likely growing over time and at the time of writing consist of many that could have been identifiers instead of global keywords:
ABORTACTIONADDAFTERALLALTERALWAYSANALYZEANDASASCATTACHAUTOINCREMENTBEFOREBEGINBETWEENBYCASCADECASECASTCHECKCOLLATECOLUMNCOMMITCONFLICTCONSTRAINTCREATECROSSCURRENTCURRENT_DATECURRENT_TIMECURRENT_TIMESTAMPDATABASEDEFAULTDEFERRABLEDEFERREDDELETEDESCDETACHDISTINCTDODROPEACHELSEENDESCAPEEXCEPTEXCLUDEEXCLUSIVEEXISTSEXPLAINFAILFILTERFIRSTFOLLOWINGFORFOREIGNFROMFULLGENERATEDGLOBGROUPGROUPSHAVINGIFIGNOREIMMEDIATEININDEXINDEXEDINITIALLYINNERINSERTINSTEADINTERSECTINTOISISNULLJOINKEYLASTLEFTLIKELIMITMATCHMATERIALIZEDNATURALNONOTNOTHINGNOTNULLNULLNULLSOFOFFSETONORORDEROTHERSOUTEROVERPARTITIONPLANPRAGMAPRECEDINGPRIMARYQUERYRAISERANGERECURSIVEREFERENCESREGEXPREINDEXRELEASERENAMEREPLACERESTRICTRETURNINGRIGHTROLLBACKROWROWSSAVEPOINTSELECTSETTABLETEMPTEMPORARYTHENTIESTOTRANSACTIONTRIGGERUNBOUNDEDUNIONUNIQUEUPDATEUSINGVACUUMVALUESVIEWVIRTUALWHENWHEREWINDOWWITHWITHOUT
Basically quote any identifiers you use to stay future-proof, as per [Wayback/Archive] SQLite Keywords:
to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to.
Dumping a SQLite database structure
Dumping a schema was harder than I expected as this fails:
C:\temp>sqlite3 C:\temp\History schema Error: in prepare, near "schema": syntax error schema ^--- error here
Via [Wayback/Archive] sqlite dump db structure – Google Search I found [Wayback/Archive] macos – sqlite3: dump schema into .sql file from command line – Stack Overflow (thanks [Wayback/Archive] Joe Flip and [Wayback/Archive] CL.)
C:\temp> sqlite3 C:\temp\History .schema
The database filename needs a full path. You can redirect the output from default stdout to file (or include any stderr messages in it using the 2>&1 mechanism shown above to filter the output mode).
The .schema is part of the special “dot commands”, for which you can get help using .help. You then get a list similar to the one on [Wayback/Archive] Command Line Shell For SQLite: Special commands to sqlite3 (dot-commands):
Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But input lines that begin with a dot (“.”) are intercepted and interpreted by the sqlite3 program itself. These “dot commands” are typically used to change the output format of queries, or to execute certain prepackaged query statements. There were originally just a few dot commands, but over the years many new features have accumulated so that today there are over 60.
For a listing of the available dot commands, you can enter “.help” with no arguments. Or enter “.help TOPIC” for detailed information about TOPIC. The list of available dot-commands follows:
sqlite> .help .archive ... Manage SQL archives .auth ON|OFF Show authorizer callbacks .backup ?DB? FILE Backup DB (default "main") to FILE .bail on|off Stop after hitting an error. Default OFF .binary on|off Turn binary output on or off. Default OFF .cd DIRECTORY Change the working directory to DIRECTORY .changes on|off Show number of rows changed by SQL .check GLOB Fail if output since .testcase does not match .clone NEWDB Clone data into NEWDB from the existing database .connection [close] [#] Open or close an auxiliary database connection .databases List names and files of attached databases .dbconfig ?op? ?val? List or change sqlite3_db_config() options .dbinfo ?DB? Show status information about the database .dump ?OBJECTS? Render database content as SQL .echo on|off Turn command echo on or off .eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN .excel Display the output of next command in spreadsheet .exit ?CODE? Exit this program with return-code CODE .expert EXPERIMENTAL. Suggest indexes for queries .explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto .filectrl CMD ... Run various sqlite3_file_control() operations .fullschema ?--indent? Show schema and the content of sqlite_stat tables .headers on|off Turn display of headers on or off .help ?-all? ?PATTERN? Show help text for PATTERN .import FILE TABLE Import data from FILE into TABLE .imposter INDEX TABLE Create imposter table TABLE on index INDEX .indexes ?TABLE? Show names of indexes .limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT .lint OPTIONS Report potential schema issues. .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?TABLE? Set output mode .nonce STRING Disable safe mode for one command if the nonce matches .nullvalue STRING Use STRING in place of NULL values .once ?OPTIONS? ?FILE? Output for the next SQL command only to FILE .open ?OPTIONS? ?FILE? Close existing database and reopen FILE .output ?FILE? Send output to FILE or stdout if FILE is omitted .parameter CMD ... Manage SQL parameter bindings .print STRING... Print literal STRING .progress N Invoke progress handler after every N opcodes .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILE Read input from FILE .recover Recover as much data as possible from corrupt db. .restore ?DB? FILE Restore content of DB (default "main") from FILE .save FILE Write in-memory database into FILE .scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off .schema ?PATTERN? Show the CREATE statements matching PATTERN .selftest ?OPTIONS? Run tests defined in the SELFTEST table .separator COL ?ROW? Change the column and row separators .session ?NAME? CMD ... Create or control sessions .sha3sum ... Compute a SHA3 hash of database content .shell CMD ARGS... Run CMD ARGS... in a system shell .show Show the current values for various settings .stats ?ARG? Show stats or turn stats on or off .system CMD ARGS... Run CMD ARGS... in a system shell .tables ?TABLE? List names of tables matching LIKE pattern TABLE .testcase NAME Begin redirecting output to 'testcase-out.txt' .testctrl CMD ... Run various sqlite3_test_control() operations .timeout MS Try opening locked tables for MS milliseconds .timer on|off Turn SQL timer on or off .trace ?OPTIONS? Output each SQL statement as it is run .vfsinfo ?AUX? Information about the top-level VFS .vfslist List all available VFSes .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set minimum column widths for columnar output sqlite>
I used PS C:\temp> sqlite3 db .help (note db here is just a dummy database name) which showed these differences with the above table:
... .mode MODE ?OPTIONS? Set output mode .nonce STRING Suspend safe mode for one command if nonce matches ... .read FILE Read input from FILE or command output ... .save ?OPTIONS? FILE Write database to FILE (an alias for .backup ...) ...
SELECT FROM DUAL (actually SELECT from nothing)
Unlike many other databases, SQLite does not have a DUAL table or equivalent, but like some other databases it does support the FROM DUAL syntax. The FROM DUAL is just ignored.
This means you can do this (run this at [Wayback/Archive] SQLite 3.39 | dbfiddle) without having a FROM clause:
select sqlite_version();
Note I used dbfiddle here instead of SQL Fiddle as for SQLite it is much faster.
Aliases in WHERE clauses
I needed to use aliases, and was positively surprised that SQLite allows them also to be used inside WHERE clauses (that’s above the SQL standard and not all DMBS allow this).
See [Wayback/Archive] sqlite – using a column alias in sqlite3 where clause – Stack Overflow (thanks [Wayback/Archive] Alex and [Wayback/Archive] Simon Dorociak):
Q
I know that in oracle/mysql/sqlserverselectstatement you are not able to reference a column alias in thewhereclause because it is executed before theselect, yet it works in sqlite3:sqlite> create table t (c1 text, c2 text); sqlite> insert into t values ("a1", "a2"); sqlite> insert into t values ("b1", "b2"); sqlite> select c1, c2 from t; a1|a2 b1|b2 sqlite> select c1, c2 as alias_c2 from t where alias_c2='b2'; b1|b2Why is this possible in sqlite?A
So, from docsStandard SQL doesn’t allow you to refer to a column alias in a WHERE clause. This restriction is imposed because when the WHERE code is executed, the column value may not yet be determined.but currentSQLiteallows itSQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause.
The docs actually are from MySQL 5.1 (the phrasing until December 2009, from December 2010 on it changes):
- [Wayback/Archive] MySQL :: MySQL 5.1 Reference Manual :: B.5.5.4 Problems with Column Aliases
Standard SQL doesn’t allow you to refer to a column alias in aWHEREclause. This restriction is imposed because when theWHEREcode is executed, the column value may not yet be determined. For example, the following query is illegal:SELECT id, COUNT(*) AS cnt FROM
tbl_nameWHERE cnt > 0 GROUP BY id;TheWHEREstatement is executed to determine which rows should be included in theGROUP BYpart, whereasHAVINGis used to decide which rows from the result set should be used.After that “doesn’t allow” got replaced by “disallows” and still is as seen in the current form below. - [Wayback/Archive] MySQL :: MySQL 8.0 Reference Manual :: B.3.4.4 Problems with Column Aliases
Standard SQL disallows references to column aliases in a
WHEREclause. This restriction is imposed because when theWHEREclause is evaluated, the column value may not yet have been determined.
The “but SQLite allowsit” is quoted from a post in the the SQLite mailing list that almost celebrates its 5th lustrum [Wayback/Archive] sqlite-users Digest, Vol 46, Issue 29 and [Wayback/Archive] Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29:
Pete <p...@mollysrevenge.com> wrote: > Thanks. I guess I'd like to confirm just where column aliases can > be referenced. I think they cannot be referenced within the list of column > names in which they are defined, and they can be referenced in any other > clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is > that correct?Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause. -- Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
The mailing list died some 5 years ago, see [Wayback/Archive] sqlite-users Info Page and [Wayback/Archive] SQLite Support Options.
DATE/TIME types and formatting timestamps
Via [Wayback/Archive] sqlite datetime type – Google Search:
- [Wayback/Archive] Datatypes In SQLite: Date and Time Datatype: there are none, so you will always have to go through functions:
SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
- TEXT as ISO8601 strings (“YYYY-MM-DD HH:MM:SS.SSS”).
- REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
- INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
- [Wayback/Archive] Date And Time Functions
SQLite supports six date and time functions as follows:- date(time-value, modifier, modifier, …)
- time(time-value, modifier, modifier, …)
- datetime(time-value, modifier, modifier, …)
- julianday(time-value, modifier, modifier, …)
- unixepoch(time-value, modifier, modifier, …)
- strftime(format, time-value, modifier, modifier, …)
All six date and time functions take an optional time value as an argument, followed by zero or more modifiers. The strftime() function also takes a format string as its first argument.Date and time values can be stored as- text in a subset of the ISO-8601 format,
- numbers representing the Julian day, or
- numbers representing the number of seconds since (or before) 1970-01-01 00:00:00 UTC (the unix timestamp).
…
--jeroen
The Wiert Corner - irregular stream of stuff 
Leave a comment