Data Types, NULL and Choosing the Right Type

Every column has a data type: whole numbers, decimals, text, dates, true/false. The type decides what values the column accepts, how they compare and sort, how much space they take, and what arithmetic does with them. You have already met the bugs that come from types on the last few pages: '9' < '10' being false, 7 / 2 being 3, the word cheap sitting in a price column.

Types also travel. When you load a table into pandas, the column types become dtypes; when a pipeline writes features to Parquet, they become Parquet types; when a model reads them, a date stored as text is just a string it cannot learn from. Choosing types well at the database is the cheapest data cleaning you will ever do.

What you will learn

• Numbers: integers, exact decimals and floating point, and why money is never a FLOAT.
• Text: CHAR, VARCHAR and TEXT, and what length limits really do.
• Dates and times: ISO 8601 text in SQLite, DATE/TIMESTAMP/TIMESTAMPTZ elsewhere, and time zones.
• Booleans in three databases, and NULL as the absence of any value.
• SQLite's type affinity, STRICT tables, and a table for choosing types.

SQLite stores five kinds of value

SQLite is the odd one out, so start there. Whatever a column is declared as, each value SQLite stores is one of five storage classes, and typeof() tells you which:

SELECT typeof(42), typeof(4.2), typeof('42'), typeof(NULL), typeof(x'2A');

+------------+-------------+--------------+--------------+---------------+
| typeof(42) | typeof(4.2) | typeof('42') | typeof(NULL) | typeof(x'2A') |
+------------+-------------+--------------+--------------+---------------+
| integer    | real        | text         | null         | blob          |
+------------+-------------+--------------+--------------+---------------+

INTEGER, REAL (a floating-point number), TEXT, NULL and BLOB (raw bytes, such as an image or a serialised embedding). There is no separate date, decimal or boolean storage. MySQL and PostgreSQL are different: the column's type is enforced, and values that do not fit are refused. Keep that contrast in mind for the rest of the page.

Numbers: integer, decimal, float

DECIMAL(10, 2) means up to 10 digits in total, 2 of them after the decimal point: the largest value is 99,999,999.99. A PostgreSQL INTEGER stops at 2,147,483,647, about 2.1 billion, which a busy table's ids or a sum of LLM tokens can pass:

-- PostgreSQL
SELECT 2147483647 + 1 AS too_big;

Error: integer out of range

BIGINT goes to about 9.2 × 10¹⁸. Use it for ids of tables that grow without limit (events, logs, messages) and for running totals. SQLite's INTEGER is already 8 bytes, so it does not have this problem.

Why money is never a FLOAT

Floating-point numbers are stored in binary, and most decimal fractions, like 0.1, have no exact binary form. The errors are tiny, but they are there:

SELECT 0.1 + 0.2 AS total, 0.1 + 0.2 = 0.3 AS equal;

+---------------------+-------+
| total               | equal |
+---------------------+-------+
| 0.30000000000000004 |     0 |
+---------------------+-------+

The sqlite3 shell rounds what it displays to 15 digits and prints 0.3, but the equal column gives the game away. In PostgreSQL and MySQL, a literal like 0.1 is an exact decimal by default, and only becomes binary floating point when you ask for it. Seen side by side:

-- PostgreSQL
SELECT 0.1 + 0.2                 AS numeric_sum,
       0.1::float8 + 0.2::float8 AS float_sum;

+-------------+---------------------+
| numeric_sum | float_sum           |
+-------------+---------------------+
|         0.3 | 0.30000000000000004 |
+-------------+---------------------+

A rounding error of a fraction of a paisa looks harmless until a monthly report summed over a million payments is off by a few taka and the accountant asks why. Two reliable ways to store money:

• Integers in the smallest unit: 1999 paisa, not 19.99 taka. Exact in every database, including SQLite. BitByte Shop stores prices as whole taka, which is the same idea.
• DECIMAL(12, 2) (NUMERIC in PostgreSQL): exact decimal arithmetic in MySQL and PostgreSQL. Python receives these as decimal.Decimal, not float.

Floats are the right choice for things that are measured, not counted: a model's confidence score, a latency in milliseconds, a feature scaled to 0–1, the numbers in an embedding. Just never test them with =; compare with a tolerance or round first.

