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 readingSQLite 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.
The ACOSH() function in SQLite calculates the inverse hyperbolic cosine of a given number. The inverse hyperbolic cosine of a number is the value whose hyperbolic cosine equals that number.
The COSH() function in SQLite calculates the hyperbolic cosine of a number, which is similar to the regular cosine function, but for hyperbolic geometry.
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 readingSQLite is widely known for its simplicity, flexibility, and lightweight architecture. One feature that sets it apart from most other SQL databases is its dynamic typing system, which allows columns in a table to store data of any type, regardless of their declared type.
While some developers welcome this departure from the traditional SQL approach, others find it extremely problematic, due to its non-enforcement of data types, which could potentially lead to data integrity issues.
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.
In SQLite, the SQRT() function calculates the square root of a number. It returns the value which, when multiplied by itself, equals the input number.
Note that SQLite returns NULL if you try to calculate the square root of a negative number, NULL, or a value of the wrong type.
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.
The ASINH() function in SQLite calculates the inverse hyperbolic sine of a given numeric value. The result is the value whose hyperbolic sine is the specified number.