Hot answers tagged

145

For "human readable" output, you can use column mode, and turn header output on. That will get you something similar to the sqlplus output in your examples: sqlite> select * from foo; 234|kshitiz|dba.se sqlite> .mode column sqlite> select * from foo; 234 kshitiz dba.se sqlite> .headers on sqlite> select * from foo; bar ...


47

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.


29

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 ...


19

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 ...


15

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-...


14

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 ...


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 ...


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

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 ...


11

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

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 ...


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

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 ...


10

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 ...


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.


10

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 ...


9

The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply: SELECT p.* FROM products AS p INNER JOIN tag_ties AS tt ON tt.ref_id = p.id AND tt.tag_id IN (10, 11, 12) GROUP BY p.id HAVING COUNT(p.id)=3 Extending this idea, we can also query based on the tag ...


9

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


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 ...


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

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 &...


8

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 ...


7

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS). CREATE TABLE AS allows you to create a table from the resultset of a query. For example: CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS ); You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows ...


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

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

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 ...


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