SQLite Forum


47 forum posts by user dfgriggs

19:04 Reply: feature request: vacuum into without indexes (artifact: 68407047f0 user: dfgriggs)


Just for kicks I wrote the script below as a mostly-generalized way to perform a vacuum without indexes. You'd still want to customize it with your source database name and any needed persistent pragma settings.

If you named it "MyCustomVac.sql" you'd invoke it with a new target db name, e.g.

sqlite3 MyCustomVac.sql NewDBname.db

Caution: Not well tested. I'm no expert. Use at own risk.

/* Script to perform quasi-vacuum from one db to another, but omit indexes */

/* Run this script from new, empty target database */

/* !customize with source db name below*/ attach 'myOriginal.db' as cur;

.bail on .echo off

.output 'MyVacTEMP.sql' .print PRAGMA foreign_keys=0; .print PRAGMA ignore_check_constraints=0; .print PRAGMA synchronous=0;

/* !customize by adding any needed persistent pragma settings below E.g. ".print PRAGMA page_size=8192;" */

.print /* Create schema */

.print BEGIN IMMEDIATE; select sql || ';' from cur.sqlite_schema where name not like 'sqlite__%' AND type <> 'index'; .print COMMIT;

.print /* Insert data into our new tables (can simplify if don't want the loading of each table to be separately atomic)*/ select printf( 'BEGIN IMMEDIATE;' || x'0A' || 'INSERT INTO "%s" SELECT * FROM cur."%s";' || x'0A' || 'COMMIT;' , name, name ) from cur.pragma_table_list where name not like 'sqlite__%' and type <> 'view';

.output stdout

.echo on

/* run the vacuum-ish script we just created */ .read 'MyVacTEMP.sql'

.print Done -- Did you remember persistent pragma settings?


15:41 Reply: WAL with Read Only Access (artifact: cb1df98cce user: dfgriggs)

The following might be useful to you:


Note that only one of the three listed conditions must be true.

If you're certain no other process will be opening your database for writing, maybe the immutable flag would be helpful.

18:17 Reply: Possible doc typos for upcoming release (artifact: a108dc5fde user: dfgriggs)

More possible typos.


An argument with SQL type TEXT it is normally converted into a quoted JSON string even if the input text is well-formed JSON.

4.10. The json_type() function

The json_type(X) function returns the "type" of the outermost element of X. The json_type(X,P) function returns the "type" of the element in X that is selected by path P. The "type" returned by json_type() is one of the following an SQL text values: 'null', 'true', 'false', 'integer', 'real', 'text', 'array', or 'object'.

[The words "array" and "object" are capitalized in section 4.8. Maybe intentional?]

4.8. The json_patch() function The json_patch(T,P) SQL function runs the RFC-7396 MergePatch algorithm to apply patch P against input T. The patched copy of T is returned.

MergePatch can add, modify, or delete elements of a JSON Object, and so for JSON Objects, the json_patch() routine is a generalized replacement for json_set() and json_remove(). However, MergePatch treats JSON Array objects as atomic. MergePatch cannot append to an Array nor modify individual elements of an Array. It can only insert, replace, or delete the whole Array as a single unit. Hence, json_patch() is not as useful when dealing with JSON that includes Arrays, especially Arrays with lots of substructure.

17:01 Post: Possible doc typos for upcoming release (artifact: f414a62820 user: dfgriggs)


I've bolded 3 words that may need attention.

The 'auto' modifier can be used to work with date/time values even in cases where it is not know if the julian day number of unix timestamp formats are in use. The 'auto' modifier will automatically select the appropriate format. However, there is a region of ambiguity. Unix timestamps for the first 63 days of 1970 will be interpreted as julian day numbers. The 'auto' modifier is very useful is the dataset is guaranteed to not contain any dates within that region, but should be avoided for applications that might make use of dates in the opening months of 1970.

Thanks as always for the work y'all do. Donald

16:34 Reply: install sqlite on windows10 (artifact: c83f5803a4 user: dfgriggs)

I've always liked sqlite3explorer.

Regarding: "What I dislike is that every cell is treated as text, which disturbs re-sorting by clicking on a column header; it will not sort numerical."