Text: CHAR, VARCHAR, TEXT

• CHAR(n): exactly n characters, padded with spaces. Only useful for fixed-length codes such as CHAR(2) country codes or CHAR(3) currency codes.
• VARCHAR(n): up to n characters. The usual choice for names, emails and statuses; the limit documents what you expect and catches garbage.
• TEXT: any length. For descriptions, prompts, model responses, documents. In PostgreSQL, TEXT and VARCHAR perform the same.

The length limit is where SQLite differs. It accepts the declaration and ignores the number:

CREATE TABLE short_names (name VARCHAR(5));
INSERT INTO short_names VALUES ('Farhana Akter');
SELECT name, length(name) AS characters FROM short_names;
DROP TABLE short_names;

+---------------+------------+
| name          | characters |
+---------------+------------+
| Farhana Akter |         13 |
+---------------+------------+

-- PostgreSQL
CREATE TABLE short_names (name VARCHAR(5));
INSERT INTO short_names VALUES ('Farhana Akter');

Error: value too long for type character varying(5)

MySQL in strict mode says ERROR 1406: Data too long for column 'name' at row 1. In both servers n counts characters, not bytes, so a Bangla name such as "ফারহানা" counts as 7 characters, not 21 bytes. In MySQL, use the utf8mb4 character set (the default since 8.0): the older utf8 cannot store emoji and some other characters, a classic source of failed inserts from user-generated text.

Dates and times

SQLite has no date type. Dates are text in ISO 8601 form (some apps use Unix-epoch integers instead), and SQLite's date functions understand that text:

SELECT order_date,
       typeof(order_date)           AS stored_as,
       date(order_date, '+30 days') AS due_date
FROM orders
WHERE order_id = 1;

+------------+-----------+------------+
| order_date | stored_as | due_date   |
+------------+-----------+------------+
| 2026-01-12 | text      | 2026-02-11 |
+------------+-----------+------------+

This only works if every value uses the same ISO form. One row written as '12/01/2026' silently breaks sorting, range filters and date functions for that row.

Time zones

Bangladesh is UTC+6, your cloud server is probably on UTC, and your users may be anywhere. PostgreSQL's TIMESTAMPTZ stores an exact instant (internally in UTC) and shows it in the session's time zone. TIMESTAMP without a zone stores the clock reading as written, with no idea where it was read:

-- PostgreSQL
SELECT TIMESTAMPTZ '2026-03-08 10:00:00+06' AS instant,
       TIMESTAMP   '2026-03-08 10:00:00'    AS wall_clock;

+---------------------------+---------------------+
| instant                   | wall_clock          |
+---------------------------+---------------------+
| 2026-03-08 04:00:00+00:00 | 2026-03-08 10:00:00 |
+---------------------------+---------------------+

10 am in Dhaka is 4 am UTC; this session runs in UTC, so that is how it is shown. Switch the session to Dhaka time and the same instant is displayed the local way:

-- PostgreSQL
SET TIME ZONE 'Asia/Dhaka';
SELECT TIMESTAMPTZ '2026-03-08 04:00:00+00' AS same_instant;

+---------------------------+
| same_instant              |
+---------------------------+
| 2026-03-08 10:00:00+06:00 |
+---------------------------+

(These tables are printed by Python, which writes the offset as +06:00; psql prints +06.) The rule that saves you: store moments in UTC with a time-zone-aware type, convert to local time only for display. It matters for AI work too: "daily active users" or "calls per day" counted in UTC put everything from midnight to 6 am Dhaka time on the previous day.

Booleans

SQLite has no boolean type: TRUE and FALSE are just the integers 1 and 0. MySQL also shows 1 and 0, and its BOOLEAN is an alias for a tiny integer:

-- MySQL
CREATE TABLE flags (is_active BOOLEAN, price DECIMAL(10, 2), score FLOAT);
SELECT column_name, column_type
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'flags'
ORDER BY ordinal_position;

+-------------+---------------+
| COLUMN_NAME | COLUMN_TYPE   |
+-------------+---------------+
| is_active   | tinyint(1)    |
| price       | decimal(10,2) |
| score       | float         |
+-------------+---------------+

