Hot answers tagged

52 votes

Is it possible to store and query JSON in SQLite?

SQLite 3.9 introduced a new extension (JSON1) that allows you to easily work with JSON data . Also, it introduced support for indexes on expressions, which (in my understanding) should allow you to ...
user avatar
42 votes

How to properly format sqlite shell output?

All of the answers provide settings you can type at the SQLite console or via CLI, but nobody mentions that these settings can be put into an RC file to avoid having to type them all the time. Save ...
user avatar
  • 527
22 votes
Accepted

Why is this sqlite query much slower when I index the columns?

In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table. If there is an index, the database can ...
user avatar
  • 4,795
17 votes

How to properly format sqlite shell output?

For those that are interested in getting the same results, except running sqlite from command line. I found that the following doesn't work: $ sqlite3 <dbfile> ".headers on;.mode column;select ...
user avatar
16 votes

How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?

I assume that your web interface lets you issue SQL commands. If so, you can use: SELECT version(); PostgreSQL If you are on a PostgreSQL database, you get a response similar to PostgreSQL 9.6.1 ...
user avatar
  • 12.4k
15 votes
Accepted

Text string stored in SQLite Integer column?

This is a well known "quirk" of SQLite. SQLite uses what it calls a dynamic typing system, which ultimately means that you can store text in integer fields - in Oracle, SQL Server and all the other ...
user avatar
15 votes
Accepted

SQLite3: Remove duplicates

If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table. Something like this: DELETE FROM ...
user avatar
  • 3,301
15 votes

Can a database table not have a primary key?

Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform. Should you create a ...
user avatar
  • 11.4k
15 votes
Accepted

Is a single-table relational database similar to a simple NoSQL database?

No - a single table relational database is more akin to a spreadsheet than a NoSQL database. NoSQL is not about having no schema, it's more about having a flexible schema! Wikipedia puts it very ...
user avatar
13 votes
Accepted

Unused columns in WHERE clause

Both queries are ridiculously complicated. Use DELETE FROM "Foo" WHERE "CreatedAt" < current_timestamp;
user avatar
  • 32.1k
12 votes

How to properly format sqlite shell output?

I always use .mode line which prints query results vertically, similar to MySQL's \G modifier.
user avatar
12 votes

Converting outer apply with Correlated subquery in SQL Server to SQLite

SQLite does not support lateral or correlated joins. You can work around that using a join condition. For example: create table t1 (id int, name text); create table t2 (id int, t1id int references ...
user avatar
  • 3,335
12 votes
Accepted

How to UPDATE a SQLITE Column with an INNER JOIN on TWO FIELDS

In order to implement the join on two columns simultaneously, you can use an EXISTS predicate: UPDATE Table_1 SET Field3 = (SELECT Field3 FROM Table_2 WHERE (Table_1....
user avatar
  • 21.1k
11 votes
Accepted

select all rows with a minimum value

As you have seen, a simple GROUP BY will not work because it would return only one record per group. Your join works fine. For a large table, it will be efficient only if there is an index on the ...
user avatar
  • 4,795
11 votes
Accepted

SELECT query using an array of tuples

There are various ways to obtain multiple IDs with a single query in your situation. First, you can just take the series of single SELECTs: SELECT ID FROM FOO WHERE col1=1 AND col2=2 AND col3=3; ...
user avatar
  • 21.1k
11 votes

SQLite: What is the use of specifying data types?

Further to Vérace’s answer: While SQLite doesn’t enforce storage classes as data types, you can apply stricter type-checking using typeof() in a constraint, e.g. Some_Quantity real check ...
user avatar
  • 211
11 votes
Accepted

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

Do all the famous Relational DBMSes like PostgreSQL, Oracle, SQL server, SQLite, store it the same way? by making a clustered Index (b+tree) based on primary key and store data on the leafs of tree? ...
user avatar
10 votes
Accepted

Scripting SQLite with dot commands

I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately. The gist of it? Put your ...
user avatar
10 votes

How to reduce size of a large SQLite database with series data

There are some ways. Here are two that seem appropriate for your case: WITHOUT ROWID tables The ROWID is an internal primary key that SQLite tables have by default, taking 64 bit per record. Since you ...
user avatar
  • 211
9 votes

Is it possible to store and query JSON in SQLite?

PostgreSQL has some nice features for JSON storage. You can select the JSON values by doing SELECT DISTINCT FROM TABLE WHERE foo->title=test AND id=42 You can also index the specific keys on the ...
user avatar
  • 99
9 votes

Is a single-table relational database similar to a simple NoSQL database?

Yes, you can use a single-table relational database as if it were a NoSQL database. You can also use a spoon to paint your house. You have to use a tool the way it was intended else you're fighting ...
user avatar
8 votes

Directly import a csv gzip'ed file into SQLite 3

I found that sqlite3 custom init script can have meta-command as well as SQL statement: #!/bin/sh commandfile=$(mktemp) # create temporary init script cat <<EOF > $commandfile .mode csv ...
user avatar
  • 181
8 votes
Accepted

How to properly align columns in SQLite?

With .mode tab the output is not guaranteed to be aligned because af the nature of the TAB characters. On the other hand .mode column should be working fine. Since it looks like empty fields for NULL ...
user avatar
  • 343
8 votes
Accepted

Sqlite Create Table with Check column from other table

Looking up a value from another table would require a subquery, but the documentation says: The expression of a CHECK constraint may not contain a subquery. There is no such restriction on a ...
user avatar
  • 4,795
8 votes
Accepted

How to read metadata from Sqlite database

The equivalent of, for instance, MySQL describe t1; is PRAGMA TABLE_INFO(t1); and SELECT * FROM sqlite_master WHERE tbl_name = 't1'; is also useful. Most of the time, however, I simply use ....
user avatar
  • 708
8 votes
Accepted

No such column when column exists

The problem was that I had an space at the end of the name of the columns, solved the problem by deleting such spaces. Before: CREATE TABLE IF NOT EXISTS "trackedinfo" ( "id" ...
user avatar
7 votes

Is it possible to store and query JSON in SQLite?

The existing answers for this deal with actually storing the OP's data as JSON (which may have been a better solution to his underlying problem). However, the actual question was how to get find ...
user avatar
7 votes

Directly import a csv gzip'ed file into SQLite 3

If you run man sqlite3, you can find command line parameters -csv and -separator. So you can do something like this: cat mycsvfile.csv | sqlite3 -csv -separator ';' mydb.db '.import /dev/stdin ...
user avatar
  • 171
7 votes

How to check what Database Engine is installed on the DataBase server that I have acces to run queries on?

This online SQL editor uses the Web SQL Database, meaning SQL embedded in the browser. It's easy to recognize if you look at their JS source code at http://www.w3schools.com/w3Database.js and compare ...
user avatar
7 votes

Primary key guarantees: duplicates and nullity

Oracle allows primary keys to have duplicate and null values. Not really. What you have managed to create - after a series of complicated statements - is an enabled but not validated constraint. ...
user avatar

Only top scored, non community-wiki answers of a minimum length are eligible