Numerical sorting works for me. I'm using the same version of sqlite3explorer and sqlite3.dll. Does it fail for you using this simple example (in a fresh database)?

create table t(myNum INTEGER, myTxt TEXT, myReal REAL); insert into t values (10, '10', 10.0); insert into t values (2, '2', 2.);

If I display the contents of table "t", then click on the myNum or myReal columns, it sorts numerically. Clicking on the myTxt column sorts lexically.

If this doesn't work for you, you might check to see if you've changed some of the "Options / Format Settings" described on the man page at:


under the "Use of datatypes" section.

21:32 Reply: Serious error emptied my complete database! (artifact: c5ccb68a6d user: dfgriggs)

You probably know this, but you'd want to be sure to run the analyzer, .recover, etc on a copy of your database made just after you found your problem.

The vacuum command will destroy any deleted content.

If you had not changed "pragma secure_delete" from its default of 'off' (0)
and you have not vacuumed or further manipulated data, I believe you would find your data in a hex dump of the database -- though maybe in a form not so trivial to restore. That would imply that you, sqliteExpert, or sqlite itself (unlikely) decided to delete your data.

If you do not find your data in a hex dump, and it passed the "pragma integrity_check" then I suspect you can conclude the data was never present in that file. (If that suspicion is wrong, I hope more knowledgeable folks will correct me.)

18:57 Reply: Serious error emptied my complete database! (artifact: c797f937cb user: dfgriggs)

Hi, Marcel,

Is there even a small chance that, when the error occurred, you were unwittingly using an older version of sqliteexpert (or a current version but loading an older sqlite dll) and that sqliteexpert didn't show the error of:

Error: malformed database schema (tablename) - near "STRICT": syntax error

That would explain the symptom of all your tables appearing empty.

Any chance your database (or wal file) was encumbered by antivirus software at the time?

Any chance your database was moved, but not the associated active WAL file or might the WAL file have been deleted? (if using WAL mode, obviously)

On a copy of the problem database, and using sqlite3.exe utility program version 3.37.0:
1. Did "pragma integrity_check" return with good status?
2. Did the sqlite analyzer utility return anything interesting?
3. Does ".vacuum into" result in a database containing data?
4. As a last resort, there's always the ".recover" command. https://www.sqlite.org/cli.html#recover

Don't know if any of this is helpful,

19:45 Reply: Table constraints accepted without comma (artifact: 262fe3e5e3 user: dfgriggs)

Regarding: >...second "check" works

FWIW, both check constraints work for me.

SQLite version 3.37.0 2021-11-27 14:13:22 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table test ( ...> id int, ...> check (id > 1) ...> check (id < 10)); sqlite> insert into test values (1); Error: stepping, CHECK constraint failed: id > 1 (19) sqlite> insert into test values (11); Error: stepping, CHECK constraint failed: id < 10 (19)

And, as you might expect, both constraints also work if one does include a comma. I guess if this is a bug, it seems a rather benign one.

13:45 Reply: Feature request: sqlite CLI allow `.mode line` and `.mode quote` together (artifact: 5cb10fd859 user: dfgriggs)

Hi, Bokwoon,

You may well be aware of this, but for immediate relief, you can use hex():

   SELECT hex(site_id), domain, subdomain FROM sites;

To format the UUID, you can of course insert hyphens as in


If you're using standard formats, you might have to consider endianness and variants.


12:55 Reply: install sqlite on windows10 (artifact: 2791f5d2a8 user: dfgriggs)

Welcome to sqlite, Anonymous,

Sqlite itself (as perhaps you know) is not a user-facing program such as MS Access. It's a library for programmers to link into their programs.

That being said, a command-line interface program, named sqlite3.exe, is provided which is rather powerful.

One of the most-loved aspects of sqlite is its simplicity. There's no installation process per se. To get started with the command-line interface (CLI) go to the download page: https://www.sqlite.org/download.html and look for the Windows pre-compiled binaries. Download the "bundle of command-line tools" zip file. Extract (at least) the sqlite3.exe file into a directory on your machine -- either one within your PATH or directly into the folder where you wish to run it. Sqlite is so compact that its database logic is compiled directly into the sqlite3.exe file, and you're ready to start using it at the command line.