Only PostgreSQL has a real BOOLEAN type with true and false, as you saw on Filtering Rows: WHERE, AND, OR and NOT. Portable advice: name flag columns as questions (is_active, has_paid), store 0/1 or BOOLEAN, and filter with WHERE is_active = 1 in SQLite and MySQL, WHERE is_active in PostgreSQL.

NULL is not zero and not empty

NULL can sit in a column of any type, unless the column is declared NOT NULL. It is a missing value, which is different from 0 or the empty string:

SELECT '' IS NULL      AS empty_is_null,
       0 IS NULL       AS zero_is_null,
       length('')      AS empty_length,
       length(NULL)    AS null_length;

+---------------+--------------+--------------+-------------+
| empty_is_null | zero_is_null | empty_length | null_length |
+---------------+--------------+--------------+-------------+
|             0 |            0 |            0 | NULL        |
+---------------+--------------+--------------+-------------+

Decide on one representation for "missing" per column and stick to it. A column where some missing emails are NULL and some are '' needs two checks in every query, and someone will forget one. (Oracle, unusually, treats '' as NULL, which surprises people who move from it.)

SQLite's type affinity, and STRICT tables

In SQLite the declared type is a preference, called the column's affinity. It is chosen from the type name: anything containing INT gets INTEGER affinity; CHAR, CLOB or TEXT gets TEXT; REAL, FLOA or DOUB gets REAL; most other names, including DATE and DECIMAL, get NUMERIC. SQLite converts a value to the preferred type when it can do so without loss, and otherwise stores it as it is:

CREATE TABLE affinity_demo (n INTEGER, t TEXT, r REAL);
INSERT INTO affinity_demo VALUES ('42', 42, '3'), ('abc', 4.5, 'x'), (7.0, '007', 2);
SELECT n, typeof(n), t, typeof(t), r, typeof(r) FROM affinity_demo;

+-----+-----------+-----+-----------+-----+-----------+
| n   | typeof(n) | t   | typeof(t) | r   | typeof(r) |
+-----+-----------+-----+-----------+-----+-----------+
|  42 | integer   | 42  | text      | 3.0 | real      |
| abc | text      | 4.5 | text      | x   | text      |
|   7 | integer   | 007 | text      | 2.0 | real      |
+-----+-----------+-----+-----------+-----+-----------+

'42' became the integer 42 and 7.0 became 7, but 'abc' could not be converted, so it was kept as text in an INTEGER column. That flexibility is how cheap ended up in a price on the last page. Since SQLite 3.37 you can switch it off by adding STRICT to the table:

CREATE TABLE strict_demo (n INTEGER, t TEXT) STRICT;
INSERT INTO strict_demo VALUES ('42', 'ok');       -- still converted: '42' is clearly 42
INSERT INTO strict_demo VALUES ('abc', 'bad');     -- refused

Error: cannot store TEXT value in INTEGER column strict_demo.n

A STRICT table only accepts the types INT, INTEGER, REAL, TEXT, BLOB and ANY, so VARCHAR(100) or DATE are not allowed there; use TEXT. For new SQLite tables in real projects, STRICT is a sensible default.

Types arrive in pandas

When you read a table into pandas, every SQL type becomes a dtype, and NULLs and SQLite's text dates shape the result:

import pandas as pd
from sqlhelp import con

products = pd.read_sql("SELECT product_id, name, price, stock FROM products ORDER BY product_id", con)
print(products.dtypes)

orders = pd.read_sql("SELECT order_id, order_date FROM orders ORDER BY order_id", con,
                     parse_dates=["order_date"])
print(orders.dtypes)

product_id      int64
name              str
price           int64
stock         float64
dtype: object
order_id               int64
order_date    datetime64[us]
dtype: object

Three things to notice. stock is float64, not int64, because it contains NULLs and pandas stores missing numbers as NaN, a float. Text columns show as str (pandas 3.0; older versions show object). And order_date becomes a real datetime only because of parse_dates; without it, it arrives as plain text, exactly as SQLite stored it.

Choosing the right type

Common mistakes

