Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 125 ยท Functions and Expressions

Types and CAST

Learn how SQLite stores values with storage classes and type affinity, check types with typeof(), convert them with CAST, and avoid implicit conversion bugs.

Every value in a database has a type: 42 is an integer, '42' is text, and 4.2 is a real (floating-point) number. Types decide how values sort, compare and combine. '10' < '9' is true for text but false for numbers, and that one difference is behind a surprising number of real-world bugs.

SQLite handles types differently from most databases. PostgreSQL, MySQL and SQL Server are statically typed: a column declared INTEGER holds only integers. SQLite is dynamically typed: the type belongs to each individual value, and a column's declared type is only a preference, called its affinity. Understanding that difference will save you from confusing results, both in SQLite and when you move between databases.

In this lesson you'll inspect types with typeof(), learn SQLite's five storage classes and affinity rules, convert values explicitly with CAST, and see where implicit conversions help and where they hurt. You'll also try STRICT tables, which make SQLite behave like a statically typed database.

The five storage classes

Every value SQLite stores belongs to one of five storage classes, and typeof() tells you which:

Storage class What it holds Example
integer whole numbers, up to 8 bytes 42, -7
real 8-byte floating-point numbers 4.2, 1e3
text strings, stored as UTF-8 '42', 'Lisbon'
blob raw bytes, stored exactly as given x'CAFE'
null the missing value NULL
SQL Checking types with typeof() ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Two results are worth noticing. 1e3 is a real, even though it equals the whole number 1000, because scientific notation always produces a real. And 3 > 2 is an integer: SQLite has no separate boolean type, so comparisons return 1 (true) or 0 (false), and the keywords TRUE and FALSE are just other ways to write 1 and 0.

The second query shows that added_on is text. As the Date and Time Functions lesson explained, SQLite stores dates as ISO text.

Type affinity: the column's preference

When you declare a column as INTEGER, SQLite gives it INTEGER affinity. When a value is inserted, SQLite tries to convert it to the preferred type. If the conversion works without losing information, the converted value is stored; if not, the value is stored as it is. So inserting '42' into an INTEGER column stores the integer 42, but inserting 'hello' stores the text 'hello'.

SQLite has five affinities, and it chooses one from the declared type name with a few simple rules, applied in this order:

Rule Affinity Example declarations
1. name contains INT INTEGER INTEGER, INT, BIGINT, TINYINT
2. contains CHAR, CLOB or TEXT TEXT TEXT, VARCHAR(255), NCHAR(10)
3. contains BLOB, or no type given BLOB BLOB, a column with no type
4. contains REAL, FLOA or DOUB REAL REAL, FLOAT, DOUBLE PRECISION
5. anything else NUMERIC NUMERIC, DECIMAL(10,2), BOOLEAN, DATE, STRING

This is why SQLite accepts table definitions written for other databases: VARCHAR(255) and DECIMAL(10,2) are valid, they just map to TEXT and NUMERIC affinity. But the details in parentheses are ignored: VARCHAR(5) happily stores a 13-character string.

SQL Affinity in action

Press Run (or Ctrl + Enter) to see the result here.

Read the results row by row:

  • In the first row, the text '42' became the integer 42 in as_integer and the real 42.0 in as_real, while the number 42 became the text '42' in as_text.
  • In the second row, 'hello' can't become a number, so both numeric columns store it as text. No error is raised.
  • as_varchar stored all 13 characters despite the (5).
  • as_string turned '0012' into the integer 12, losing the leading zeros. STRING doesn't contain CHAR or TEXT, so rule 5 gives it NUMERIC affinity. Always use TEXT for text columns in SQLite.
  • The column with no type kept each value exactly as inserted.

The Data Types lesson goes deeper into choosing column types.

Converting values with CAST

CAST(expression AS type) converts a value explicitly. It's standard SQL and works in every database, though the conversion rules differ. In SQLite, the target type follows the same affinity rules as column declarations:

SQL CAST conversions

Press Run (or Ctrl + Enter) to see the result here.

SQLite's CAST never fails. It reads as much of a number as it can from the start of the text and stops at the first character that doesn't fit:

  • '42abc' becomes 42, and 'abc' becomes 0.
  • '1e3' becomes 1 as an INTEGER (it stops at the e) but 1000 as a REAL. Cast to REAL first if text might use scientific notation.
  • Converting a real to an integer drops the fraction toward zero: 3.99 becomes 3 and -3.99 becomes -3. Use round() first if you want the nearest integer; see Numeric Functions.
  • NUMERIC picks an integer when the value is a whole number and a real otherwise.
  • NULL stays NULL whatever type you cast to.

A silent 0 is not "valid". Because CAST('abc' AS INTEGER) returns 0, bad data can quietly turn into plausible numbers. When you need to know whether text really is a number, check it first, for example WHERE trim(code) GLOB '[0-9]*' AND trim(code) NOT GLOB '*[^0-9]*' for whole numbers. In SQL Server, TRY_CAST returns NULL for bad input, and PostgreSQL raises an error.

Implicit conversions

Often SQLite converts values for you without a CAST. Arithmetic operators convert text to numbers using the same rules as CAST, and || converts numbers to text:

