Questions tagged [sqlite3]
The sqlite3 tag has no usage guidance.
21
questions
0
votes
2
answers
23
views
How I can check whether a column contains a substring of a given searchterm?
In sqlite I have the following table
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
0
votes
1
answer
22
views
How I can make a single trigger for both create and update at sqlite3?
I made the following table:
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
0
votes
2
answers
38
views
What I make wrong and Cannot inset a value at sqlite3 using custom trigger?
I am making the follwing table at SQLITE3:
CREATE table IF NOT EXISTS redirect (
id INTEGER PRIMARY KEY AUTOINCREMENT,
url_from TEXT not null,
url_to TEXT not null,
...
2
votes
1
answer
220
views
Retrieving the local timezone inside SQLite
I have an SQLite table containing a "last changed" column with a date in the format e.g. "2022-11-07T11:51:06+01:00". Coreutils' date outputs this by using the following command:
...
- 127
0
votes
1
answer
77
views
Summing/Grouping LAG() results in SQLite query
I have a table with incremental counters in an SQLite database. A stripped-down example would be:
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "counters"(date TEXT ...
- 127
0
votes
1
answer
210
views
Archiving SQLite DB with WAL mode
I have an SQLite database gapongasi.db configured using WAL mode and I want to have daily archive with tar(1). With WAL, there are two files being used (1) gapongasi.db-wal and (2)gapongasi.db-shm, ...
0
votes
1
answer
44
views
Is there a way to VACUUM an sqlite3 database in parts to avoid OperationalError: database or disk is full/reduce size of sqlite3 databse
I have a rather large sqlite3 database that I've built/populated through python over a few months. I have done many inserts/deletes etc while putting it together and it now uses the majority of my ...
- 101
2
votes
2
answers
428
views
1st process writes, 2nd one reads -- " SQLITE_BUSY: database is locked "
I have 2 processes that use the same Sqlite3 database.
1st one -- in NodeJs, is a program which constractly writes data into a database, with the frequency of around 1 write / 1 second. It only ...
- 37
0
votes
1
answer
359
views
Why UPDATEs are faster when WHERE key is sorted in SQLite? How to improve performance?
I have a large database and a file with ID-Name pairs in each line, where ID corresponds to the primary key of the table in the database.
I need to write all the names from the file into the database ...
- 3
1
vote
0
answers
17
views
How do I make sure a specific TRIGGER will be executed only when other TRIGGERS are finished executing and return?
foo table
| row# | foo_col1 | foo_col2 | foo_col3 |
| -----|----------|----------|----------|
| 0 | 1 | 5 | 1 |
| 1 | 2 | 3 | 3 |
| 2 | 3 | ...
- 11
0
votes
1
answer
39
views
Sqlite spec: 4.2. Type Conversions Prior To Comparison
Based on Sqlite docs: https://www.sqlite.org/datatype3.html#type_conversions_prior_to_comparison, especially this statement:
If one operand has INTEGER, REAL or NUMERIC affinity and the other operand ...
- 426
2
votes
1
answer
56
views
Understanding `WHERE x = NEW.x` Syntax
I'm writing an application that uses SQLite to store court information obtained from a government database in a client table. I've set up a DB Fiddle here, which is a simplification of my application.
...
- 123
0
votes
1
answer
149
views
SQLite: excluded int primary key null value replaced with next auto value
I'm trying to create generic upsert query builder with specific behavior. Table has two unique constraints: primary key itId and column slug:
create table it (
itId integer primary key -- an alias ...
0
votes
0
answers
107
views
Proceeds JOIN only if condition met on the Owner table
I have many hasOne relationships like this:
components
component_headings
component_images
Here one Component can only have one hasOne to any of [headings, images].
Logically when a Component.type =...
- 101
0
votes
1
answer
160
views
formating dot commands in sqlite3
My first SQL database im using sqlite3 in idle on linux. Everything is working fine but I want my table to display in an easier to read format in the terminal. been reading about dot commands eg .mode ...
- 1
0
votes
1
answer
117
views
Random value duplicated, cannot select N random pairings, subquery not refreshing - sqlite
The Problem
Using SQLite v3.35.4 and v3.36.0 I have a first_name table and a surname table that have a list of common names. I want to produce N number of pairings into a new table.
I wrote this ...
- 105
0
votes
1
answer
512
views
SQLite Aggregation then Transpose
I am currently uploading my solar inverter data to PVOutput using a Linux machine and a scratch built Perl script. I want to add the meter data from my enrgy supplier. I have an 87,000 row CSV file ...
0
votes
0
answers
144
views
How to create two sqlite triggers for two tables without conflict?
Below are my example queries:
"""
CREATE TABLE IF NOT EXISTS projects (
Project_ID TEXT UNIQUE,
Initial_Project_count TEXT,
Additional_Project_count TEXT,
...
- 101
0
votes
1
answer
1k
views
Is it possible to create Dynamic SQL statements purely in SQLite? [closed]
A lot of modern RDBMS have a feature called Dynamic SQL (MS SQL Server, for example) which allows you to build your SQL statements in strings and then execute that string of SQL.
Does such a feature ...
- 29.7k
8
votes
2
answers
320
views
Sqlite: Finding the next or previous element in a table consisting of integer tuples
I have a sqlite table called tuples defined like
create table tuples
(
a INTEGER not null,
b INTEGER not null,
c INTEGER not null,
d INTEGER not null,
primary key (a, b, c, d)
) ...
0
votes
1
answer
16
views
How can I display multiple column records in the same row?
I want to display all related information in to the same row column. Currently my output and query are the following:
Trader
Items
Prapor
Weapons
Prapor
Wearables
Prapor
Weapon Modifications
...
- 1