1. FLOAT or REAL for money. Sums drift by fractions. Store INTEGER paisa or DECIMAL(12, 2).
2. Dates as text in a local format. '08/03/2026' does not sort or compare as a date, and is it 8 March or 3 August? Use ISO 8601: '2026-03-08'.
3. Numbers stored as text. Sorting puts '10' before '9', and sums need a CAST everywhere. Give numbers a number type.
4. Timestamps without a time zone. Two servers in different zones write different clock readings for the same moment. Use TIMESTAMPTZ or store UTC consistently.
5. Trusting SQLite to enforce types or lengths. It does neither by default. Use STRICT tables, and CHECK constraints for lengths that matter, or validate in Python before inserting.

Try it yourself

1. Easy: Use typeof() to find the storage class of name, city and joined_on for customer 5, and of name, price and stock for product 6. Which values are NULL?
2. Medium: Create a STRICT SQLite table llm_calls with an integer id, a model name, prompt and completion token counts, a cost in micro-dollars (integer) and a UTC timestamp as text. Insert one valid row, then show that a token count of 'many' is refused.
3. Hard: Write a PostgreSQL CREATE TABLE payouts for payments to course instructors: id, instructor name, amount in taka with paisa, whether it has been paid, and the exact moment it was paid. Insert one row with an amount of 1250.50 paid at 3 pm Dhaka time, and select it back.

Answers

-- 1: NULL has its own storage class, 'null'
SELECT typeof(name), typeof(city), typeof(joined_on) FROM customers WHERE customer_id = 5;
SELECT typeof(name), typeof(price), typeof(stock) FROM products WHERE product_id = 6;

-- 2
CREATE TABLE llm_calls (
    call_id           INTEGER PRIMARY KEY,
    model             TEXT NOT NULL,
    prompt_tokens     INTEGER NOT NULL,
    completion_tokens INTEGER NOT NULL,
    cost_micro_usd    INTEGER NOT NULL,      -- 0.0042 USD is stored as 4200
    called_at         TEXT NOT NULL          -- ISO 8601 in UTC
) STRICT;
INSERT INTO llm_calls VALUES (1, 'small-model', 812, 145, 4200, '2026-03-08T04:00:00Z');
SELECT * FROM llm_calls;

import sqlite3
from sqlhelp import con

# 2, continued: the bad row is refused
try:
    con.execute("INSERT INTO llm_calls VALUES (2, 'small-model', 'many', 10, 100, '2026-03-08T04:01:00Z')")
except sqlite3.IntegrityError as error:
    print(error)

cannot store TEXT value in INTEGER column llm_calls.prompt_tokens

-- PostgreSQL
-- 3
CREATE TABLE payouts (
    payout_id   BIGINT PRIMARY KEY,
    instructor  VARCHAR(100) NOT NULL,
    amount      NUMERIC(12, 2) NOT NULL CHECK (amount > 0),
    is_paid     BOOLEAN NOT NULL DEFAULT false,
    paid_at     TIMESTAMPTZ
);
INSERT INTO payouts VALUES (1, 'Lamia Karim', 1250.50, true, '2026-03-08 15:00:00+06');
SELECT * FROM payouts;

The amount comes back as an exact decimal, is_paid as a real boolean, and paid_at as the instant 09:00 UTC: shown as 09:00:00+00 in a UTC session, or 15:00:00+06 in a session set to Asia/Dhaka.

Summary

• Integers for ids, counts and money in the smallest unit; DECIMAL/NUMERIC for exact decimals; floats only for measured values. Never FLOAT for money.
• CHAR(n) for fixed codes, VARCHAR(n) for bounded text, TEXT for long text. SQLite ignores the length; the servers enforce it, in characters.
• SQLite stores dates as ISO 8601 text; MySQL and PostgreSQL have DATE and timestamps. Store moments in UTC with a time-zone-aware type.
• Booleans are 0/1 in SQLite and MySQL (TINYINT(1)), real BOOLEAN in PostgreSQL. NULL is neither 0 nor ''.
• SQLite's affinity converts when it can and stores anything otherwise; STRICT tables make it enforce types.

Next: CREATE, ALTER, DROP and Constraints. Now that you can choose a type for each column, you will build whole tables: keys, NOT NULL, UNIQUE, CHECK and DEFAULT rules, and how to change a table once it holds data.