If you google "sqlite gui" you'll find that lots of folks (not part of this project) have written graphical Windows programs to work with sqlite, and you may decide to use one or more of these instead of the CLI. Some of these programs may expect you to download sqlite in the form of a "DLL" file -- just as you would if you want to write a program yourself. You'll find these DLL files on the download page as well. Programmers can also decide to compile sqlite themselves, either into their own programs or into customized DLL files.

Maybe this helps, Donald

18:28 Reply: Ask for new feature: a pre-decoder mechanism for 'variables' and 'stdev' ? (artifact: 4cf65735f4 user: dfgriggs)

If I'm understanding 'pre-decoder stage' correctly, maybe one of these is relevant:

Sqlite now supports CTEs (WITH clause) which may help you concisely define a function. https://www.sqlite.org/lang_with.html

For basic statistics functions, note the contribution of extension-functions.c at https://sqlite.org/contrib/

Should you be using the CLI (command line interface utility program), it does indeed support named parameters. https://www.sqlite.org/cli.html#sql_parameters

14:26 Reply: SQLite File size not increased with chunk size (artifact: ca66992544 user: dfgriggs)

You might consider:

  • Creating a dummy table.
  • Use a CTE to insert sufficient data to grow your database to a maximum anticipated size
  • Run vacuum
  • Drop the dummy table.
  • Possibly run a defragmentation utility on your disk as appropriate

Your database should now retain its size and low fragmentation.

Of course, even if you should exceed your new file size, nothing tragic happens.

If you are frequently creating new such databases, you can prepare a big empty database file in advance, and just copy it into place for each new one.

13:44 Reply: SQLite irrespective query (artifact: a42e6e2bb7 user: dfgriggs)

Would the following simple WHERE clause do what's needed? (BTW, it will include those of unspecified gender who joined recently.)

