SQLite Forum


50 most recent forum posts by user knu

17:16 Reply: using a | as separator does not parse into columns during import, please help out (artifact: 4d8fcb1fe3 user: knu)

It works for me with this script:

sqlite3 test.sqlite -version
sqlite3 test.sqlite <<EOSQL
.echo on
.mode csv
.separator |
.import 'in8.bsv' data1
.mode columns
SELECT CVEID,severity,datetime,vendor FROM data1;


+ sqlite3 test.sqlite -version
3.37.0 2021-11-19 19:11:58 d814ba6effc7c5b021ff625b57371627f3ee326687c508da99f78bc164386b7e
+ sqlite3 test.sqlite
.mode csv
.separator |
.import 'in8.bsv' data1
.mode columns
SELECT CVEID,severity,datetime,vendor FROM data1;
CVEID           severity  datetime                 vendor
--------------  --------  -----------------------  -------
CVE-2019-10218  moderate  2019-10-29T00:00:00Z     JREDHAT
CVE-2019-10222  Medium    2019-08-28 14:00:00 UTC  TRUSTY

I think you forgot to supply a table name in the .import command.

Kind regards,
Kees Nuyt
12:01 Reply: Is this inherent casting by design? (artifact: 1ce2db8a30 user: knu)

Use the ltrim() function to remove leading characters.

Kees Nuyt
11:58 Reply: sqldiff for Windows 10 x 64 bits (artifact: 1b2b1a1424 user: knu)

Hi anonymous,

32-bit sqldiff.exe runs just fine on my 64-bit Windows 10, that is Microsoft Windows [Version 10.0.19043.1288] .

Can you be a bit more specific on your diagnosis "does not start" and "is not compatible"?

If you expect a graphical user interface, there isn't one. It is a command line utility.

Open a CMD.EXE window and run sqldiff.exe --help for more information.

Kind Regards,
Kees Nuyt
14:32 Reply: Compiling sqlite system on Ubuntu (artifact: 9da545904f user: knu)

Where do I place them?