SQL
SELECT
  '10' + 5,        -- 15
  '3 apples' + 2,  -- 5
  'abc' * 1,       -- 0
  2 * '1.5',       -- 3.0
  '5' || 5;        -- '55'

Comparisons are trickier. When you compare two literal values, SQLite doesn't convert anything, and any number is considered smaller than any text. So 1 = '1' is false (0). When a column is involved, its affinity is applied to the other side first. That rule is reasonable, but it produces a famous surprise when numbers are stored in a TEXT column:

SQL Numbers stored as text

Press Run (or Ctrl + Enter) to see the result here.

Because code is TEXT, everything compares like dictionary words, character by character:

  • ORDER BY code gives 10, 100, 25, 9, because '1' < '2' < '9'.
  • WHERE code > 20 converts the 20 to the text '20' (TEXT affinity) and returns 9 and 25, not 25 and 100.
  • max(code) is '9', because '9' is the "largest" text.

Casting the column fixes every one of these. The real fix is to store numbers in a numeric column in the first place; casting in every query is a workaround for a design problem. On the other hand, values that only look like numbers, such as postal codes, phone numbers and IDs with leading zeros, belong in TEXT columns, where '00123' keeps its zeros.

STRICT tables

Since version 3.37, SQLite can enforce column types the way other databases do. Add STRICT after the closing parenthesis of CREATE TABLE. In a strict table, each column must use one of INTEGER, INT, REAL, TEXT, BLOB or ANY, and a value that can't be converted losslessly is rejected.

This example fails on purpose at the last insert:

SQL A STRICT table rejects bad values

Press Run (or Ctrl + Enter) to see the result here.

The text '40' is still accepted, because it converts cleanly to the integer 40. But 'plenty' stops the run with Error: cannot store TEXT value in INTEGER column stock_levels.quantity. For new SQLite projects, STRICT tables catch bugs early and make your schema behave like PostgreSQL's. CREATE TABLE covers the other table options.

Types in other databases

Statically typed databases check types when you write data and when you run queries, so many mistakes become errors instead of strange results:

Situation SQLite PostgreSQL MySQL SQL Server
'hello' into an integer column stored as text error error (strict mode) or 0 with a warning error
CAST('abc' AS INTEGER) 0 error 0 with a warning (AS SIGNED) error (TRY_CAST gives NULL)
CAST('42abc' AS INTEGER) 42 error 42 with a warning error
'10' + 5 15 15 (the literal is converted) 15 15
VARCHAR(5) with 13 characters stored error error (strict mode) error
Boolean type none (1/0) boolean BOOLEAN = TINYINT(1) bit
Exact decimals no numeric(10,2) DECIMAL(10,2) decimal(10,2)

The CAST target names also differ: MySQL uses SIGNED, UNSIGNED, CHAR and DECIMAL(p,s) instead of INTEGER and TEXT, and PostgreSQL offers the shorthand value::type:

SQL
-- PostgreSQL
SELECT '42'::integer, price::text, CAST('19.99' AS numeric(10, 2));

-- MySQL
SELECT CAST('42' AS SIGNED), CAST(price AS CHAR), CAST('19.99' AS DECIMAL(10, 2));

-- SQL Server
SELECT CAST('42' AS int), TRY_CAST('abc' AS int), CONVERT(varchar(10), price);

A query that relies on SQLite's forgiving conversions may fail on another database, so write explicit CASTs whenever you mix types. Your queries will be clearer and more portable.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does SELECT typeof('3.14'); return?

    The quotes make it a text value. Literals keep their own type; only column affinity or CAST converts them.
  2. 2. What does CAST('25kg' AS INTEGER) return in SQLite?

    SQLite reads the leading digits and stops at the first character that isn't part of a number, so the result is 25. PostgreSQL and SQL Server would raise an error.
  3. 3. A TEXT column holds '9', '25' and '100'. What does ORDER BY code return?

    Text sorts character by character, and '1' < '2' < '9'. Use ORDER BY CAST(code AS INTEGER) to sort numerically.
  4. 4. Which affinity does SQLite give a column declared as STRING?

    STRING doesn't contain INT, CHAR, CLOB, TEXT, BLOB, REAL, FLOA or DOUB, so the fallback rule gives it NUMERIC affinity, and '0012' would be stored as 12. Use TEXT instead.
  5. 5. What happens when you insert 'plenty' into an INTEGER column of a STRICT table?

    STRICT tables reject values that can't be converted losslessly to the column's type.

Summary

  • SQLite values have one of five storage classes (integer, real, text, blob, null); typeof() shows which.
  • A column's declared type gives it an affinity (INTEGER, TEXT, BLOB, REAL or NUMERIC) chosen by simple name rules; lengths like VARCHAR(5) are ignored.
  • Affinity converts inserted values when it can do so without loss, and stores them unchanged otherwise.
  • CAST(x AS type) converts explicitly; in SQLite it never fails, reading leading digits and returning 0 for non-numbers.
  • Booleans are the integers 1 and 0; comparing literals never converts, and a column's affinity applies to the value it's compared with.
  • Numbers stored as text sort and compare as text ('100' < '9'); store numbers in numeric columns.
  • STRICT tables (SQLite 3.37+) reject values of the wrong type, like PostgreSQL, MySQL and SQL Server do.

Next: Working with JSON, where you'll store, query and build JSON documents in SQL.