Hot answers tagged

130

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


42

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.


33

You mostly answered the question yourself already. I have a few morsels to add: In PostgreSQL (and other RDBMS that support the boolean type) you can use the boolean result of the test directly. Cast it to integer and SUM(): SUM((amount > 100)::int)) Or use it in a NULLIF() expression and COUNT(): COUNT(NULLIF(amount > 100, FALSE)) Or with a simple ...


17

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


16

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

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

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


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


12

(reposting swasheck's comment as reply) sqlite3 database1.db > ATTACH DATABASE 'database2.db' AS database2; > INSERT INTO mytable SELECT * FROM database2.mytable;


11

This is my test on SQL Server 2012 RTM. if object_id('tempdb..#temp1') is not null drop table #temp1; if object_id('tempdb..#timer') is not null drop table #timer; if object_id('tempdb..#bigtimer') is not null drop table #bigtimer; GO select a.* into #temp1 from master..spt_values a join master..spt_values b on b.type='p' and b.number < 1000; alter ...


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

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

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

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

Yes. From the FAQ: Can multiple applications or multiple instances of the same application access a single database file at the same time? Multiple processes can have the same database open at the same time. Multiple processes can be doing a SELECT at the same time. But only one process can be making changes to the database at any moment in ...


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

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

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


9

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


9

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.


8

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


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

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


8

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

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


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

In theory you should know where the file came from and ask the person who gave it to you. A file extension could mean anything. In the old days, .db3 extension used to be for dBase III data files. It also could be for SQLLite binary dump file. See for example Importing SQLite db3 files. If you have problems, it may be attributed to many factors, version ...


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