DuckDB is a database management system tailored for analytical tasks. Its command line interface (CLI) provides a convenient way to interact with databases and execute queries. The CLI provides us with the ability to configure the output mode for query results, offering flexibility in how data is displayed or exported.
This article provides a quick overview of how to change the output mode in the DuckDB CLI.
Continue readingDuckDB is a lightweight, high-performance database system designed for analytical workloads. As with any database management system (DBMS), one of the first things we normally want to do when we launch it is to create or open a database.
When it comes to creating a database, you can’t create a database in DuckDB by using the SQL CREATE DATABASE statement (unless you’re using a tool that allows you to do so). DuckDB works differently.
In this article, we look at various options for creating a database in DuckDB.
Continue readingDuckDB is a high-performance in-process analytical database management system, often referred to as the “SQLite for analytics.” Its Command Line Interface (CLI) is a useful tool for executing SQL queries, managing databases, and exploring DuckDB’s features.
This article provides a comprehensive guide to the basics of using DuckDB’s CLI, helping beginners and advanced users alike understand how to leverage it effectively.
Continue readingIf you’re getting an error that reads “AUTOINCREMENT not allowed on WITHOUT ROWID tables” in SQLite, it appears that you’re trying to define a column as an AUTOINCREMENT in a WITHOUT ROWID table.
SQLite doesn’t allow us to create AUTOINCREMENT columns on WITHOUT ROWID tables.
To address this issue, either remove the AUTOINCREMENT attribute or remove the WITHOUT ROWID from the table definition.
If you’re getting an error that reads something like “unknown datatype for (columnname): “DATE”” in SQLite, it appears that you’re trying to define a column as a DATE type in a strict table.
SQLite doesn’t support the DATE type, however, this error should only occur on strict tables (i.e. a table defined as STRICT).
To fix this issue, either use a supported data type or make the table a regular (non-strict) table.
Continue readingIn most relational database management systems (RDBMSs) the PRIMARY KEY is used to define the unique row identifier for a table. But in SQLite, not all primary keys are handled the same way when it comes to indexing.
Depending on how the primary key is defined in a table, it may or may not show up in the list of indexes returned by the PRAGMA index_list() command. In particular, when the primary key is an INTEGER PRIMARY KEY, SQLite doesn’t explicitly create a separate index for it.
This article will explain why this happens and provide examples with different types of primary key definitions.
Continue readingDeleting rows in a SQL database can sometimes be a nerve-racking experience. What if you’re deleting the wrong rows? Or what if the business later tells you they want their data back?
Fortunately SQL Server provides us with an easy way to essentially “back up” any rows affected by a DELETE operation to a table.
This article looks at using the OUTPUT ... INTO clause to save a copy of deleted rows to another table.
Perhaps you’re familiar with MySQL’s SERIAL type, which is not actually a data type, but a shortcut for BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE.
One restriction of SERIAL is that it forces us to use the BIGINT data type, which is fine if you think you’ll need the extra space. But what if you want the same attributes for a smaller integer type, like INT or MEDIUMINT for example?
Enter SERIAL DEFAULT VALUE.
In most DBMSs (including SQLite), we can create indexes to improve query performance by allowing faster access to data. However, you might occasionally need to rename an index for whatever reason, be it for clarity, consistency, organizational purposes, or some other reason.
Continue readingIf you’ve got an index in a SQLite database that you no longer need, you should probably get rid of it. Keeping unused indexes can be a drag on database performance, especially if your data is being regularly updated.
This article shows you how to delete an index in SQLite.
Continue reading