Data Types

Every column has a data type that decides what it can store. Choosing the right types keeps data valid and queries fast.

Common types

Money: use exact decimals

Approximate types (REAL, FLOAT) can't represent many decimal values exactly:

SELECT 0.1 + 0.2 AS approximate, 0.1 + 0.2 = 0.3 AS is_equal;

+---------------------+----------+
|     approximate     | is_equal |
+---------------------+----------+
| 0.30000000000000004 |        0 |
+---------------------+----------+

0.1 + 0.2 isn't exactly 0.3 in floating-point arithmetic. For money, use NUMERIC(p, s) (called DECIMAL in MySQL) in PostgreSQL, MySQL, and SQL Server, which stores exact decimal values — or store whole cents as integers.

(Booleans in SQLite are just 1 and 0, which is why is_equal shows 0.)

SQLite is flexible about types

Most databases reject a value of the wrong type. SQLite, by default, uses type affinity: it tries to convert values, and otherwise stores them as given:

-- standalone
CREATE TABLE t (n INTEGER);
INSERT INTO t VALUES (42), ('17'), ('hello');
SELECT n, typeof(n) AS stored_as FROM t;

+-------+-----------+
|   n   | stored_as |
+-------+-----------+
|    42 | integer   |
|    17 | integer   |
| hello | text      |
+-------+-----------+

PostgreSQL would reject 'hello' in an INTEGER column with an error. SQLite's STRICT tables (CREATE TABLE t (...) STRICT;) enforce types too. (The -- standalone comment is just this course's marker for examples that start from an empty database.)

Converting types: CAST

SELECT CAST('42' AS INTEGER) + 1 AS number, CAST(7 AS REAL) / 2 AS decimal;

+--------+---------+
| number | decimal |
+--------+---------+
|     43 |     3.5 |
+--------+---------+

PostgreSQL also has a shorthand: '42'::integer.

Choosing types

• Use the most specific type: DATE for dates (not text), INTEGER for counts, NUMERIC for money, BOOLEAN for yes/no.
• Store dates and times in ISO format (YYYY-MM-DD HH:MM:SS), and timestamps in UTC.