Nov 3, 2025
As an “SQLite consultant” for my local area of Google, I often have people come to me having written SQL like:
SELECT CAST(bar AS STRING) AS baz
FROM foo
and ask me “Why is baz always an integer?! Have I hit an SQLite bug?”.
I have to again reach for my list of “odd quirks that SQLite has that people don’t know about”. Because this is not a bug, at least according to the SQLite manual.
Instead, the correct way to write the above query is:
SELECT CAST(bar AS TEXT) AS baz
FROM foo
The reason for this? Quoting from “Determination of Column Affinity”
For tables not declared as STRICT, the affinity of a column is determined by the declared type of the column, according to the following rules in the …
Nov 3, 2025
As an “SQLite consultant” for my local area of Google, I often have people come to me having written SQL like:
SELECT CAST(bar AS STRING) AS baz
FROM foo
and ask me “Why is baz always an integer?! Have I hit an SQLite bug?”.
I have to again reach for my list of “odd quirks that SQLite has that people don’t know about”. Because this is not a bug, at least according to the SQLite manual.
Instead, the correct way to write the above query is:
SELECT CAST(bar AS TEXT) AS baz
FROM foo
The reason for this? Quoting from “Determination of Column Affinity”
For tables not declared as STRICT, the affinity of a column is determined by the declared type of the column, according to the following rules in the order shown:
- If the declared type contains the string “INT” then it is assigned INTEGER affinity.
- If the declared type of the column contains any of the strings “CHAR”, “CLOB”, or “TEXT” then that column has TEXT affinity. Notice that the type VARCHAR contains the string “CHAR” and is thus assigned TEXT affinity.
- If the declared type for a column contains the string “BLOB” or if no type is specified then the column has affinity BLOB.
- If the declared type for a column contains any of the strings “REAL”, “FLOA”, or “DOUB” then the column has REAL affinity.
Otherwise, the affinity is NUMERIC.
STRING does not match any of the numbered rules and so fallback to NUMERIC affinity which, in the general case, means integer.
Due to SQLite’s staunch stance of being “backwards compatible” there’s very little chance of this paper-cut ever going away. But at least next time someone comes to me with this issue, I’ll be able to link to this post instead of writing the same thing for the nth time :)
If you enjoyed this post, you can subscribe to my weekly roundup of recent posts, or follow via RSS.