Data Types

numeric vs floating point, text, dates and intervals, time zones and casting.

PostgreSQL has a large, strict type system. Choosing the right type prevents whole classes of bugs: money in numeric instead of float, timestamps with time zones, text instead of fixed-length strings.

Numbers, text, dates and casts

0.1 + 0.2 = 0.3 is true for numeric (exact decimal) and false for float8 (binary floating point).

7 / 2 is 3: dividing two integers gives an integer. Write 7 / 2.0 for a decimal result.

2025-01-31 plus one month is 2025-02-28: PostgreSQL clamps to the last day of the shorter month.

A timestamptz entered as 10:00 UTC is displayed as 15:30+05:30 because the session time zone is Asia/Kolkata.

extract(dow ...) returns 6: 2025-03-01 was a Saturday (Sunday = 0).

Choosing types

price numeric(10,2) Binary floating point cannot represent most decimal fractions exactly, so sums of prices drift by fractions of a paisa or cent.

SELECT hours / people FROM... -- both integer SELECT hours::numeric / people FROM...

Integer division truncates. Cast one operand to numeric (or multiply by 1.0) for a decimal result.

What does SELECT 5 / 2, 5 % 2, 5 / 2.0; return?

2, 1 and 2.5000000000000000 (integer division, remainder, numeric division).

Why is timestamptz recommended over timestamp?

It represents an absolute point in time (stored in UTC, shown in the session's zone), so values from different time zones compare correctly. timestamp has no zone and is ambiguous.