How to select 1st, 3rd, 11th and nth row from a table? Answer If there is a primary key defined for the table that is an integer based data type–both MySQL and SQLite have auto_increment for example–then you can use: …where id is the auto_increment column. There’s very little detail to…
Tag: sqlite
Aggregating save()s in Django?
I’m using Django with an sqlite backend, and write performance is a problem. I may graduate to a “proper” db at some stage, but for the moment I’m stuck with sqlite. I think that my write performance problems are probably related to the fact that I’m creating a large number of ro…
How do I reference sqlite db column to use in update statement
I am trying to update a datetime column in an android sqlite db to use international date format (yyyy-mm-dd) instead of the current format (mm/dd/yyyy). I want to use the sqlite date() function to reformat the current value of the column. I thought it would be as simple as the following: but the above does n…
sqlite select with condition on date
I have an sqlite table with Date of Birth. I would like to execute a query to select those records where the age is more than 30. I have tried the following but it doesn’t work: Answer Some thing like this could be used: If you are using Sqlite V3.7.12 or greater Dont use date(dateofbirth) just use date…
Does SQLite support replication?
In an application which embeds SQLite3 and uses an in-memory database, is it possible to replicate the database between two running instances of the application? I could do this by hand with a homebrew protocol duplicating all my DB accesses, but it seems like something that should be done inside the DB layer…
How do I UPDATE a row in a table or INSERT it if it doesn’t exist?
I have the following table of counters: I would like to increment one of the counters, or set it to zero if the corresponding row doesn’t exist yet. Is there a way to do this without concurrency issues in standard SQL? The operation is sometimes part of a transaction, sometimes separate. The SQL must ru…
Drop all tables command
What is the command to drop all tables in SQLite? Similarly I’d like to drop all indexes. Answer
How to create timestamp column with default value ‘now’?
How to create a table with a timestamp column that defaults to DATETIME(‘now’)? Like this: This gives an error. Answer As of version 3.1.0 you can use CURRENT_TIMESTAMP with the DEFAULT clause: If the default value of a column is CURRENT_TIME, CURRENT_DATE or CURRENT_TIMESTAMP, then the value used…