Data Types and NULL

In one line

Pick types that make invalid data impossible and storage compact: bigint for IDs, numeric for money (never float), timestamptz for instants, text with CHECKs instead of arbitrary varchar(n), jsonb for flexible documents. NULL means unknown and follows three-valued logic, which changes comparisons, joins, aggregates, uniqueness and indexing.

0/6 · 0%

Think of it like this

A form field left blank. Blank could mean "doesn't apply", "unknown" or "forgot". SQL has one NULL for all of these, and it isn't equal to anything, not even another NULL.

Key ideas

01
Integers: int (±2.1 billion) overflows sooner than people expect for IDs and counters; use bigint for IDs. numeric(12,2) stores exact decimals for money; double precision makes 0.1 + 0.2 = 0.30000000000000004.
02
Time: timestamptz stores an absolute instant (UTC internally) and converts to the session time zone on output; timestamp without time zone is a wall-clock reading with no zone, and is ambiguous across regions and DST. Use date for calendar dates like birthdays.
03
Text: in PostgreSQL text and varchar perform the same; char(n) pads with spaces. Constrain length and format with CHECK (CHECK (length(code) = 6)) when it's a business rule.
04
JSONB: binary, indexed with GIN, great for sparse or evolving attributes; not a substitute for columns you filter, join or constrain frequently. Arrays suit small, owned lists. Enums are compact but adding values needs ALTER TYPE, and removing them is hard; a lookup table or CHECK is more flexible.
05
Three-valued logic: NULL = NULL is UNKNOWN; use IS NULL / IS DISTINCT FROM. Aggregates skip NULLs (count(col) vs count(*), avg ignores them); sum of no rows is NULL, so wrap it in coalesce. B-tree indexes do store NULLs in PostgreSQL, so WHERE col IS NULL can use an index.

Code & diagrams

types-null.sqlsql

SELECT 0.1::float8 + 0.2::float8;          -- 0.30000000000000004
SELECT 0.1::numeric + 0.2::numeric;        -- 0.3

SET timezone = 'Asia/Kolkata';
SELECT '2026-03-29 02:30:00+00'::timestamptz;   -- 2026-03-29 08:00:00+05:30

SELECT NULL = NULL;                          -- NULL (unknown), not true
SELECT NULL IS NOT DISTINCT FROM NULL;       -- true
SELECT count(*), count(discount), avg(discount), sum(discount)
FROM (VALUES (10), (NULL), (20)) t(discount);
--  count | count | avg | sum
--      3 |     2 |  15 |  30

-- JSONB attributes with a GIN index
CREATE TABLE product_attr (product_id bigint PRIMARY KEY, attrs jsonb NOT NULL);
CREATE INDEX ON product_attr USING gin (attrs jsonb_path_ops);
SELECT product_id FROM product_attr WHERE attrs @> '{"color": "red", "size": "M"}';

Interview problem

The problem

Model money and time for a global marketplace

A marketplace operates in India, the US and the EU, in INR, USD and EUR. Payments are stored as float amounts and timestamp times; finance reports are off by paise and orders appear on the wrong day. Redesign the columns.

When it breaks

int primary key overflow

What you see

At 2,147,483,647 inserts fail with "integer out of range"; a production outage at an arbitrary moment, and converting to bigint rewrites the table.

Fix & prevent

Use bigint from the start; monitor sequence headroom (pg_sequences.last_value); for existing tables, migrate with a new column and trigger-based backfill.

Explain it without notes

Why does count(col) differ from count(*)?

Practice

Write a WHERE clause matching rows where a differs from b, treating two NULLs as equal and NULL vs value as different.

Trade-offs

↔
Precise types (numeric, timestamptz, constraints) cost a little CPU and storage and prevent entire classes of bugs.

Done when you can

I can choose correct types for money, time, IDs and flexible attributes, and reason about NULL.

Command Palette