Why Data Types Matter More Than They Look
Picking the right data type isn't just pedantry — it directly affects storage size, index performance, join correctness, and whether calculations round the way you expect. This guide is a practical reference for the types you'll actually use day to day, with the naming and behavior differences between PostgreSQL, MySQL, and SQL Server called out explicitly, since this is one of the most common sources of confusion when switching between databases.
Numeric Types
| Purpose | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Small integer | SMALLINT | SMALLINT | SMALLINT |
| Standard integer | INTEGER / INT | INT | INT |
| Large integer | BIGINT | BIGINT | BIGINT |
| Exact decimal (money, precise math) | NUMERIC(p,s) / DECIMAL(p,s) | DECIMAL(p,s) | DECIMAL(p,s) / NUMERIC(p,s) |
| Approximate float | REAL, DOUBLE PRECISION | FLOAT, DOUBLE | REAL, FLOAT |
| Auto-incrementing ID | SERIAL / IDENTITY (modern) | INT AUTO_INCREMENT | INT IDENTITY(1,1) |
Golden rule: never use FLOAT/DOUBLE for money or anything requiring exact arithmetic — binary floating point can't represent most decimal fractions exactly, so repeated calculations accumulate rounding error. Always use DECIMAL/NUMERIC with an explicit precision and scale for currency.
-- DECIMAL(10, 2): up to 10 total digits, 2 after the decimal point
CREATE TABLE payments (
id INT PRIMARY KEY,
amount DECIMAL(10, 2) NOT NULL
);String Types
| Purpose | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Fixed-length | CHAR(n) | CHAR(n) | CHAR(n) |
| Variable-length, bounded | VARCHAR(n) | VARCHAR(n) | VARCHAR(n) |
| Variable-length, unbounded | TEXT (no real limit, same performance as VARCHAR) | TEXT (stored off-row past a threshold) | VARCHAR(MAX) |
| Unicode-safe string | VARCHAR/TEXT (UTF-8 native) | VARCHAR with utf8mb4 charset | NVARCHAR(n) / NVARCHAR(MAX) |
For a full breakdown of when to use each string type and why, see our dedicated guide: CHAR vs VARCHAR vs TEXT.
SQL Server specifically distinguishes VARCHAR (single-byte, non-Unicode) from NVARCHAR (Unicode) — use NVARCHAR whenever the data might contain non-English characters, since plain VARCHAR on SQL Server can silently corrupt or lose those characters.
Date and Time Types
| Purpose | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Date only | DATE | DATE | DATE |
| Time only | TIME | TIME | TIME |
| Date + time, no timezone | TIMESTAMP | DATETIME | DATETIME2 |
| Date + time, timezone-aware | TIMESTAMPTZ | TIMESTAMP (stores in UTC, converts on read) | DATETIMEOFFSET |
Use the timezone-aware variant (TIMESTAMPTZ, DATETIMEOFFSET) for anything user-facing or spanning multiple regions — silent timezone bugs from naive timestamp columns are one of the most common and hardest-to-debug data-correctness issues in production applications. Store event times in UTC and convert for display, not the other way around.
Boolean
-- PostgreSQL: native BOOLEAN type
CREATE TABLE users (is_active BOOLEAN DEFAULT true);
-- MySQL: BOOLEAN is an alias for TINYINT(1) — no true native boolean type
CREATE TABLE users (is_active TINYINT(1) DEFAULT 1);
-- SQL Server: BIT type, 0 or 1
CREATE TABLE users (is_active BIT DEFAULT 1);MySQL's lack of a real boolean type is a frequent surprise for engineers coming from PostgreSQL — TRUE/FALSE are accepted as syntax but stored and compared as 1/0 under the hood.
JSON and Semi-Structured Data
| Database | Type | Notes |
|---|---|---|
| PostgreSQL | JSON, JSONB | JSONB is binary-parsed, indexable, and almost always preferred over plain JSON |
| MySQL | JSON | Validated on write, supports functions like JSON_EXTRACT |
| SQL Server | NVARCHAR(MAX) with JSON functions | No dedicated JSON type — JSON is stored as text with helper functions (JSON_VALUE, OPENJSON) layered on top |
JSON columns are useful for genuinely variable, sparse, or externally-defined schemas (webhook payloads, user-defined custom fields) — but resist the temptation to use them as a substitute for proper normalized columns for data your application queries and filters on regularly, since indexing and constraint enforcement are weaker than on native typed columns.
Choosing the Right Type: A Practical Checklist
- Use the smallest integer type that comfortably fits your real value range — it keeps indexes smaller and rows more compact
- Always use DECIMAL/NUMERIC for money, never FLOAT/DOUBLE
- Prefer VARCHAR(n) with a realistic limit over unbounded TEXT when the application enforces a max length anyway — it documents the constraint at the schema level
- Default to timezone-aware timestamp types unless you're certain every consumer of the data is in a single, fixed timezone forever
- Match data types exactly on both sides of any JOIN or foreign key — a type mismatch forces an implicit cast that can silently disable an index
Common Mistakes
- Using FLOAT for currency — causes rounding errors that compound across calculations and reconciliations.
- Using a naive (non-timezone-aware) timestamp for global data — leads to off-by-several-hours bugs the moment users or servers span timezones.
- Over-provisioning BIGINT everywhere "just in case" — doubles storage and index size versus INT for tables that will never approach 2 billion rows.
- Storing structured, frequently-queried data as JSON instead of proper columns, purely to avoid a migration — this trades short-term convenience for long-term query and indexing pain.
Common Interview Questions
- Why shouldn't you use FLOAT for monetary values? Binary floating point can't exactly represent most decimal fractions, so repeated arithmetic accumulates rounding error — use DECIMAL/NUMERIC instead.
- What's the difference between TIMESTAMP and TIMESTAMPTZ in PostgreSQL? TIMESTAMP stores a naive date/time with no timezone context; TIMESTAMPTZ stores it alongside timezone handling, normalizing to UTC internally and converting on read/write based on the session's timezone setting.
- Does MySQL have a real boolean type? No — BOOLEAN is an alias for TINYINT(1); TRUE/FALSE are just syntactic sugar for 1/0.
- When would you choose JSON/JSONB over a normalized table structure? When the schema is genuinely variable or externally defined (webhook payloads, plugin configuration) — not as a shortcut around designing proper columns for data your application regularly queries and filters on.
Frequently Asked Questions
Does choosing a smaller data type actually make queries faster?
Yes, measurably at scale — smaller row sizes mean more rows fit per disk page, so scans and index traversals touch less data. It's a small effect per row but compounds significantly across millions of rows.
Should I always use VARCHAR(MAX)/TEXT to avoid ever hitting a length limit?
Not by default — an explicit, realistic length limit documents an intentional constraint and can help query planners in some engines. Reserve unbounded types for genuinely unbounded content, like long-form text fields.
Practice With Real Schemas
Data type decisions show up constantly once you move from writing queries to actually designing schemas. Try it hands-on with our SQL practice questions, or work through schema design questions in our case studies. For the related constraint types that pair with these data types, see our guide to SQL constraints.