Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [sqlite]

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

0
votes
1answer
31 views

How to SELECT last row of every hour?

I have a table (in database) with one minute data in each row. Like this: Date_time Value 2018-11-01 10:58:00 189.0 2018-11-01 10:59:00 189.0 2018-11-01 11:00:00 189.19 2018-11-01 11:...
0
votes
0answers
28 views

How to sort rows in table using SQL? [closed]

I have table with 3 columns: "FirstCol", "SecondCol" and "ThirdCol". I need to sort table by "FirstCol". I know that I can sort table using SELECT: SELECT * FROM `TableName` ORDER BY `FirstCol` DESC ...
1
vote
1answer
26 views

How to change date format using SQL?

I have column with strings of dates with format: "dd.mm.yyyy". I need to convert the date format contained in the strings to ISO8601 format: "YYYY-MM-DD" for the whole column.
1
vote
1answer
22 views

Constraint via Foreign Key

I have the following schema in SQLite: CREATE TABLE calendars ( id INTEGER PRIMARY KEY, title TEXT NOT NULL ); CREATE TABLE entry_types ( id INTEGER PRIMARY ...
0
votes
1answer
23 views

How to import SQL file which has no N prefix

I have a large .sql file in Persian Language. The file is exported from sqlite db browser and has no N prefix for unicode fields. How can I reuse it in Sql Server? This is sample of insert query in ...
0
votes
0answers
27 views

SQLITE PHP Like Query wont return results on all rows

I have created a SQLite query in PHP which queries user input and checks it against the database, however when I run the query it just displays everything in the database. and if I was trying to ...
0
votes
1answer
42 views

Relation between Faculty, Student and Course tables [closed]

What should be the relation between following tables. Confuse whether i create an enroll table with student and course or courses_assigned table using faculty and course and then create an enroll ...
0
votes
1answer
44 views

SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause?

In SQLite's query planners is it generally faster to do this kind of thing? SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha) ... or ... SELECT ...
0
votes
1answer
21 views

SQLite how to store translations

Im looking for advise because my experience is not really good in SQLite. My table look like this: CREATE TABLE IF NOT EXISTS Route ( id integer PRIMARY KEY UNIQUE AUTOINCREMENT NOT NULL, name ...
0
votes
0answers
53 views

Slow insert on very large tables, (even in transactions)

I have a table with about 50 million rows, the table itself is fairly simple CREATE TABLE Word (id integer PRIMARY KEY, word TEXT UNIQUE) I then do an insert INSERT OR IGNORE INTO Word (word) ...
3
votes
3answers
450 views

Do all Relational DBMSes store table tuples in a clustered index based on primary key by default?

So i was reading MySQL innoDB, which apparently stores table data by default on a clustered Index (b+tree) based on primary key, and the tuples are in the leafs of that b+tree https://blog.jcole.us/...
1
vote
0answers
23 views

How get process that is locking Database on SQLite?

I'm developing a iOS app using SQLite. I've got a class to manage SQL's operations, basically read, write and update. In many cases I get a SQLITE_BUSY error, so I want know which process is using the ...
0
votes
2answers
24 views

Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do?

I've written an API for a client that accepts orders from clients and converts them into print jobs that go to an HTTP bridge into the company's RIP software. On the backend, the bridge is reading and ...
1
vote
1answer
48 views

Retrieving Parent Hierarchy Entity

I have the following table structure: CREATE TABLE "Material" ( "MaterialId" TEXT NOT NULL CONSTRAINT "PK_Material" PRIMARY KEY, "Content" TEXT NULL, "Title" TEXT NULL, "Paid" INTEGER ...
0
votes
2answers
26 views

in sqlite how to check the table is empty or not?

I need the check the table is empty or not employee table contains columns are id name id name 1 john 2 hunter 3 smith 4 will
1
vote
1answer
49 views

Commit() takes 50 seconds after inserting about 100000 rows

I have a sqlite database that is, (I think), fairly small. I has about 1 million rows in a couple of table and the total space on disk is about 150 Mb. I then open a transaction and insert about ...
2
votes
1answer
57 views

Why does a SELECT with no WHERE cause a full table scan, (or does it)?

I have the following table with ~2000000 rows CREATE TABLE TableA (idA integer PRIMARY KEY, idB TEXT UNIQUE) From time to time, I need to process each rows, the order does not mater. So I process ...
2
votes
4answers
170 views

What is a DBA's definition of “batch”?

I've heard it being used in relation to SQLite sometimes but never completely understood what it means.
1
vote
2answers
113 views

SQL: How to Select Multiple Row Data for the Same ID?

My data is like this: ID col1 0000001 00020 0000001 00023 0000001 00019 0000001 00010 0000003 00030 0000003 00020 I want to find which IDs have a combination of 2 values in the column 'col1' (...
1
vote
3answers
88 views

How to increase column limit of a table in SQLite?

SQLite: How can we configure the maximum number of columns allowed in a table?
1
vote
2answers
58 views

Another newbie query on 2 tables

I'm having trouble working out this apparently simple query, and would appreciate any suggestions for a solution using Sqlite. I have two tables t1 and t2 : Eg., Table1 : -------- Key Data A ...
1
vote
0answers
23 views

SQLite database: many-to-many optimization (simple but large example) without IDs available?

I'm a beginner looking for people who can think through these things more easily to see where I've messed up. I have a many-to-many table that I am trying to optimize for speed of creation, speed of ...
1
vote
0answers
23 views

Getting text surround search queries?

I'm looking for a way to retrieve search results from an sqlite3 db, including some text around the query, to show context in the search results. I'm not finding any examples of this. Is this ...
0
votes
1answer
36 views

Python SQLite3 horizontal partitioning by string column

I have a 30 GB SQLite 3 database with a table containing 25 million rows. I want to group the table by a string column and create a new table for every group of records, named after the string and ...
1
vote
1answer
40 views

SQL squash rows returned when joining several tables

I have a database which has a table for the main items, lets call it A with primary key 'id'. Then there are a number of tables (B, C...), each corresponding to some attribute that each item can have ...
1
vote
1answer
23 views

Replacing one row with multiple similar rows? [closed]

I'm trying to update an inventory in my database. I have an item number, location, and quantity that I would like to expand to an item number, location, and serial number. Example: Item Number, Item ...
-1
votes
1answer
22 views

How to group rows

I don't understand how to retrieve the results from the following tables depending on some prerequisites. I have 2 tables Table "project" | id | category_id | count_view | -- | ------------| ------...
2
votes
1answer
69 views

Directly go to Hadoop or use SQLite/Postgres as a stepping stone towards Hadoop/Spark? [closed]

In our organization, some people are working on setting up Hadoop with a lot of security restrictions etc. By the rate of progress, this seems complex, especially considering security restrictions, ...
0
votes
1answer
365 views

How can I disable database lock in sqlite with python?

I made a application in python and it should read and write simultaneously in multiple instances(multi CMD widows)(multi-threading). each thread is configured independently so it will not change the ...
2
votes
2answers
42 views

Any benefit in running ANALYZE after bulk insert in table with only PRIMARY KEY index in SQLite?

If I create a table in SQLite that has only the PRIMARY KEY index, is there any role for running ANALYZE after a bulk insert, or will that key automatically have been kept up-to-date by necessity? In ...
3
votes
1answer
83 views

How large is SQLites minimum data storage overhead and what generates them?

Motivation: In my company there exists the (probably strange) idea to store pure time series data in a normal SQL table (instead of using BLOBs or just binary files which would probably be a better ...
1
vote
1answer
27 views

SQLite DB from file vs “service”

I apologize if this question has been asked before, I am not exactly sure how to phrase it. I am currently creating a SQLite database for use in a NodeJS application. The DB will need to be read from ...
0
votes
1answer
70 views

SQLite database becomes corrupt

I created a single table SQLite db from a csv file using python. I was able to open, browse and query the database using DB Browser for SQLite (browser) immediately after creating it. However, when I ...
1
vote
1answer
57 views

Updating table schema with correct datatype in sqlite

I know that importing from CSV into SQLite treats all fields as text. According to How to import csv file to sqlite with correct data types, one can use UPDATE MyTable SET MyColumn = CAST(MyColumn ...
0
votes
1answer
123 views

Is there a way in sqlite3 to set .width of all columns to be equal to the width of the column header?

I am using: SQLite version 3.21.0 I look for away to prettify my SQLite SELECT shell output (to make it more human readable, in essence). This answer helped me a lot. Though, now I am wondering if ...
0
votes
1answer
18 views

Find rows that have the same values in another column

I have the following table. User Permission John 1 Mary 1 Anne 1 Joe 2 Marie 2 Nick 3 I want to select and group users with the same permission number. Is this actually possible? I have ...
1
vote
3answers
42 views

What's the correct syntax for this Postgres query in MySQL?

I have a query which works well in our PostgreSQL db, but we now need to run our app on a MySQL DB as well. The query takes an array of IDs and positions, and updates each record with given ID with ...
0
votes
1answer
75 views

Open an existing sqlite database by sqlite3 on linux machine

I'm very new to databases and SQL, but have to perform select command on already existing sqlite database. I have: globally installed SQLite version 3.6.20 on the cluster file GBSV2.db I tried the ...
1
vote
1answer
146 views

SQLite3 doesn't have concat_ws, looking for workaround

I'm trying to concatenate some columns and I would like to use space as my output string delimiter. Basically concat_ws would be a perfect fit, but it doesn't exist for SQLite3. I'm inside a ...
0
votes
1answer
42 views

Create rows groups by using subgroups limits

I had originally posted this in SO but reasoned that it would be more appropriate here. I have a view in an sqlite database such as this: SQL Fiddle (http://www.sqlfiddle.com/#!5/ae95b/1) This ...
0
votes
1answer
139 views

Select between multiple ranges

Sorry for probably stupid question, but I've not found answer for it. I am android programmer and use SQL Lite for data storing. Now I faced with the problem - I need SELECT set of entities by ...
0
votes
0answers
26 views

Exclude lowest ranking team and repeat the process

The following are the first few rows of a SQLite table which is from a league-based championship: +---------+-------+-------------+---------------+----------+---------------+ | EventID | Round | ...
0
votes
1answer
79 views

Take a Column, Split it into New Columns

Example table: ID CB CB2 CB3 CB4 ---- -------- ---- ---- ---- 1 亀 龜 龜 2 竜 龒 Note: Each character is separated by a space. Note: The number of characters in CB ...
2
votes
1answer
81 views

Compare Two Columns, Remove Duplicate Characters

Example table: ID CA CB ------ ------ ----- 1 龍 龍 竜 龒 2 齒 歯 齒 3 黽 黽 I would like to modify column CB so that it doesn't contain any of the ...
1
vote
1answer
95 views

Creating a database structure for a quiz

My app has this menu: TestMode - make a quiz with 26 rndom questions from the database with a timer of 30 minutes. LearningMode - make a quiz with all the questions from my db, displaying the correct ...
0
votes
1answer
22 views

Designing a database for Reading Comprehension

So I am building an RC app that will require me to fetch data. I have data in Excel sheet and I would like to load the data in sqlite database. But I am having trouble designing the db. A little ...
0
votes
0answers
59 views

iOS SMS.db - limits of vacuuming

Considering this is an sql question, I thought I'd have more luck here rather than in the Apple section. A bit of background: iOS SMS.db is a sqlite db that, as the name suggests, holds messages (SMS ...
0
votes
0answers
34 views

Which database is as easy to set up and run as SQLite?

My project has currently 2 backends: plain-file & SQLite3. It is a background shell script that receives logs from foreground shell instance. Adding sqlite3 support was very easy. The cli tool ...
0
votes
0answers
35 views

I have a model of a table, and would like to keep database in sync, what is best method?

The model is an ordered (using additional array) hash table, with keys being columns' names, and values being columns' types. To visualize in an abstract programming language: MyHashTable[2] = ( "...
0
votes
1answer
20 views

Join with sqlite on table in android

Given a table like: object subject ID parentID ------ ------- ----- -------- A NULL 1 0 B NULL 2 0 C NULL 3 ...