Skip to main content
9 votes
Accepted

Sqlite: Finding the next or previous element in a table consisting of integer tuples

At the moment I try to find the next tuple with ("center" is (1-1-1-3)) Test SELECT * FROM tuples WHERE (1,1,1,3) < (a,b,c,d) ORDER BY a,b,c,d LIMIT 1 fiddle
Akina's user avatar
  • 20k
4 votes
Accepted

Retrieving the local timezone inside SQLite

You cannot extract the timezone, but you can calculate it. ROUND((JULIANDAY('now', 'localtime') - JULIANDAY('now')) * 24) -- this gives the number of hours (signed) ABS(ROUND((JULIANDAY('now', '...
Andrea B.'s user avatar
  • 1,613
4 votes

1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "

The first problem is that your application is not handling the busy error. By default sqlite will NOT wait for the other processes to complete their read or write operation, but will return the error. ...
Andrea B.'s user avatar
  • 1,613
2 votes

Sqlite: Finding the next or previous element in a table consisting of integer tuples

You should store the 4 values concatenated and then converted to an int in a Generated Column. You can then index that column for better for performance and then your queries are just a comparison to ...
J.D.'s user avatar
  • 38.1k
2 votes

formating dot commands in sqlite3

https://sqlite.org/cli.html says: 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 ...
Bill Karwin's user avatar
  • 15.1k
2 votes
Accepted

Random value duplicated, cannot select N random pairings, subquery not refreshing - sqlite

"I want to produce N number of pairings into a new table." Why not just simply do a CROSS JOIN with a LIMIT clause like so? SELECT fn.first_name, sn.surname FROM first_name fn CROSS JOIN ...
J.D.'s user avatar
  • 38.1k
2 votes
Accepted

Summing/Grouping LAG() results in SQLite query

You cannot use nested aggregate/window functions. So use your 1st query as CTE, and aggregate it in outer query. WITH cte AS ( SELECT STRFTIME('%Y-%m', DATE(date, 'localtime')) month, ...
Akina's user avatar
  • 20k
2 votes
Accepted

Sqlite comparison of the same operand types behaves differently

Type affinity should not be confused with Data Type (which Sqlite calls Storage Class). Every single value in Sqlite has a Storage Class, which is always either NULL, INTEGER, REAL, TEXT or BLOB. 4 is ...
Andrea B.'s user avatar
  • 1,613
2 votes

Sqlite comparison of the same operand types behaves differently

I think you've spotted a slight inconsistency in the behaviour of SQLite! All of the code below is available on the fiddle here. First we have: SELECT 4 = '4' AS "INT_to_TEXT"; -- No ...
Vérace's user avatar
  • 30.2k
1 vote
Accepted

Select random records until a cumulative total is exceeded

In other words, the task is to get a list of records with a sum less then threshold, and one additional record to go over the threshold? This can be achieved by using with and union clauses: with ...
White Owl's user avatar
  • 853
1 vote

sqlite3.OperationalError: no such column: stock_name

stockName is a database column. stock_name is a python variable. Compare these two: c.execute("SELECT price FROM stocks WHERE lower(stockName) = ?", (stock_name.lower(),)) c.execute('update ...
Phill  W.'s user avatar
  • 8,860
1 vote

Is frequent opening and closing of the SQLite file expensive operation?

Will this frequent opening of SQLite file cause considerable penalty if load increases moderately? As great as SQLite is, it wasn't necessarily designed with concurrency built in mind as much as most ...
J.D.'s user avatar
  • 38.1k
1 vote
Accepted

insert trigger to auto-set key fields for composite primary key

Are you certain that you can have only one update per trigger? I didn't find any reference to this limit in the sqlite docs. Anyway, you can just combine both updates in a single one: CREATE TRIGGER ...
Andrea B.'s user avatar
  • 1,613
1 vote
Accepted

Error "UNIQUE constraint failed" with composite primary key

The problem was the non-unique WHERE clause missing AND SEQ = NEW.SEQ in the trigger. This version of the trigger works correctly: CREATE TRIGGER TRG_NAMES_ADD AFTER INSERT ON NAMES FOR EACH ROW BEGIN ...
U. Windl's user avatar
  • 125
1 vote
Accepted

How I can find a best match on string records that use * as a wildcard?

In SQL, the wildcard symbol that works like * in file paths is %. You need to replace the former with the latter for the pattern to work. You can use the REPLACE() function to do the substitution, ...
Andriy M's user avatar
  • 23.1k
1 vote
Accepted

How I can make a single trigger for both create and update at sqlite3?

No, it isn't possible with a single trigger to handle both insert and update. The CREATE trigger statement on the graph shows that only one of the DELETE, INSERT, UPDATE can be used.
Ergest Basha's user avatar
  • 3,945
1 vote
Accepted

Archiving SQLite DB with WAL mode

Yes, the WAL file should be archived along with the .db, but if you archive them when the application is closed, there should be no wal file at all. From the docs: While a database connection is open ...
Andrea B.'s user avatar
  • 1,613
1 vote

Is there a way to VACUUM an sqlite3 database in parts to avoid OperationalError: database or disk is full/reduce size of sqlite3 databse

Use VACUUM INTO filename; where filename is a full path on another disk (even a removable one). This will leave your original db unchanged and will not require space from the origin disk. After the ...
Andrea B.'s user avatar
  • 1,613
1 vote

1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "

SQLite is not a multiuser database and should not be used as such. If you require more than one application working with the database at the same time, choose a proper multiuser DBMS. Documentation ...
mustaccio's user avatar
  • 26.3k
1 vote
Accepted

Why UPDATEs are faster when WHERE key is sorted in SQLite? How to improve performance?

Rows are stored in pages within the database and sqlite will always read or write whole pages. Default size of page is 4096 bytes, so if each row is 40 bytes of data then a page can contain up to 100 ...
Andrea B.'s user avatar
  • 1,613
1 vote
Accepted

Sqlite spec: 4.2. Type Conversions Prior To Comparison

I think you may be missing this part: with the CAST operator the conversion always takes place even if the conversion lossy and irreversible, whereas column affinity only changes the data type of a ...
mustaccio's user avatar
  • 26.3k
1 vote
Accepted

Understanding `WHERE x = NEW.x` Syntax

This is not actually a DBA question but rather a Boolean algebra question (which means it might be more at home on Stack Overflow.) In short, you want the trigger to fire if EITHER: a_number is ...
Darwin von Corax's user avatar
1 vote
Accepted

Is it possible to create Dynamic SQL statements purely in SQLite?

No. SQLite is designed as a small, embedded database to be accessed from a 'real' programming language. Dynamic SQL is not needed for its intended purposes.
CL.'s user avatar
  • 5,243

Only top scored, non community-wiki answers of a minimum length are eligible