SELECT shopper_first_name, shopper_surname,    shopper_email_address,IFNULL(gender, 'Not known' ) AS 'Gender',STRFTIME('%d- 
 %m-%Y', date_joined) AS 'date_joined', STRFTIME('%Y',date('now'))-
 STRFTIME('%Y',date(date_of_birth)) AS 'Age'
FROM shoppers
WHERE gender = 'F'
OR date_joined >= '2020-01-01' 
ORDER BY gender,Age DESC;
12:41 Reply: Test ACID properties SQLITE (artifact: 443f12e93f user: dfgriggs)

Regarding: "....125 copies per person "

One dose leaves you wanting more.

14:28 Reply: Fail to calculate long expression (artifact: 5f5060cfc6 user: dfgriggs)

The differences seem to be in integer arithmetic, is that right?

If one makes all the numbers floating point, then sqlite (and, I suspect others) join the 8.2598e+12 crowd.

select (((((((788.)(8.46))))+8342. * 1.803 -1.))*4186.4 *(15.))(((22. /((7530. /((2.)*(((((25.))-421.))))))*597.)+7283.8 - 9.60+167.))+(8871.); 8259816920615.11

So is there any evidence that any of the listed software is not working as documented?

19:08 Reply: can i custom index blob data? (artifact: 796297680e user: dfgriggs)

Hi Dave,

I'm not confident I understand what you're asking.

If you want to index and sort on data derived from your blobs, then unless space is in very short supply, could you not extract and manipulate this data, then store it in additional columns?

A generated column can even be virtual: https://www.sqlite.org/draft/gencol.html

Alternately, triggers can be used to keep the derivative data in sync with its mother blob.

17:16 Reply: Is there a 64 bit sqlite3.exe available to avoid out of memory error? (artifact: 1ef82d9feb user: dfgriggs)

Compiling sqlite yourself gives you maximum configurability, but Nalgeon kindly hosts 64-bit CLI's at https://github.com/nalgeon/sqlite/releases/

and also packages some extensions together at: https://github.com/nalgeon/sqlean

12:42 Reply: WAL files deleted (artifact: e23e360710 user: dfgriggs)

Doesn't appear to fail using the Windows version of 3.32.3 CLI (nor with current CLI)

k:\tmp>c:sqlite3 test.db
SQLite version 3.32.3 2020-06-18 14:00:33
Enter ".help" for usage hints.
sqlite> PRAGMA journal_mode = WAL;
sqlite> CREATE TABLE t(a);
sqlite> .exit

 Volume in drive K is Windows
 Volume Serial Number is 3EB4-4EE0      
 Directory of k:\tmp
06/07/2021  08:34 AM    <DIR>          .
06/07/2021  08:34 AM    <DIR>          ..
06/07/2021  08:34 AM             8,192 test.db
               1 File(s)          8,192 bytes
               2 Dir(s)  107,722,104,832 bytes free
13:10 Post: fyi, minor broken web link (artifact: 59fa69d198 user: dfgriggs)

I noticed that from the download page: https://www.sqlite.org/download.html

if I take the timeline link at the top: https://www.sqlite.org/src/timeline?bt=version-3.36.0&p=trunk:202105251610&n=all

I get a "not found: version-3.36.0" bad request page.

Not a problem for me, and I'm sure it will resolve once it's released; just FYI in case it's helpful

18:16 Reply: How to import multi-line feeds? (artifact: ee605c5cfe user: dfgriggs)

I'm not aware of a method to set an arbitrary hex value for a field or record separator (though I could swear that strings were acceptable as separators in some ancient version of the CLI -- maybe I just dream in sqlite.)

I don't know if

.mode  ascii

would be of any use to you. 

It's described as:  "Columns/rows delimited by 0x1F and 0x1E"

A hack might be to use the CLI's file functions to read the import file into a single blob field in a temporary 1-row table, perform a REPLACE or two, then write it back out as your new import file.


Someone here may have a better answer.

15:20 Reply: Nothing for several days from the forum (artifact: 8c3169c009 user: dfgriggs)

There have been a number of postings, per: https://sqlite.org/forum/forum

I've gotten a number of emails, so you may want to check your subscription information and also check your email client for overactive spam detection.

19:30 Reply: Administrator SQLite (artifact: 4c24c5139f user: dfgriggs)

They are commands to the windows command line, before you type SQLITE3, or, as Keith showed, by using the ".system" command to sqlite. From the windows command line, you can type:

      help color
to see your options. You can, of course, include "color" along with "sqlite3" commands in a batch file to start things off. An advantage of customizing the terminal is that if/when you exit sqlite3, you still have a reminder of the omnipotent capabilities of your windows prompt.

16:26 Edit reply: Administrator SQLite (artifact: a91a16fc3c user: dfgriggs)

One way you can denote the difference is to color your terminal windows differently from your default, e.g.

     color  1e

With multiple terminal windows open, that allows you to identify them at a glance.

You can of course combine this with something like:

     title   I am the Administrator, drunk on my tremendous power

if that helps.

16:22 Reply: Administrator SQLite (artifact: 330a421b8c user: dfgriggs)

One way you can denote the difference is to color your terminal windows differently from your default, e.g.

 color  1e

With multiple terminal windows open, that allows you to identify them at a glance.

You can of course combine this with something like:

title   I am the Administrator, drunk on my tremendous power

if that helps.

13:15 Reply: Error when trying to add a column (Error checking foreign keys after table modification) (artifact: b9f6df3bf5 user: dfgriggs)

Hello, Zappfinger,

The db browser software isn't written or maintained by the sqlite team, so you'd want to find the maintainers of that software and give them a detailed minimal reproducible example.

16:14 Post: tiny typo (artifact: e86df48e35 user: dfgriggs)

tiny typo at:


"...and subsequent ANALYZE commands will breaks off processing..."

Not a complaint; just fyi.

23:11 Delete reply: Can not drop a table... (artifact: 23119912bd user: dfgriggs)
22:53 Reply: Can not drop a table... (artifact: 048718aed4 user: dfgriggs)
It seems to work ok for me.  Windows 10, using pre-compiled binary for command line utility, version 4.34.0.


SQLite version 3.34.0 2020-12-01 16:14:00
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite>     DROP TABLE IF EXISTS a;
sqlite>     DROP TABLE IF EXISTS b;
sqlite>     CREATE TABLE b(
   ...>         c_ID INTEGER REFERENCES c(ID),
   ...>       );
sqlite>     DROP TABLE a;
sqlite> .tables
22:13 Reply: Suggestion: Use strong hashes on the download page (artifact: f1e3d72efa user: dfgriggs)

Another pre-compiled option for Windows is OpenSSL.

From page: https://wiki.openssl.org/index.php/Binaries

I took the link to:


The page asserts: The OpenSSL DLLs and EXE files are digitally code signed 'Open Source Developer, François PIETTE'

Example of use: openssl dgst -sha3-256 sqlite-tools-win32-x86-3340000.zip

16:03 Reply: SQLite ODBC (artifact: 67d9b2ba59 user: dfgriggs)


"None of these have the feature that I need, namely: CLI enhancement: The .read dot-command now accepts a pipeline in addition to a filename."

I'm writing to say that the sqlite developers take backward compatibility very seriously, and since the feature only accrues to the CLI program, there should be no disadvantages to having "mixed versions" of sqlite on your machine -- in fact, it's typically what one has. (Just hold off on putting any new 3.34 SQL features in your non-CLI code, of course.)

As to obtaining the new CLI -- as others have said, you can compile it yourself immediately, or just wait 8 days and the official release should be available.


16:12 Reply: SQLite: -tabs option is missing (artifact: a494876691 user: dfgriggs)

Not speaking to the merits of your suggestion directly, but wanted to be sure you're aware that the commandline interface program does easily let you pick a separator for input or output.

.separator \t

22:33 Reply: Is `JSON` a valid `create table` type? (artifact: 9fcbfcd171 user: dfgriggs)

I think you'll find your answers in the documentation:



18:02 Reply: Performance Issue: How can I increase a performance? (artifact: f29c1a6fb7 user: dfgriggs)

Regarding: " It's just a SQLite command you execute like CREATE INDEX. "

I feel sure Simon was thinking of "EXPLAIN". For the sqlite analyzer visit the download page:


and download the "bundle of command-line tools" for your machine, or compile from source as needed.

The analyzer is a separate program you'll run from your commandline ("terminal") as follows:

sqlite3_analyzer filespecOfYourDatabase >MyAnalyzeOutput.txt

Then post here the contents of MyAnalyzeOutput.txt, or link to it from somewhere like pastebin.com.

Unrelated question: The field "attributes" is labeled JSON. Is this field's contents always small?

16:07 Reply: Alternative for nesting REPLACE()? (artifact: 1da2219a3d user: dfgriggs)

Bedsides Simon's good suggestion of writing a custom function, you might consider whether the expected rows would fit in cache, and, if so, a series of separate UPDATE's enclosed in a transaction might be sufficiently speedy.

-- or, a CTE can be used with a table of search/replace strings.

-- or would just formatting a big UPDATE not meet your need? I imagine a dozen replacements as something like:

UPDATE myTable SET data = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( data, CHAR(13), '~' ) ,CHAR(10), '~' ) ,'division', 'unity' ) ,'fear', 'serenity' ) ,'greed', 'charity' ) ,'travesties', 'justice' ) ,'suspicion', 'empathy' ) ,'depression', 'joy' ) ,'hate', 'love' ) ,'poverty', 'sharing' ) ,'xenophobia', 'friendship') ,'copyright', 'blessing' ) ;


