SQL / DEFINING TABLES AND CONSTRAINTS
Choosing text, numeric, and date column types
Pick the right column type for money, text, and time in PostgreSQL, and predict which values a type rejects and which it quietly rounds or truncates.
What you will learn
- Use numeric(p,s) for money; double precision drifts as soon as you sum rows.
- Read varchar(n) as a rule: overlong input raises an error, it is not trimmed.
- Choose date for calendar days, timestamp for wall clock, timestamptz for instants.
- Keep codes with leading zeros or symbols in text, and size integers by real range.
Understanding Choosing text, numeric, and date column types
A column type is the first rule the database enforces, and it acts before any constraint you write yourself. Some values it refuses outright: 'AB123' will not go into varchar(4). Others it accepts but reshapes on the way in, so numeric(5,2) turns 3.14159 into 3.14 and a date column keeps 2026-02-28 and discards the 18:45 that arrived with it. The question when declaring a column is therefore not what could hold this value, but which type makes wrong values impossible and right values exact.
Integers, exact decimals, and binary floats fail in different directions. integer is a counter that stops at 2147483647, a ceiling real id columns do hit, and bigint is the boring fix. numeric(p,s) stores base-10 digits and does base-10 arithmetic, so 0.10 is exactly 0.10, while real and double precision store the nearest binary fraction, and 0.1 has no exact binary form, so each value is a hair off and every addition re-rounds. That is why a ledger belongs in numeric with an explicit scale while a sensor reading is fine in double precision: the temperature was already approximate, the money never was.
For text, the length in varchar(n) is a business rule rather than a performance setting; in PostgreSQL text, varchar, and varchar(n) share the same storage, so text costs nothing and a limit buys validation. Anything with leading zeros, plus signs, or hyphens is text: '01234' placed in an integer column comes back as 1234, and afterwards you cannot tell which values were padded. Time splits the same way, where date is a calendar day with no clock and no zone, timestamp is a wall-clock reading that does not identify a moment, and timestamptz records an actual instant normalized to UTC. The types even answer different questions, since subtracting two dates yields an integer count of days while subtracting two timestamps yields an interval, which is a strong hint that the type carries meaning and not just formatting.
-- PostgreSQL
CREATE TABLE reading (
sensor varchar(6),
amount numeric(5,2),
approx real,
taken_on date,
taken_at timestamp
);
INSERT INTO reading
VALUES ('s-1', 3.14159265358979, 3.14159265358979,
'2026-02-28 18:45:00', '2026-02-28 18:45:00');
SELECT sensor, amount, approx, taken_on, taken_at FROM reading;A column type is the first constraint on a table: it decides which values are refused and which are silently reshaped, so choose the type whose exactness, range, and meaning match the real value.
Worked examples
Money in numeric, not double precision
The same ten values total correctly in an exact decimal column and incorrectly in a binary float column.
-- PostgreSQL
CREATE TABLE cart_item (
price_num numeric(8,2),
price_float double precision
);
INSERT INTO cart_item VALUES
(0.10, 0.10), (0.10, 0.10), (0.10, 0.10), (0.10, 0.10), (0.10, 0.10),
(0.10, 0.10), (0.10, 0.10), (0.10, 0.10), (0.10, 0.10), (0.10, 0.10);
SELECT sum(price_num) AS numeric_total,
sum(price_float) AS float_total
FROM cart_item;Example explained
Line 1price_num numeric(8,2) keeps base-10 digits, so each 0.10 is stored exactly and the ten rows add to 1.00.
Line 2price_float stores the nearest binary double to 0.1, which is not 0.1, and each addition re-rounds the running total.
Line 3sum(price_float) settles one representable step below 1, at 0.9999999999999999, even though every single row prints as 0.1.
Line 4Rounding at display time hides that gap; only changing the column type removes it.
Length limits reject, they do not trim
varchar(4) refuses a five-character code instead of shortening it, while text takes prose of any length.
-- PostgreSQL
CREATE TABLE account (
code varchar(4),
note text
);
INSERT INTO account VALUES ('AB12', 'any length of prose fits here');
SELECT code, length(note) AS note_len FROM account;
INSERT INTO account VALUES ('AB123', 'this row never lands');Example explained
Line 1'AB12' is exactly four characters, so it fits varchar(4) and the row inserts.
Line 2length(note) is 29 because text has no declared limit, so no length had to be invented for free-form prose.
Line 3The five-character code raises an error rather than being cut down to 'AB12', and inside a transaction that error aborts the statements after it.
Line 4varchar(4) is enforcement, not a hint, so widening it later means an ALTER TABLE.
date and timestamp answer different questions
Subtracting dates yields whole days while subtracting timestamps yields an interval that keeps the clock time.
-- PostgreSQL
CREATE TABLE stay (
arrive_on date,
depart_on date,
arrive_at timestamp,
depart_at timestamp
);
INSERT INTO stay VALUES
('2026-03-01', '2026-03-04', '2026-03-01 22:00:00', '2026-03-04 09:30:00');
SELECT depart_on - arrive_on AS nights,
depart_at - arrive_at AS stay_length
FROM stay;Example explained
Line 1depart_on - arrive_on returns the integer 3 because date subtraction is counted in whole days, which is what a night count is.
Line 2depart_at - arrive_at returns an interval, so the 22:00 arrival and 09:30 departure survive as 2 days 11:30:00.
Line 3Declaring arrive_at as date would have dropped 22:00 at insert time and the stay would read as three full days.
Line 4The choice changes which questions the column can still answer, not only how the value prints.
Important notes
char(n) blank-pads values to n, and PostgreSQL hides that padding in length() and when casting to text, so it resurfaces only in octet_length and in other engines' comparison rules; prefer text or varchar(n).
These examples are PostgreSQL: numeric is spelled DECIMAL in MySQL and SQL Server, and MySQL's TIMESTAMP converts to UTC while its DATETIME does not, so verify type behaviour on your engine.
Common mistakes
Declaring a price as double precision or float: each row looks right, then sum() reports 0.9999999999999999 and a month of invoices is off by cents nobody can trace to a row.
Storing postcodes, phone numbers, or SKUs as integer: '01234' comes back as 1234, '+44 20 7000 0000' will not insert at all, and the lost leading zeros cannot be reconstructed.
Using date for an event that has a clock time: 2026-02-28 18:45 lands as 2026-02-28, so events within a day can no longer be ordered and the time is gone for good.
Try it yourself
Change, predict, then run
Create a table with columns amt numeric(5,2), amt_f double precision, day date, and at timestamp, then insert 12.345 into both amount columns and '2026-03-01 23:59:59' into both time columns. Select the row back and name the two values that differ from what you typed, and say what the type did to each.
Open the SQL workspaceCheck your understanding
A table stores prices as double precision. Every row displays the price you entered, but the invoice total is off by a cent. What is happening?
- double precision keeps only two decimal places, so the cents are lost when each row is inserted.
- The client rounds each price for display, so the total is right and only the printed rows are wrong.
- Each price is stored as the nearest binary fraction, and those small errors accumulate as the rows are added.
- sum() switches to approximate arithmetic on large tables unless the column is declared with a precision.
Show answer
A value like 19.99 has no exact representation in base 2, so each stored double is slightly off and every addition re-rounds the running total, which is why the total drifts while individual rows look fine. Option 2 is tempting because display rounding is real, but here the total itself is wrong; the same rows in numeric(10,2) add up exactly.