sudo make install will place anything that needs to be installed into the proper directories, by default in the /usr/local tree, or anywhere else where ./configure --prefix=.... points to. Run `./configure --help for more info.

... python for some scripting and I want to make sure that the latest built libraries are the one it uses.

The sqlite make process will not build or install python modules.

If you want to build that yourself, you are probably best off with Roger Binns' APSW (Another Python SQLite Wrapper)

Kind Regards,
Kees Nuyt
16:49 Reply: bug? Unexpected CLI shell comment handling (artifact: f07616e218 user: knu)

Hi Larry,

Thanks for your quick reply and thorough analysis.

I can confirm that the current trunk behaves as expected.

Next time I report a possible bug, I will bisect it beforehand, so you can pinpoint it more easily.

Thank you for your efforts!

Kind regards,
Kees Nuyt
12:29 Post: bug? Unexpected CLI shell comment handling (artifact: 600247a354 user: knu)

In recent versions of SQLite, I noticed single line comments in the sqlite3 command line tool behave weird.


knu@f7p3:~ $ sqlite3 test.db # a database with one table, t1
SQLite version 3.37.0 2021-10-01 21:01:07
Enter ".help" for usage hints.
sqlite> .tables
sqlite> -- .tables
   ...> ;
sqlite> SELECT name FROM sqlite_schema;
sqlite> -- SELECT name FROM sqlite_schema;
   ...> ;

Observed behavior: Line starting with -- requires continuation, and a ';' to complete.

Expected behavior: No continuation prompt.

Same behavior when processing redirected scripts with single line comments, as in:

sqlite3 test.db <script.sql

I didn't bisect it, but it must be a recent change, because I recompile from trunk almost weekly and just over a week ago one of my scripts suddenly failed by this peculiarity.

Kind regards, Kees Nuyt

14:30 Reply: about "strict" mode (artifact: 96a1b562fb user: knu)

A table can have multiple uniquely identifying columns, we can call those candidate keys. One of the candidate keys is chosen as the primary key.

It is just a matter of taste which of the candidate keys is chosen as the primary key, typically it is the key that foreign keys refer to.

The other candidate keys should have a UNIQUE constraint. Without that constraint, you can't call it a key.

If you add an extra INTEGER candidate key for performance reasons, so it can serve as the key that is referenced by foreign keys, it should become the primary key of the table.

This is called a surrogate key, because it does not represent the attribute of an entity in the real world.

In SQLite I would implement that as follows:


, ...


, ...

12:03 Reply: Join Multiple Table (artifact: 60be1dfbb9 user: knu)
SELECT col1,col2,..
     FROM Table_1 AS T1
LEFT JOIN Table_2 AS T2 ON T2.col2 = T1.col1
LEFT JOIN Table_3 AS T3 ON T3.col4 = T2.col3
LEFT JOIN Table_4 AS T4 ON T4.col5 = T1.col6
 WHERE ....
 ORDER BY ....
15:06 Reply: Feature request: Stored Procedures (artifact: 0293f157f9 user: knu)

You would have separate INSTEAD OF triggers on


Any trigger can do INSERT, UPDATE or DELETE on any table in the statements between BEGIN and END.

Triggers can do SELECTs where needed to make decisions of what values to use for INSERT, UPDATE or DELETE.

You can even use multiple INSERT/UPDATE/DELETE triggers with different WHEN clauses to discriminate between cases.

I would implement a "SELECT in a trigger" as a view. No need for a trigger.

13:49 Reply: Anton Dyachenko (artifact: 00a4eeb495 user: knu)

Yeah, it is complicated, I don't know where to begin. And I can't address all of your concerns.

but what I am really looking for is something like a vacuum (but much more performant)

If VACUUM is a working, but too slow solution, this could be used instead:

migration steps
PRAGMA schema_version = 1+(SELECT schema_version FROM pragma_schema_version);

Some warnings apply

I have no idea how safe this is when more than one connection updates the same database schema concurrently, except "they shouldn't do that".

but we have plans to start using WAL where this is possible, and it is not clear for me what is the proper handling of this case. Should already running statements be interrupted or not? Interruption is "correct" but not always reasonable due to change in the schema can be quite often compatible with running statements so there is no point interrupting them.

As I understand it, any readers (SELECT) that are already sqlite_stepping() before another connection to the database changes the schema will proceed to see the schema and table definitions that were valid when the SELECT was sqlite_prepared() and the first sqlite_step() executed.

In other words, (cached) prepared statements can only be invalidated and recompiled when the first step is made and the engine sees the schema has just been changed. Once the first step is made, it will proceed do execute all further steps without recompiling again, because they run within a transaction (implicit or explicit).

11:47 Reply: Anton Dyachenko (artifact: d4856d3e08 user: knu)

If the migration involves PRAGMA writable_schema=ON, you'd better discard the connection in which you did that and open a new one.

Richard Hipp has suggested several ways to refresh the cached schema:

From: https://sqlite.org/forum/forumpost/29bad1c5c1

There are many ways to force a check of the schema to see if it needs to be reread. Here is one: PRAGMA user_version;

From: https://sqlite.org/forum/forumpost/8067ae2895

OK, so it looks like you have to actually do something that needs to use the schema before the schema is checked. Something like this will suffice: SELECT 1 FROM sqlite_master LIMIT 1;

15:31 Reply: SQLite DB corrupt while inserting the data in iOS application. (artifact: f6e1f45385 user: knu)

Your question might be more appropriate in some iOS app developer forum, but anyway ...

we have provided the data Downloading feature from the API.

Which API?

Do you close the database when your app loses focus?

Wouldn't it be easier to just remove the database from the filesystem and create a new instance for your data Downloading feature?

13:39 Reply: Complex view breaking SQLite on Android < v11 (artifact: dc3f32e5b7 user: knu)

Thanks for letting us know you were able to solve it.

I assume you mean the following pages:

16:23 Reply: Complex view breaking SQLite on Android < v11 (artifact: 6204001deb user: knu)

Your view uses a window function `OVER (PARTITION BY', which may not be supported by the SQLite version on Android < v11, or by aSQLiteManager on those versions.

Use SELECT sqlite_version() to find out what version your target Android version has installed, and consult the release history to find out if that version is supposed to support window functions.

17:52 Reply: Update table from SQLite3 queries results. (artifact: 64464fe101 user: knu)

It will be much easier to INSERT it in a new table than UPDATE one row in Table1 and DELETE the other rows. Something like:

, meaning TEXT

INSERT INTO Table2 (word,meaning) 
 SELECT word, group_concat(meaning, ' / ') as meaning
 FROM Table1 GROUP BY word;

Now verify the contents of Table2. If all is well:

11:27 Reply: Find the relation in a sql query (artifact: 71447d2b7a user: knu)

The SQLite documentation contains an example of traversing a tree structure , which is much like your requirement.

Not exactly the same, but close enough to inspire you.

14:54 Reply: Feature request: .import based on column (artifact: 563d2b3925 user: knu)

I took the liberty to improve a bit (in my eyes, at least) your code.

That was the idea,"to get you started".

18:02 Reply: Feature request: .import based on column (artifact: 02d1d8d0d6 user: knu)

To get you started:

# fixed width import
cat >in.tmp <<EOF
id Name          Address             Phone
1  John Doe      My street           555-1212
2  Marc O'Donnel His street          555-1313

gawk -v pos="1,4,18,38" -v table=T1 \
	if (table) TABLE    = table
	else TABLE = "mytable"
	q = "\x27"
	for (i=1;i<NROFCOL;i++) wd = wd sprintf("%d ",tmp[i+1]-tmp[i])
	wd = wd "999"
	fmtins = "INSERT INTO %s (%s) VALUES (%s);\n"
function trim(s){
	return s
function quote(s){
	sub(q,q q,s)
	return s
function isnum(s){
	return (s=="")
	for (i=1;i<=NROFCOL;i++){
		COLLIST = COLLIST ((COLLIST)?",":"") trim($i)
	printf "-- fieldwidths %s\n",FIELDWIDTHS
	printf "-- columnlist %s\n",COLLIST
	printf "CREATE TABLE %s (%s);\n",TABLE,COLLIST
	for (i=1;i<=NROFCOL;i++){
		val = trim($i)
		if (isnum(val)){
			VALLIST = VALLIST ((VALLIST)?",":"") val
		} else {
			VALLIST = VALLIST ((VALLIST)?",":"") q quote(val) q
}' in.tmp     # | sqlite3 import.sqlite

Sorry, I'm more proficient in awk than TCL, Python or Perl

14:01 Reply: Need to connect with CDAS server (artifact: 3bc65a3f78 user: knu)

You will have to write some code in the language of your choice.

That code would:

  • access the CDAS server using the APIs described on that page,
  • access your SQLite database using the SQLite API in the language you have chosen.

Probably somebody already did that, so a thorough web search might yield some example code.

13:47 Reply: How often sqlite flush to disk under NORMAL mode? (artifact: cc2541b1b6 user: knu)

That is a very broad question, and the answers are in the documentation.

Please visit the documentation pages, search for sync, read the relevant documents, and come back here if you have more detailed questions, not answered by the documentation.

03:21 Reply: How to check if HAVE_USLEEP is activated? (artifact: dbf1154db9 user: knu)

In my humble opinion, any development that needs this kind of detail should not use the amalgamation, but rather a checkout of a clone of the SQLite repository.

That said, config.h can be safely removed, and recreated by running ./configure, which will fill in the platform specific details.

make sqlite.c will build the amalgamation again.

Besides, keeping a clone of the repository up to date instead of downloading the amalgamation for every release is network friendly.

12:49 Reply: How to check if HAVE_USLEEP is activated? (artifact: 186ca05f60 user: knu)

Have a look at config.h

12:14 Reply: memory vs mmap (artifact: 67d5ef6b1b user: knu)

But I don't know why Sqlite would need to differentiate between two pointers and must go through a different codepath for mmap files.

I guess that is because a :memory: database can't be fully ACID. When the program exits or crashes, the database is lost, so it is not Durable. A :memory: dataabase is so to speak just a volatile page cache without backing store.

A file backed database (mmap or not) can be durable, with journaling and proper synchronization to permanent storage.

See also Architecture

22:42 Reply: Feedback on writing a SQLite VFS for a distributed object store (artifact: 6cacd30d01 user: knu)

In modern Operating Systems (that is, anything developed since 1970) this has been the job of the Operating System.

As a former systems programmer, I totally agree with you. And I support your reasoning.

15:06 Reply: Index Btree (artifact: 2cc73c01d3 user: knu)

I guess it would be possible to make a reasonable estimate using the file format specification in the documentation, but it will not be easy.

Additionally, you may want to study the btree source code, and other sources. All sources have a lot of informative and accurate comments.

Note that integer numbers are stored in a compact form, so low values consume fewer bytes than high values.

If you develop an algorithm, it would make sense to verify its validity with sqlite3_analyzer, which is available in the sqlite-tools download for your platform, or can be complied from source.

21:24 Reply: Place in the code where can understand that the file was created (artifact: 17ee57eccd user: knu)

Open the database with SQLITE_OPEN_CREATE.

If it returns an error, the file already existed.

The details are handled in the VFS module for your operating system, in your case probably src/os_win.c.

Search for Begin file os_win.c in sqlite3.c to find that code.

20:46 Reply: How to convert am/pm time to 24hrs time format (artifact: b50c873fe9 user: knu)

There was recently a thread on this forum with subject SQL query help for between times and grouping times which discussed the conversion from AM/PM to ISO-8601 notation.

22:15 Reply: How to save and load configuration files (artifact: 85fb6675d0 user: knu)

In the simplest form, you can save and extract files into / out of an SQLite archive from your application or using the SQLite command line shell.

But perhaps a fossil repository with version control is more suitable for your use case.

By the way, if you clone the repository of the fossil software itself, you can see how it stores its own configuration in a "key/value" store:

fossil sql -R /path/to/fossil.fossil "SELECT * FROM Config" | less

For more concrete answers, you will have to pose more concrete questions.

17:00 Reply: Where is the temporary database file stored? (artifact: 1fe9cfb542 user: knu)

... that the temporary file is only generated when it needs to be, rather than every time.

Not only that. In Operating Systems that allow it (Unix, Linux) SQLite will often unlink(3) temp files immediately after they are created.

The inode and space allocation will exist as long as SQLite has the file open, but the directory entry will vanish in less than the blink of an eye.

22:05 Reply: Sending output to a pipe (artifact: 291370e6f6 user: knu)

It is still very useful in scripts:

sqlite3 -bail test.sqlite <<EOSQL
, tx  TEXT
INSERT INTO a VALUES (3,'three');
.once |less
12:41 Reply: Why no release announcement in this Forum anymore? (artifact: 1778ca6fb1 user: knu)

Doesn't the forum have a page that shows all announcements

No, but if people are willing to visit a forum page with a browser, the SQLite home page and a bookmark to a filter on the repository timeline are good alternatives for release information.

11:03 Reply: Only Column Names read from table (artifact: b5788bffc7 user: knu)

If you really can't upgrade to version 3.16.0 or newer, you will have to use

PRAGMA table_info('Datalogger1');

This statement will return the same information in a result set, that you can retrieve in the same way as the result set from any SELECT statement.

If you use the value of cid as an index in the table where you store the column names in your program, the order in the result set will not matter.

14:18 Reply: Only Column Names read from table (artifact: 6fd4924c40 user: knu)


SELECT name FROM pragma_table_info('Uploads') ORDER BY cid;

The available columns are: cid,name,type,notnull,dflt_value,pk

15:52 Reply: Growing WAL-File (artifact: e4fb73dc00 user: knu)

You can checkpoint the WAL with PRAGMA wal_checkpoint().

There is even a PRAGMA to automatically checkpoint the WAL. Use that PRAGMA just after you open the connection, or call the equivalent sqlite3_API .

Make sure to preiodically COMMIT or ROLLBACK any open transactions.

02:34 Reply: For a given SQL query, mechanisms for figuring out which physical columns would be returned? (artifact: 04e4ed6168 user: knu)

I think this might work!

Ok, great. Just one remark I should have made before: The output format of EXPLAIN is not stable, it may change over versions.

Also, it lists the "byte code" for the virtual machine that executes the query, so if the optimizer get smarter, the code may change.

It might be stable enough for your purpose though.

00:09 Reply: For a given SQL query, mechanisms for figuring out which physical columns would be returned? (artifact: 482abd2e0f user: knu)

In my projects, I write queries with an eye on the schema, so I always know where the results come from.

Also, I never use SELECT x.* , because any added column will ruin my day.

Anyway, you could try to interpret the output of EXPLAIN SELECT, perhaps in combination with PRAGMA table_info(), but it isn't easy, especially with aliased rowid.


,	name  TEXT
,	name  TEXT
INSERT INTO b VALUES (3,'three');
INSERT INTO a VALUES (1,'one',2);
.mode column
.headers on
SELECT * FROM pragma_table_info('a')
SELECT * FROM pragma_table_info('b');
EXPLAIN SELECT a.*, b.* FROM a JOIN b ON a.b_id = b.id;
SELECT a.*, b.* FROM a JOIN b ON a.b_id = b.id;


cid  name  type     notnull  dflt_value  pk
---  ----  -------  -------  ----------  --
0    id    INTEGER  1                    1
1    name  TEXT     0                    0
2    b_id  INTEGER  0                    0
0    id    INTEGER  1                    1
1    name  TEXT     0                    0
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     16    0                    0   Start at 16
1     OpenRead       0     2     0     3              0   root=2 iDb=0; a
2     OpenRead       1     3     0     2              0   root=3 iDb=0; b
3     Explain        3     0     0     SCAN a         0
4     Rewind         0     15    0                    0
5       Explain        5     0     0     SEARCH b USING INTEGER PRIMARY KEY (rowid=?)  0
6       Column         0     2     1                    0   r[1]=a.b_id
7       SeekRowid      1     14    1                    0   intkey=r[1]
8       Rowid          0     2     0                    0   r[2]=rowid
9       Column         0     1     3                    0   r[3]=a.name
10      Column         0     2     4                    0   r[4]=a.b_id
11      Rowid          1     5     0                    0   r[5]=rowid
12      Column         1     1     6                    0   r[6]=b.name
13      ResultRow      2     5     0                    0   output=r[2..6]
14    Next           0     5     0                    1
15    Halt           0     0     0                    0
16    Transaction    0     0     2     0              1   usesStmtJournal=0
17    Goto           0     1     0                    0

id  name  b_id  id  name
--  ----  ----  --  ----
1   one   2     2   two

This probably doesn't solve your problem, but it might give some inspiration.

17:52 Reply: Serious problems with STORED columns (artifact: e21f2ff0be user: knu)

stored is persistent, the values just don't survive

sqlite3 olddb .dump | sqlite3 newdb

But they will survive .backup / .restore and VACUUM INTO.

17:46 Reply: SQL to return list of PRAGMAs (artifact: 318944bf6b user: knu)

Can I get this list using SQL?

Not just like that. You'll have to write a script/program that executes the pragmas you are interested in against your database, and compare the outcome with the outcome of the same script run against a "default database".

Something like:

for p in $(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3 default.sqlite >pragma0.txt
for p in $(cat relevant_pragmas) ; do printf ".print %s::\nPRAGMA %s;\n" "$p" "$p" ; done | sqlite3  yourdb.sqlite >pragma1.txt
diff -u pragma0.txt pragma1.txt
01:42 Reply: Serious problems with STORED columns (artifact: c545226a94 user: knu)

Unlike a VIRTUAL column that is computed on the fly, a generated STORED column is actual stored data and as such it must survive dumps, i.e., it must be possible to restore to the exact same content it had before a DUMP.

That would be hard, as a dump file just contains DDL and INSERT statements. And you can't INSERT :

insert into log(dt,msg) VALUES ('somestamp','c');
Error: near line ...: cannot INSERT into generated column "dt"

AFAIC, a solution is needed for that. The current behavior in my eyes is buggy!

A solution is available in the backup interface and the VACUUM INTO statement.

08:30 Reply: How to READ and WRITE an image to an SQLITE BLOB FIELD (artifact: 9ae1d26e28 user: knu)

I don't have any working code and this is not the place to discuss it.

A web search for "PHP sqlite database store image file tutorial" returns quite a few examples. Many are about MySQL, but the mechanisms are mostly the same.

A PHP forum or usenet group would be a better place to ask.

21:06 Reply: How to READ and WRITE an image to an SQLITE BLOB FIELD (artifact: 6167f4d8bf user: knu)

The reference material says:

7.3. File I/O Functions

The command-line shell adds two application-defined SQL functions that facilitate reading content from a file into a table column, and writing the content of a column into a file, respectively.

So, those functions are part of the SQLite shell, and are not in the SQLite library by default. The SQLite library that PHP offers will not have them either, because it doesn't need them.

You will have to use PHP functions to read the image into memory and bind it as a BLOB to your prepared INSERT statement, and the other way around for SELECT.

12:48 Delete reply: BUG Precompiled sqlite shell for windows can't open filenames with unicode chars (artifact: 4ac1166b90 user: knu)
12:37 Reply: BUG Precompiled sqlite shell for windows can't open filenames with unicode chars (artifact: da23e11181 user: knu)

But I'm using a old version of windows, which doesn't have well support of 'chcp 65001',

We can't solve that for you. Codepage 65001 was already available in Windows XP. This article may help you.

and I'd like drag database to the icon of sqlite.exe. It would be appreciated if precompiled sqlite shell have native unicode support.

As far as I know, the precompiled shell has native unicode support, but it depends on the Windows "terminal".

Try this: Make a new shortcut (icon) which does not point to sqlite3.exe, but to a .cmd file that you write yourself, which sets up the environment for sqlite. Something like:

@echo off
chcp 65001
disk:\path\to\sqlite3.exe "%1"


12:45 Reply: SAVEPOINTS (artifact: 4c7e9a3c76 user: knu)

According to the syntax diagram for CREATE TRIGGER, SAVEPOINT is not a supported statement.

You can make a trigger execute conditionally with the WHEN expr syntax.

Also, RAISE() can influence the result of the statement that caused the trigger to fire.

By the way, your code example does not represent a trigger definition, and it is unclear what problem you experience.

15:35 Reply: external pages or resources returned a 403 HTTP status code (artifact: 8ac4e6ed74 user: knu)

I have a similar construction, but I generate the site from a local clone of the sqlitedoc repository.

Then I run a postprocessing script over the generated html pages to replace any references to sqlite.org with links to my local site.

22:42 Reply: external pages or resources returned a 403 HTTP status code (artifact: 1a528b630f user: knu)

Yes, sqlite.org has protections in place to prevent bots from (recursively) iterating over all links in the web site.

In my opinion, your tool should not try to follow external links.

Any content of sqlite.org is not part of your website and should not be analyzed by your tool.

It is perfectly fine to have hyperlinks to sqlite,org on your site, and your users are free to click them. But the tools should not do that.

Just my two cents.

08:51 Reply: Cannot change my email settings (artifact: 4c4bb3b65c user: knu)

Other forums that I use allow me to request email notifications of replies to any thread that I have posted to or replied to.

I use filters in my email client whenever I need that.

The forum does not need to know which posts I have read to do this: it only needs to know which threads I have posted or replied to, which can be found by a search.

The forum software is discussed on the fossil forum .

Your patches might be considered, if processor and I/O load are not excessive.

15:23 Reply: sqlite3_exec: the 3rd argument (artifact: f5e410ce9a user: knu)

Please refer here for my code (also, note the response/advice the callback interface is rarely the best way to do something even in C.

That stackoverflow post only shows the function headers, not the actual processing code.

15:50 Reply: Additional Delimiter Support for the CSV Virtual Table Module (artifact: ef8b7677b5 user: knu)

Other separators are supported by the sqlite3 shell. Consider this script:

set -x # echo commands
rm -f test.db* test.csv # clean start
# create colon delimited input file
cat >test.csv <<EOF
sqlite3 test.db \
a TEXT, \
b TEXT)" \
".mode csv" \
".separator ':'" \
".import test.csv t1" \
".mode box" \


Kees Nuyt
17:48 Reply: General question concerning database stucture and number of databases to use. (artifact: 140ca8461e user: knu)

The point with BLOBs is, they can easily be larger than a database page, so overflow pages get involved. They have to be read from disk if not cached.

If the BLOB itself is not referenced by the query, reading those overflow pages can be avoided by placing all short columns at the beginning of the row.

Kees Nuyt
More ↓