13:12 Reply: Dump and online backupjexclude tables (artifact: 0260eca709 user: dfgriggs)

Since disk space is a concern with the OP, I wondered if a regimen of:

  1. Weekly full backup with output of .dump
  2. Daily incremental backup of .dump

would result (especially if compressed) in smaller disk requirements even if the fts table was included.

18:48 Reply: How to write this trigger? (artifact: 8a4568c312 user: dfgriggs)

Hello, Anonymous,

  1. I think you want an AFTER trigger. See https://sqlite.org/lang_createtrigger.html#cautions_on_the_use_of_before_triggers
  2. I wonder if you wanted an UPDATE rather than a SELECT
  3. You don't want the trigger to alter the entire table with every insert, so you likely want a WHERE clause.
  4. AFAIK, you must write two triggers if they should fire on both INSERT and UPDATE.

In the example below, I used visible characters instead of CR/LF to avoid confusion. I inserted two rows before we even created the trigger -- just to prove we aren't updating the entire table with each firing.

SQLite version 3.32.3 2020-06-18 14:00:33 sqlite> CREATE TEMP TABLE t (notes TEXT); sqlite> /* Insert two rows before we create the trigger */ sqlite> INSERT INTO t VALUES ('CAFE'), ('BOBCAT');
sqlite> CREATE TRIGGER tgr AFTER insert on t /*changes 'CA' to 'WHA' */ ...> begin ...> UPDATE t SET notes = replace(new.notes, 'C'||'A', 'WHA') ...> WHERE ROWID = new.ROWID; ...> end; sqlite> sqlite> INSERT INTO t VALUES ('CAT'); sqlite> SELECT ROWID, * FROM t; 1|CAFE 2|BOBCAT 3|WHAT Maybe this helps, Donald

