Brew decided to build a simple report: "how many orders does each customer have." The query was quick to write, but the number of customers in the report came out smaller than in the customers table. Carol, who signed up but hasn't bought anything yet, simply vanished from the result. It's not a join bug — she was "eaten" by careless handling of NULL: she has no orders, so her order_id is NULL, and NULL behaves differently than it looks.

This unit is about three types that look boring but are exactly what applications trip over: text (and why not char(n)), boolean (with its three-valued logic), and NULL. The key point about NULL is that it's not "empty" and not "zero" — it's "unknown". The full NULL semantics are coming in 03-06; here it's a teaser, so the trap doesn't catch you off guard.

text, not char(n) or varchar(n)

In Postgres the default string type is text, with no length limit. varchar(n) is text with a length check (and almost never needed: constrain length with a CHECK if you must). char(n), though, is a separate trap: it pads the string with spaces to a fixed length. Because of that 'abc'::char(5) actually stores 'abc ', and on comparison the trailing spaces are "eaten": 'abc'::char(5) = 'abc '::char(5) → true. In text spaces are significant: 'abc' = 'abc ' → false. So in the course (and in the Brew base schema) we keep text — predictable byte-wise comparison.

boolean: true, false, and… NULL

boolean looks two-valued, but in SQL it's three-valued: true, false, and NULL (unknown). Logical values come easily right out of predicates: base_price > 400 is already a boolean, and sqlc types such a column as Go bool. But the moment a NULL enters the expression, the result can become NULL — and that's the next section.

NULL is "unknown", not "empty"

The key intuition: NULL means "the value is unknown." So comparing with NULL via = gives not false but NULL: NULL = NULL is "unknown = unknown" → also NULL. Two things follow:

• WHERE col = NULL never fires (the condition is never true) — to test for the absence of a value there's IS NULL / IS NOT NULL.
• Aggregates skip NULL: count(*) counts all rows, while count(col) counts only rows where col is not NULL. That same Carol is lost if you count count(order_id) instead of count(*).

NULL appears in data naturally — for example, from a LEFT JOIN: for a customer with no orders, the columns from the right table are NULL. And that's the correct, type-safe way to express "there is no value": sqlc sees that a LEFT JOIN column is nullable and types it as pgtype.Int8 (with a Valid field), not as a bare int64.

boolean and NULL: a cheat sheet

This is the everyday working minimum; the full three-valued logic (NOT IN with NULL, COALESCE, IS DISTINCT FROM) is covered in 03-06.

What our code shows

The first query is NULL in a comparison, on literals:

-- name: NullComparison :one
SELECT
    ((NULL = NULL) IS NOT TRUE)::boolean  AS eq_null_is_not_true,
    ((NULL = NULL) IS NULL)::boolean      AS eq_null_is_unknown;

Both are true: NULL = NULL is not TRUE and is in fact NULL. Next is a real NULL from a LEFT JOIN and the count(*) vs count(col) contrast:

SELECT c.id AS customer_id, c.name, o.id AS order_id          -- CustomersWithOrders :many
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id::text
ORDER BY c.id, o.id;
 
SELECT count(*) AS rows_total, count(o.id) AS rows_with_order  -- CountStarVsCol :one
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id::text;

In Go order_id is a pgtype.Int8; we print NULL when !Valid. The last two queries are a boolean from a predicate (base_price > 400) and the behavior of text/char(n) ('abc' = 'abc ' vs char(5) padding).

Running it

docker compose up -d
make lecture L=01-data-types/01-02-text-boolean-and-null-teaser T=db-reset
make lecture L=01-data-types/01-02-text-boolean-and-null-teaser

Output:

1) (NULL = NULL) — это не TRUE и не FALSE, а NULL («неизвестно»):
   (NULL = NULL) IS NOT TRUE = true;  IS NULL = true
   → отсутствие значения проверяем через IS NULL, не через = NULL.
 
2) LEFT JOIN customers ↔ orders — order_id у клиента без заказов = NULL:
CUSTOMER_ID  ИМЯ              ORDER_ID
1            Алиса Иванова    1
1            Алиса Иванова    3
2            Борис Петров     2
3            Карина Сидорова  NULL
 
3) count(*) = 4 (все строки), count(o.id) = 3 (без NULL-заказа Карины)
 
4) boolean из выражения base_price > 400 (в Go это bool):
ID  НАЗВАНИЕ     IS_PREMIUM
1   Эспрессо     false
2   Капучино     true
3   Латте        true
4   Колд брю     true
5   Зелёный чай  false
 
5) text сравнивает по байтам, char(n) дополняет пробелами:
   'abc' = 'abc '           → false  (text: пробел значим)
   'abc'::char(5) = 'abc  ' → true   (char(n): паддинг съел пробелы)

(The demo prints in Russian.) There's Carol: in the LEFT JOIN her order_id is NULL, and count(o.id) (=3) doesn't count her, while count(*) (=4) does. An "orders per customer" report should show her with a zero, not lose her — and now you can see why the naive count did exactly that.

The fence

This is only a teaser. The rest is in 03-06; here we hold three rules:

• The full NULL semantics are still ahead. NOT IN with NULL (the classic hole that returns nothing), COALESCE/NULLIF/IS DISTINCT FROM, three-valued logic in WHERE/CHECK/unique indexes — all of it in 03-06.
• Absence of a value — only IS NULL / IS NOT NULL. Never = NULL: such a condition won't become true.
• You'll almost never meet char(n) for a good reason in production. If you see it in someone else's schema, it's usually a historical mistake rather than a deliberate choice.

Takeaways

• Keep text. char(n) silently pads with spaces and corrupts comparison; varchar(n) is text with a usually-pointless length check.
• boolean is three-valued: true, false, NULL. A predicate like base_price > 400 is already a bool.
• NULL is "unknown", not "empty". NULL = NULL → NULL. Test for absence with IS NULL.
• count(*) counts rows, count(col) skips NULL. A LEFT JOIN produces real NULLs — sqlc types them as pgtype.* with a Valid field.

Next up — the 01-03 "date, time, and timestamptz" unit: why time is always stored in timestamptz, how the same instant looks different under SET TIME ZONE, and what the trap of timestamp without a zone is.