Hot answers tagged

52

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 define indexes on your JSON data as well.


37

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 this as ~/.sqliterc: .mode column .headers on .separator ROW "\n" .nullvalue NULL Note I've also added a placeholder for null values, instead of the default ...


20

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 look up any matches in the index quickly, and then go to the corresponding table row to get the values of any other columns that are needed. In this case, there ...


16

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 * from MyTable" Error: mode should be one of: ascii column csv html insert line list tabs tcl Instead, you have to use the options -column and -header with ...


15

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 on x86_64-apple-darwin14.5.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit MySQL If you are on a MySQL database, the answer looks like 5.7.12-...


15

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 well when it says NoSQL: provides a mechanism for storage and retrieval of data that is modeled in means other than the tabular relations used in relational ...


14

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 database table without a primary key? No. Every table should have some column (or set of columns) that uniquely identifies one and only one row. It makes it ...


13

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 big hitters in the database world, attempts to do this will fail - not with SQLite. Take a look here: SQLite uses a more general dynamic type system. In ...


13

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 sms WHERE rowid NOT IN (SELECT min(rowid) FROM sms GROUP BY address, body); should work to get you the rows with the minimum rowid, which will be the first ...


12

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 t1(id), name text); insert into t1 values (1, 'a'), (2, 'b'), (3, 'c'); insert into t2 values (1, 1, 'a1'), (2, 1, 'a2'), (3, 2, 'b1'); Now we'd like to look ...


12

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.Field1 = Table_2.Field1) AND (Table_1.Field2 = Table_2.Field2)) WHERE EXISTS (SELECT * FROM Table_2 WHERE (Table_1....


11

That page you linked, besides being quite old, talks about accesses from the same process through the same database connection (or through multipe connections in shared cache mode, which you should not use). When not in WAL mode, multiple connections can read from the same database, but a writing transaction is exclusive, i.e., no other readers or writers ...


11

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 join columns (num and text). Alternatively, you could use a correlated subquery: SELECT * FROM t WHERE num = (SELECT MIN(num) FROM t AS t2 ...


11

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; SELECT ID FROM FOO WHERE col1=4 AND col2=5 AND col3=6; SELECT ID FROM FOO WHERE col1=7 AND col2=8 AND col3=9; and combine them with UNION ALL: SELECT ID FROM ...


11

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? No, not all. Lets take them one by one: MySQL. MySQL has several "engines" and depending on what engine a table is defined to use, the storage is: InnoDB: ...


10

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


10

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 JSON object if you use the jsonb type.


9

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 commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script. First Method: sqlite3 database.db &...


9

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 (typeof(Some_Quantity) = 'real') You do lose automatic type coercion, e.g. it would prevent the values '1' or 1 from being stored in a real column (you would ...


9

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 were talking about a UNIQUE constraint for the datetime column, this column seems a good candidate for the primary key (which always has a unique constraint ...


9

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 both the tool and the problem you're trying to use the tool to solve. If you want normalisation and the absence of data anomalies it brings you're likely to have ...


8

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 tablename .import /dev/stdin tablename EOF # import bzip2 -d -c huge_compressed.csv.bz2 | sqlite3 --init $commandfile dbname.db


8

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 .schema t1 which outputs the SQL definition of the table or view, even with the original comments (exactly the same as SELECT sql FROM sqlite_master WHERE..., with ...


7

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 mycsvtable' SQLite will automatically create the table if it does not exist, using the first line as the column names. I tested this and it worked like a charm. ...


7

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 trigger's WHERE clause: CREATE TRIGGER employee_salary_check BEFORE INSERT ON employee WHEN NEW.salary < (SELECT min FROM position WHERE id_position = NEW....


7

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 the API calls to the W3C spec of Web SQL, for instance how they open the database: w3Database = window.openDatabase('W3SchoolsDemoDatabase', '1.0', '...


7

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. Which means that Oracle will check inserts and updates (for uniqueness) but there may be left existing duplicates. So it is a PK only in name. It's a not-validated ...


7

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" INTEGER PRIMARY KEY, "date_time_start" TEXT, "date_time_end" TEXT, "tracked_name" TEXT, "tracked_origin" TEXT, "...


6

You can do this by using an init file. init.sql (note that the timeout value is in milliseconds - 1 is rather short): .timeout 1000 At the prompt: $ sqlite3 -init init.sql outgoing.db "select * from edges where worker is not null;" Loading resources from init.sql # 1 second pause Error: database is locked With some shells (on Linux at least, not very ...


6

Most of what these pragmas do for SQLite can be best accomplished by using an UNLOGGED table or a TEMPORARY table. This also makes it very obvious that these are ephemeral tables, that aren't crash or restart safe. It also highlights that there are very real tradeoffs for blazing speed versus data durability. Going off of the PRAGMA documentation for ...


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