14:07 Reply: Querying all compile options used (artifact: 780ceb472f user: dfgriggs)

Maybe just me, but I'm not understanding the question very well.   I know there's, e.g. a compile option of


that would have no effect unless at some point someone has invoked:

PRAGMA journal_mode = WAL; 

Is that what you mean? Maybe you could elaborate on the problem that caused you to pose the question?


21:56 Post: possible documentation typos (artifact: 8276b574c0 user: dfgriggs)
  1. https://www.sqlite.org/compile.html For maximum database safety following a power lose

  2. https://www.sqlite.org/compile.html This option has no affect on the SQLite core (suggest effect)

  3. https://sqlite.org/floatingpoint.htm Title is: Floating Pointer Numbers

  4. Suggestion, not a typo, but if you're editing https://sqlite.org/floatingpoint.htm then around "If you need an exact answer, you should not use binary64..." might want to link down to the decimal.c extension on the same page.

Submitted 100% in the hopes of being helpful, and 0% in the way of complaint.

17:54 Reply: strange strftime returns at julian day zero (artifact: 3cfbf774ca user: dfgriggs)

The beginning of the Julian day epoch is 4714 BC (Nov 24, noon Greenwich, Gregorian Proleptic calendar.)

From the datetime documentation, the "%Y" conversion is not defined for BCE years.


%Y year: 0000-9999

13:31 Reply: how to check update statement really modify a record (artifact: 481faf0b61 user: dfgriggs)


Yes. https://www.sqlite.org/lang_corefunc.html#changes

14:53 Reply: Can of worms - Easiest way to build a UI for Sqlite? (artifact: 5f21603bfd user: dfgriggs)

One possibility that allows forms and reports may be LibreOffice "Base" using the ODBC drivers of Christian Werner.

The instructions at the page below look promising:


19:03 Reply: .mode html fails on html content (artifact: 3392f0a349 user: dfgriggs)

This may be obvious and unhelpful, but for my need I just use

.mode list

And create my own HTML markup with:

UPDATE myTable set ln = '<a href="' || ln || '">' || ln || '</a><br />' WHERE ln like 'http%';

(Where "ln" is a text field with an entire line of output.)

13:38 Reply: output in column mode is truncated (artifact: 68b799c55b user: dfgriggs)

And in case a spreadsheet isn't suitable for your needs, you're probably aware of the .width command in the command line shell.

See section 5 of: https://sqlite.org/cli.html

11:15 Reply: Return boolean if item EXISTS in database (artifact: 127f27b773 user: dfgriggs)

You may already know that SQLite does not have a separate boolean type, and that typing, in general, differs from strongly-typed databases.


22:52 Reply: Bug in ALTER TABLE ADD COLUMN (artifact: ffa5244727 user: dfgriggs)

But how would it matter in actual practice? The ALTER TABLE ADD COLUMN command is intended for situations in which the need for the new column was not foreseen during the initial design.

Of what real use would adding code to allow this corner case be of benefit -- and justify the added verification test and small expenditure of RAM?

19:31 Reply: Searching www.sqlite.org options (artifact: 67f1bc2b45 user: dfgriggs)

Hi, Jose,

I can't speak to the search question, but if you go to the command line page, https://sqlite.org/cli.html

and do a FIND for .schema, you may find the information you need.

16:35 Reply: Renaming a database (artifact: 3e62dce4e8 user: dfgriggs)

Re: "..I have a requirement to rename a database, and various disparate clients which might be using it."

Hi, Toby,

Are you at liberty to elaborate on this requirement? This list includes some really helpful folks who might be able to offer some alternate solutions.