Skip to main content

Questions tagged [sqlite3]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
1 answer
41 views

Issues with Self-Referencing Foreign Key in SQLite

I'm having trouble with a self-referencing foreign key in SQLite. I've created a table to store employee details, where each employee can have a manager who is also an employee. Here's the table ...
reubenjohn's user avatar
0 votes
2 answers
64 views

How can I select all rows from two SQLite tables, but prefer one if there are matching rows in both?

I have two tables, each with a round and an id column and additional data columns. One table is the planned version, the other one is the actual one. E.g.: planned: round | id | value ------+----+-----...
Tobias Leupold's user avatar
1 vote
2 answers
24 views

SQLite Join 2 tables with table1 having two columns that reference table2

I have two tables: jobs CREATE TABLE IF NOT EXISTS jobs ( job_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, job_name TEXT, prime_desc INTEGER NOT NULL REFERENCES descriptions(desc_id), ...
Erich4792's user avatar
-1 votes
1 answer
50 views

Return data in the same order that I have inserted it with Sqlite3

I'm working with, I think, the lastest version of Sqlite and C++. I have a table with data, and I need to return this data in the order I inserted it. Do I have any guarantee that the columns will ...
VansFannel's user avatar
  • 1,873
0 votes
1 answer
32 views

Create composed key (YEAR+COUNT) field with trigger after insert in SQLITE

I have a table Product with two columns: name of product and price CREATE TABLE IF NOT EXISTS YearCounters ( year INTEGER PRIMARY KEY, counter INTEGER DEFAULT 0 ); -- Product Table CREATE ...
kiaderouiche's user avatar
-1 votes
1 answer
62 views

How do I partition my database by rowid and query the partitions properly?

Obligatory I'm a beginner at database management. To be specific about my situation, I'm working in SQLite3 in Python as a means to an end for now for a finance project. The table is called ...
KillerDiek's user avatar
0 votes
2 answers
61 views

Select random records until a cumulative total is exceeded

I have a table of videos with an id, skill_level and duration column and I am attempting to write a query that will return a playlist of distinct videos that has a total duration exceeding X. select ...
Ashley's user avatar
  • 103
-1 votes
1 answer
855 views

sqlite3.OperationalError: no such column: stock_name

#user_input.py from flask import Flask, request, jsonify import sqlite3 app = Flask(__name__) def process_user_input(data): # Retrieve user input from the data stock_name = data.get('...
Steven's user avatar
  • 107
1 vote
1 answer
102 views

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

I need to build a multi-tenant solution with a requirement for strict data isolation (meaning isolated Databases at tenant level). Even the authentication is different for each tenant? I find that ...
Harshal Patil's user avatar
3 votes
2 answers
356 views

Sqlite comparison of the same operand types behaves differently

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 ...
mvorisek's user avatar
  • 418
1 vote
1 answer
165 views

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

Basically the next step for Error "UNIQUE constraint failed" with composite primary key: I created a trigger after insert to set SEQ to the highest SEQ plus one if it's less than one. ...
U. Windl's user avatar
  • 125
0 votes
1 answer
1k views

Error "UNIQUE constraint failed" with composite primary key

With littele SQL(ite) practice, I tried to create a table with a composite primary key: CREATE TABLE NAMES ( TYPE INTEGER NOT NULL, SEQ INTEGER NOT NULL, NAME VARCHAR(20), PRIMARY KEY (TYPE, ...
U. Windl's user avatar
  • 125
0 votes
1 answer
24 views

Name a column with a string from the row

I have the following query, which works fine: select Produkt.Name, max(case when Substanz.ID == 1 then Inhalt.Menge end) as "Histidin", max(case when Substanz.ID == 2 then Inhalt.Menge ...
ceving's user avatar
  • 359
0 votes
1 answer
18 views

coalesce works only once

I have a table with redundant values in the first two columns: sqlite> select * from amino where "lebensmittel" = 'Walnüsse'; Lebensmittel Protein pro 100g Histidin Isoleucin Leucin ...
ceving's user avatar
  • 359
1 vote
0 answers
16 views

is it possible to repair a partially downloaded sqlite3 file

I was only able to partially download a SQLite3 database file, is it possible to repair the file so that it can be opened? Unfortunately the device where the file came from is out of power and ...
Ben Bird's user avatar
  • 111
0 votes
1 answer
50 views

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

In my application that I am making, I have the following table records ------ id PK pattern TEXT In this database I have records like this id pattern 1 https://*.google.com 2 https://google.com/* ...
Dimitrios Desyllas's user avatar
0 votes
2 answers
253 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, ...
Dimitrios Desyllas's user avatar
0 votes
1 answer
47 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, ...
Dimitrios Desyllas's user avatar
0 votes
2 answers
64 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, ...
Dimitrios Desyllas's user avatar
3 votes
2 answers
3k 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: ...
Tobias Leupold's user avatar
0 votes
1 answer
800 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 ...
Tobias Leupold's user avatar
0 votes
1 answer
1k 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, ...
ordinary_guy's user avatar
0 votes
1 answer
665 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 ...
Emi OB's user avatar
  • 101
3 votes
2 answers
2k 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 ...
Kum's user avatar
  • 47
0 votes
1 answer
2k 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 ...
g00dds's user avatar
  • 103
1 vote
0 answers
77 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 | ...
Paulo's user avatar
  • 11
0 votes
1 answer
281 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 ...
mvorisek's user avatar
  • 418
2 votes
1 answer
59 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. ...
студент001's user avatar
0 votes
1 answer
422 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 ...
Denis Borzenko's user avatar
1 vote
0 answers
255 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 =...
Riajul's user avatar
  • 111
0 votes
1 answer
502 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 ...
dogfood's user avatar
1 vote
1 answer
493 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 ...
Nathan Goings's user avatar
0 votes
1 answer
1k 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 ...
user2058625's user avatar
0 votes
0 answers
234 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, ...
user3030327's user avatar
0 votes
1 answer
3k 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 ...
J.D.'s user avatar
  • 39.5k
8 votes
2 answers
413 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) ) ...
std_unordered_map's user avatar
0 votes
1 answer
24 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 ...
zepher4's user avatar