Questions tagged [sqlite]
A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.
680
questions
-1
votes
1
answer
19
views
select user with least amount of tiquets between all other users
I have a table where I store users info like this
user
tickets
s.lewis
0
a.barrantes
2
x.mendez
3
j.robert
0
I want to create a query to select the user that has the least amount of tickets ...
0
votes
0
answers
22
views
User types as foreign keys
Largely theoretical question or idea discussion
Lets say we have several simple dictionaries:
create table dic1(code integer primary key, description varchar(256));
create table dic2(code integer ...
- 645
0
votes
3
answers
49
views
Single-tenant SQLite vs Postgres + SQLite
About the app
I am building an application which will have 2 clients: web and mobile. Both clients will need to auth to sync the user data. On average, each user will generate 2000 rows of data.
The ...
- 113
0
votes
1
answer
41
views
Optimizing the SQLite database as source data for R scripts
I have an application written in R Shiny that supports a large number of csv's (and 3 new ones are added every day). These files have a structure that is quite unpleasant to handle, so I decided to ...
- 101
-2
votes
1
answer
63
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('...
- 97
0
votes
0
answers
10
views
How do I migrate SQLite to Postgres for OWASP Juice Shop?
I tried to make a .sql but it's failing.
My files here: https://drive.google.com/drive/folders/1BAs5YFPWpShzIgtd-NppL0iWZ_HywWNf?usp=sharing
I also tried using pgloader but that was even more ...
- 105
0
votes
1
answer
35
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 ...
- 101
2
votes
2
answers
125
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 ...
- 398
1
vote
0
answers
15
views
Find best intersect (most elements) between Order (haystack) and Pricerules (needle)
Full disclosure
I have posted a greater scoped question at SO a couple of days ago with a bounty (no answers yet).
Scope here
How can a CTE be used to find a match with the most elements?
For the ...
- 209
0
votes
1
answer
47
views
Column accepting more characters than defined
I have a column defined as character varying(20). But it is accepting more than 20 characters. I've already searched but couldn't find anything about it (maybe I'm searching wrong). Has anyone ...
- 3
0
votes
1
answer
46
views
SQL Lite is giving an "index out of range" error
When I run this code in Rust
statement.read::<i64, _>("creation_utc").unwrap(),
I get,
Error { code: None, message: Some("the index is out of range (creation_utc)") }
But ...
- 62.2k
1
vote
1
answer
145
views
Query for repeating sequences in SQLite
I have an SQLite DB, let's assume the id can be considered as the order of rows, I want to find repeating sequences in the table according to certain criteria.
consider this example (http://sqlfiddle....
- 167
0
votes
1
answer
17
views
How do we recurse through this table?
I have a simple family tree table that includes:
PIN Numeric, primary key
FATHER_PIN ID of father
MOTHER_PIN ID of mother
I want to recurse through the tree with:
WITH RECURSIVE
parent_of(PIN, ...
- 111
0
votes
3
answers
48
views
Can you formulate an SQL command for this case?
I have an SQLite table PERSONS with the following columns, all integers:
PIN (unique ID)
SEX (1 = male, 2 = female)
FATHER_PIN (PIN of person's father)
MOTHER_PIN
I want to find all couples that had ...
- 111
0
votes
0
answers
11
views
Visual editor for imputing sqlite foreign key ids using another field
Here's my use case: I want to build a simple database that maps a reverse DNS domain back to an email sending service, and information about that service. I have a table of email source information ...
- 125
0
votes
2
answers
31
views
In GRASS GIS db.execute IN statement works, but NOT IN doesn't
Here is my command for GRASS GIS database (SQL Lite driver used):
db.execute sql="UPDATE streams_order SET factor=next_stream WHERE number IS NOT NULL AND next_stream IN (SELECT number FROM ...
2
votes
1
answer
114
views
DBMI-SQLite driver error: Error in sqlite3_prepare(): near ".": syntax error in db.execute command
I try to make a query with inner join from grass gis (but in that community this question was concerned as offtopic, so it seems to concern only SQL lite):
db.execute sql="UPDATE A SET A.next2=B....
0
votes
2
answers
36
views
Can many table names having the same prefix slow down a database?
I understand (maybe wrongly) that SQL databases often use associative arrays based on B-trees and that looking up entries in these arrays entails binary search that lexicographically compares a key ...
- 101
0
votes
0
answers
84
views
SQLite Upsert - syntax error
I'm trying to use SQLite's upsert syntax as described in their documentation
However I get the following error, in both WebSql (SQLite v3.41.2) and SQLite on Android (SQLite v3.40.0)
could not prepare ...
- 51
1
vote
1
answer
87
views
storing histogram in sqlite database
I am not a database administrator, but only a scientist who would appreciate your help in solving my issue with storing histogram data in an SQLite database.
I have several of them to be stored and to ...
- 11
0
votes
1
answer
39
views
Order the same column by ascending or descending dependant on the value of another column
I've got a situation where I want to order a column by ascending or descending based on the value of a second column (in essence, if column A is odd, sort column B ascending, otherwise descending). ...
- 3
1
vote
1
answer
58
views
Database design suggestion for improving existing design
Background
I am working on an IoT application that deals with sensors, their configurations and their readings. Following are the requirements of the system,
Each sensor can support multiple features....
- 111
1
vote
2
answers
48
views
What is the evaluation clause order in SQLite?
Traditionally in SQL, the main clauses are evaluated as:
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Among other things, it explains why you can’t use alias from the SELECT clause in other clauses ...
- 2,779
0
votes
1
answer
23
views
update row with age calculation with DOB in related table
SQLite database with a table "events" with a columns events.eventDate, events.ageAtEvent and events.foreignKey_participants, and a table "participants" with columns participants....
0
votes
0
answers
13
views
Sum profits of sales for every sale in SQLite using avg purchase price?
In my SQLite database i have the following tables and data:
Products:
pro_id. pro_name
1. Item1
Purchases:
pur_id pro_id. pur_qty. pur_price
1. 1. 10. 10
2. 1. ...
- 101
0
votes
2
answers
30
views
How to sum profits of all products using AVG prices in SQLite?
In my SQLite I have the following tables:
Products:
Pro_id. Pro_name
1. Item1
2. Item2
3. Item3
4. Item4
Purchases:
Pro_id. Pur_Qty Pur_Price
1. 10. 10
...
- 101
1
vote
1
answer
189
views
SQLite on AWS EBS vs MySQL AWS RDS
I'm planning to host a web application that consists of a Front-end, Back-end (Written in NestJS + TypeORM). This Web application requires a database like many others.
Something I'm struggling with ...
- 11
2
votes
1
answer
82
views
script SQL equivalent
I have this SQLite script:
CREATE TRIGGER ACCOUNT_UPDATE_DELETE_SESSION_ID_NULL AFTER UPDATE OF DELETE_SESSION_ID ON ACCOUNT
FOR EACH ROW
WHEN OLD.DELETE_SESSION_ID IS NOT NULL AND NEW....
- 33
0
votes
1
answer
150
views
Same data stored in more rows with more repetition results in smaller SQLITE file?
I'm comparing two very barebones SQLite databases with the same data stored differently.
Database A has tables like this:
string (TEXT UNIQUE)
count_1 (INTEGER)
count_2 (INTEGER)
the
23
10
it
56
44
...
- 13
2
votes
1
answer
324
views
UPSERT fails for INSERT expression with SELECT
I ran into a problem with the UPSERT statement in SQLite.
I have a master and a slave database, the updates to the slave database should be merged into the master database. I would like to use the ...
- 23
3
votes
1
answer
31
views
Help with SQLite query to generate new table based on lookups
Sorry, I can't think of a more specific title.
SQLFiddle here: http://sqlfiddle.com/#!5/cfed3/1/0
I have a BaseData table
Name
ParamId
NumericValue
A
202
30
B
203
69
O
203
50
A
203
70
I
204
2
B
...
- 45
0
votes
1
answer
108
views
What's the easiest way to implement division in SQLite?
I'm putting together a simple cooking application that holds recipes in an SQLite database. I have three tables right now: Recipes, Ingredients, Recipe_Ingredients
* Recipes
-------------------
name,
-...
- 141
1
vote
0
answers
123
views
Is my food-tracker's database designed properly? (ER diagram + SQL Relations)
My idea:
The database will store foods, days, and "inserted foods" aka diet_logs (days and diet_logs have both existence dependency)
for each diet_log of a specific day, I will create a ...
- 11
1
vote
2
answers
221
views
Calculating the total time a device is on per day
At the moment I've got an sqlite3 database that keeps track of the state of my smart home devices. The relevant parts of the schema for the main table are
CREATE TABLE states(
state_id INTEGER NOT ...
- 113
5
votes
1
answer
870
views
How does offline-first database design work?
I would like to make an offline-first To-do app which works on both the mobile app (SQLite) and the web app (PostgreSQL). The app will have these business models:
User:
id
name
created_datetime
Todo:...
- 151
0
votes
0
answers
162
views
Is it possible to split aggregated text into multiple sqlite3 columns?
I'm trying to pivot rows into columns on an SQLite3 database. This is not a native function on this DB, so I thought it could have been possible to manage the task via cyclically running a set instr/...
- 225
0
votes
0
answers
429
views
DB Browser for SQLite foreign key errors
My Main Table is:
CREATE TABLE "STUDENT" (
"last_name" TEXT NOT NULL,
"first_name" TEXT NOT NULL,
"date_of_birth" INTEGER NOT NULL,
"...
- 1
3
votes
1
answer
49
views
best practice for dependency roots
When a database has a dependency table showing the parent-child relationship of items...
I've mostly seen guides that assume the top level parent has a "parent id" field which is either zero ...
- 31
0
votes
1
answer
224
views
SQLite database isolation level on the same connection
SQLite documentation https://www.sqlite.org/isolation.html states that on the same connection: "there is no isolation between operations that occur within the same database connection."
As ...
- 103
0
votes
1
answer
2k
views
count occurances inside a json array
I have a table with a column that holds a JSON array, and I want to count the array members that match a certain criteria. When I run the query I always get a count of zero, so I assume that I have ...
- 103
1
vote
0
answers
331
views
Pandas to_sql and JSON data
I'm in the process of creating a Python application which takes in a JSON encoded file and stores the information in an SQLite database in memory.
The JSON file in itself is essentially a Database for ...
- 11
0
votes
1
answer
92
views
keeping only first value of every minute in table
i have a huge table called "eFact" with timestamps and values
example:
ts value
__________________________
20221018080350000 22756.9
20221018080354028 22756.9
...
7
votes
3
answers
7k
views
SQLite - How does Count work without GROUP BY?
With a table like:
first_name last_name
------------------------
jack frost
john wayne
betty white
null jackson
And this query:
select first_name, COUNT(...
- 181
3
votes
1
answer
2k
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:
...
- 137
0
votes
2
answers
298
views
How to use foreign key, to remove subentries?
I try to delete all the subcategories and entries within this subentries, when i delete the main categorie from within one table. If i had multiple tables, i would use a foreign key with ON DELETE ...
- 13
0
votes
0
answers
58
views
SQL Query - restructure columns to rows
I'm stuck! Wondering if someone can help me figure out an SQL query. I've got some data that looks like the following:
Date
Country
Total
1/1/2022
Australia
1234
2/1/2022
Australia
5678
3/1/2022
...
- 1
1
vote
0
answers
273
views
Resample time series in SQLite
I have following table:
CREATE TABLE entries (
utc TEXT,
entry INTEGER
);
with following data:
INSERT INTO entries
(utc, entry)
VALUES
('2022-10-01 01:54', 23),
('2022-10-04 02:40', 46),
(...
- 113
0
votes
1
answer
979
views
Sqlite fails when performing an 'insert' with a 'returning' value clause
I am having problems with SQLite? I didn't have this problem when using the same tables on PostgreSQL. When the table is empty and you use the following SQL statement it throws an error.
INSERT INTO ...
- 103
3
votes
1
answer
211
views
Valid for WITH clause to use existing table name?
Suppose I have a table Entry. Is it valid for a WITH clause to replace the Entry table for the duration of a query? I tried with SQLite and MySql and it seems to work, but I want to be sure this is ...
- 133
0
votes
1
answer
598
views
How to conditionally return multiple different columns in sqlite?
I am trying to return multiple columns only if a condition matches, else, return something else. Using Sqlite3, my understanding is that the way to do this is using CASE, so something like this:
...
- 113