Category: DBMS

Database Management Systems

What is DuckDB?

Posted on by Ian

This article takes a look at DuckDB, a modern database management system (DBMS) designed specifically for data analysis and processing.

Often described as the “SQLite for analytics,” DuckDB is lightweight, embeddable, and highly efficient, making it a popular choice for those working with structured data.

Whether you’re considering DuckDB for your projects or simply curious about its capabilities, this article will give you a basic overview of what it is and how it fits into the data analytics landscape.

Continue reading →

Fix Error “AUTOINCREMENT not allowed on WITHOUT ROWID tables” in SQLite

Posted on by Ian

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

Continue reading →

An Introduction to SQLite’s LIKELY() Function

Posted on by Ian

SQLite provides a range of tools to help optimize query execution. One of these is the likely() function, which helps developers guide the query planner by hinting at the probability of certain conditions being true.

In this article, we’ll look at the likely() function, understanding how it works, its syntax, some use cases, and its impact on query performance.

Continue reading →

An Overview of the LIKELIHOOD() Function in SQLite

Posted on by Ian

SQLite is a lightweight, serverless database engine widely used for its simplicity and flexibility. Among its many functions, the likelihood() function is a handy option that allows you to influence query planning by providing hints about the probability of certain conditions being true.

This article explores the details of the likelihood() function, its syntax, use cases, and a practical example to demonstrate its application.

Continue reading →

Fix Error: unknown datatype for (columnname): “DATE” in SQLite

Posted on by Ian

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

Data Types that SQLite Allows for Strict Tables

Posted on by Ian

SQLite is a lightweight, self-contained SQL database engine known for its simplicity and versatility. In version 3.37.0, SQLite introduced strict tables, offering stricter type enforcement compared to its regular tables.

This feature allows developers to define tables with precise data types, ensuring better data consistency.

In this article, we’ll look at the six supported data types for strict tables—INT, INTEGER, REAL, TEXT, BLOB, and ANY—and provide simple examples to illustrate their usage.

Continue reading →

Why the Primary Key Might Not Appear in PRAGMA index_list() in SQLite

Posted on by Ian

In 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 reading →