Questions tagged [sqlite3]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
17 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
1 vote
2 answers
76 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 ...
  • 27
0 votes
1 answer
118 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
11 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
22 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 ...
  • 416
2 votes
1 answer
54 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. ...
0 votes
1 answer
44 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
37 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
101 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 ...
0 votes
1 answer
63 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 ...
0 votes
1 answer
272 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
76 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, ...
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 ...
  • 26.5k
8 votes
2 answers
289 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
15 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 ...