Questions tagged [sqlite]

A popular open-source embeddable (i.e. non client-server) RDBMS implemented as a C library.

Filter by
Sorted by
Tagged with
-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 ...
Sarah Lewis's user avatar
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 ...
White Owl's user avatar
  • 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 ...
GRS's user avatar
  • 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 ...
tomsu's user avatar
  • 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('...
Steven's user avatar
  • 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 ...
mLstudent33's user avatar
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 ...
Harshal Patil's user avatar
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 ...
mvorisek's user avatar
  • 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 ...
surfmuggle's user avatar
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 ...
FranS's user avatar
  • 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 ...
Evan Carroll's user avatar
  • 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....
yossico's user avatar
  • 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, ...
Pierre's user avatar
  • 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 ...
Pierre's user avatar
  • 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 ...
Sean W.'s user avatar
  • 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 ...
Nikolay Yasinskiy's user avatar
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....
Nikolay Yasinskiy's user avatar
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 ...
user570286's user avatar
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 ...
Eriedor's user avatar
  • 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 ...
user41796's user avatar
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). ...
Toll's user avatar
  • 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....
Hem Bhagat's user avatar
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 ...
Manngo's user avatar
  • 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....
Stam Kapetanakis's user avatar
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. ...
Anti Atlas Dev's user avatar
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 ...
Anti Atlas Dev's user avatar
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 ...
CXY's user avatar
  • 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....
INTERNECIO's user avatar
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 ...
Nico's user avatar
  • 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 ...
flacer's user avatar
  • 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 ...
datguyray's user avatar
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, -...
Izzo's user avatar
  • 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 ...
climaxMa's user avatar
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 ...
CopOnTheRun's user avatar
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:...
Robin's user avatar
  • 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/...
Andrea Moro's user avatar
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, "...
mghee's user avatar
  • 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 ...
David's user avatar
  • 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 ...
Kurovsky's user avatar
  • 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 ...
db48x's user avatar
  • 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 ...
David E's user avatar
  • 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 ...
Kevin Filzwieser's user avatar
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(...
John's user avatar
  • 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: ...
Tobias Leupold's user avatar
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 ...
Angled's user avatar
  • 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 ...
Ginger's user avatar
  • 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), (...
adius's user avatar
  • 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 ...
Grymjack's user avatar
  • 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 ...
ggambetta's user avatar
  • 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: ...
Josh Brower's user avatar

1
2 3 4 5
14