By default, the SQLite command line shell formats query output as a pipe-separated list, without column headers.
If you need to output the results using commas instead of the pipe-symbol, you can specify this with the .separator dot command.
If you need to format your results as string literals, see How to Enclose Strings in Single Quotes. Or if you need any strings to be enclosed in double quotes, see How to Format Results as CSV.
Continue readingBy default, when you connect to the SQLite command line shell and run a query, the result is returned as a pipe separated list.
You might have noticed that the results don’t include the column names, which can make your results confusing if you’re trying to read them, especially if the query returned many columns.
Fortunately, there’s an easy way to format the output so that it’s displayed as a column with column headers.
Continue readingSQLite provides us with the ability to run scripts directly from a file. This can be especially useful when you have a large script (such as creating a bunch of database tables and inserting data into those tables).
Below are five ways to run SQL scripts directly from a file in SQLite.
Continue readingIf you need a sample database to run some quick tests in SQLite, here are a couple of options.
Continue readingSQLite uses a different syntax for creating databases to what many other relational database management systems use.
Most of the popular relational database management systems such as MySQL, SQL Server, PostgreSQL, and so on, use the CREATE DATABASE statement to create a database.
When you create a database with SQLite however, you do so by simply providing a filename.
Continue readingTo create a table in SQLite, use the CREATE TABLE statement.
This statement accepts the table name, the column names and their definitions, as well as some other options.
Continue readingWhen you create a table in SQLite, you can create it as a permanent table or as a temporary table.
When you create a table in a database that you’ve created, that would be a permanent table. A temporary table is created in the temp database.
To create a temporary table, you use the same syntax as creating a regular table. The difference is that you use either the TEMP or TEMPORARY keyword. You can also (or alternatively) prefix the table name with temp, which indicates that it will be created in the temporary database.
When using SQLite, you can use the ATTACH DATABASE statement to add a database file to the current database connection.
When you do this, you attach a database file name and provide a name for the database. If the file exists it will be attached with your chosen name, otherwise it will be created and attached with your chosen name.
Continue readingHere are a few ways to check your version of SQLite.
Actually, you probably already saw which version you were using when you connected to SQLite.
In any case, if you found this page, maybe you need another method to check your SQLite version.
Continue readingIn PostgreSQL you can use the EXTRACT() function to get the month from a date.
You can also use the DATE_PART() function to do the same thing.