60 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 ...
miken32's user avatar
  • 708
54 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 ...
ThePhysicist's user avatar
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 ...
CL.'s user avatar
  • 5,213
20 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 ...
jersey bean's user avatar
19 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 ...
RDFozz's user avatar
  • 11.6k
18 votes

How to properly format sqlite shell output?

I always use .mode line which prints query results vertically, similar to MySQL's \G modifier.
Geoff_Clapp's 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 ...
joanolo's user avatar
  • 13.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 ...
Vérace's user avatar
  • 29.9k
14 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; ...
Andriy M's user avatar
  • 23k
13 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....
Andriy M's user avatar
  • 23k
13 votes
Accepted

Unused columns in WHERE clause

Both queries are ridiculously complicated. Use DELETE FROM "Foo" WHERE "CreatedAt" < current_timestamp;
Laurenz Albe's user avatar
  • 51.7k
13 votes
Accepted

SQLite - How does Count work without GROUP BY?

SQLite does not adhere to the standard in this regard. See https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause SQLite ...
Lennart - Slava Ukraini's user avatar
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 ...
screwtop's user avatar
  • 213
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? ...
ypercubeᵀᴹ's user avatar
10 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 ...
Neza's user avatar
  • 109
10 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 ...
Matjaž's user avatar
  • 363
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 ...
StudentsTea's 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 ...
tanius's user avatar
  • 261
9 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 ...
shr's user avatar
  • 191
9 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 ....
Dario's user avatar
  • 748
9 votes
Accepted

Multiple concurrent connections to same large SQLite file on a network share

Concurrency is not a problem, as long as you only read. SQLite doesn't allow concurrency on writes, because it never was designed for that. I don't know the internals of the python driver and ...
jynus's user avatar
  • 14.9k
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 ...
Michael Green's user avatar
8 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 ...
humbads's user avatar
  • 181
8 votes

How do I specify a timeout in sqlite3 from the command-line?

Just use: $ sqlite3 -cmd ".timeout 1000" outgoing.db "SELECT * FROM edges"
luzik's user avatar
  • 181
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 ...
CL.'s user avatar
  • 5,213
8 votes

SQLite, ASCII A-Z Check Constraint

The LIKE operator in SQL does not work like regular expressions. The pattern matches allowed by this operator are very simple, compared to the powerful regexp. Actually, you have two metacharacters: % ...
joanolo's user avatar
  • 13.4k
8 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. ...
ypercubeᵀᴹ's user avatar
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" ...
Victor Castillo Torres's user avatar
7 votes

configure: error: SQLite3 library not found

I tried both, sudo apt-get install sqlite and building from source but problem persists. Then I tried the following command and it worked. May be it'll help someone else too. sudo apt-get install ...
MD Ruhul Amin's 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 ...
Thomas Ahle's user avatar

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