SQL Data Types – Integers, Strings, Dates & More

Numeric Types

SQL distinguishes between exact numeric types (no rounding) and approximate floating-point types. Use the right one for the job.

Type	Storage	Range	Use for
`TINYINT`	1 byte	-128 to 127 (or 0–255 unsigned)	Flags, small counters
`SMALLINT`	2 bytes	-32,768 to 32,767	Age, short codes
`INT` / `INTEGER`	4 bytes	~-2.1 billion to 2.1 billion	IDs, counts — most common
`BIGINT`	8 bytes	~-9.2 × 10¹⁸ to 9.2 × 10¹⁸	Large IDs, timestamps as numbers
`DECIMAL(p,s)`	variable	Up to 65 digits (MySQL)	Money — exact arithmetic
`FLOAT` / `DOUBLE`	4 / 8 bytes	~±3.4 × 10³⁸ / ±1.8 × 10³⁰⁸	Scientific measurements

⚠️

Never use FLOAT or DOUBLE for money

Floating-point types store approximations. 0.1 + 0.2 may equal 0.30000000000000004 internally. For prices, balances, or any financial figure, always use DECIMAL(p,s) — for example DECIMAL(10,2) stores values up to 99,999,999.99 exactly.

SQL

-- Exact integer examples
SELECT 2147483647 + 1;   -- INT overflow! Use BIGINT for very large numbers

-- DECIMAL for money: DECIMAL(total_digits, decimal_places)
price   DECIMAL(10, 2)   -- up to 99,999,999.99
tax_rate DECIMAL(5, 4)   -- e.g. 0.0875

-- Approximate: fine for science, not finance
temperature FLOAT        -- 98.6° ± tiny rounding error is acceptable

String (Character) Types

SQL has three main string types. The key difference is whether the length is fixed or variable.

Type	Length	Padding	Best for
`CHAR(n)`	Fixed: exactly n	Right-padded with spaces	Fixed-length codes (ISO country codes, phone prefixes)
`VARCHAR(n)`	Variable: up to n	No padding	Names, emails, titles — the most common choice
`TEXT`	Unlimited*	No padding	Long content: articles, descriptions, comments

SQL

-- CHAR: always 2 characters, padded if shorter
country_code CHAR(2)     -- 'US', 'GB', 'DE'

-- VARCHAR: stores only the characters you give it
email        VARCHAR(255)  -- 'alice@example.com' (18 chars stored, not 255)
first_name   VARCHAR(100)

-- TEXT: no length limit (cannot be indexed in full in MySQL without prefix)
bio          TEXT
article_body TEXT

💡

Use VARCHAR, not CHAR, unless the length is truly fixed

CHAR pads shorter values with spaces, which wastes storage and can cause unexpected comparison results. Stick with VARCHAR(n) for almost everything. Use CHAR(n) only for fixed-length codes like ISO 3166 country codes (CHAR(2)) or IBAN prefixes.

Date and Time Types

SQL provides dedicated types for dates and times — do not store them as strings or integers.

Type	Stores	Example value
`DATE`	Calendar date only	`2026-06-08`
`TIME`	Time of day only	`14:30:00`
`DATETIME`	Date + time (no timezone)	`2026-06-08 14:30:00`
`TIMESTAMP`	Date + time (UTC-aware in MySQL)	`2026-06-08 14:30:00`
`YEAR`	Four-digit year (MySQL only)	`2026`

SQL

birth_date    DATE        -- '1990-04-15'
event_time    TIME        -- '09:00:00'
created_at    TIMESTAMP   -- '2026-06-08 14:30:00' — auto-set on insert
updated_at    TIMESTAMP   -- '2026-06-08 15:00:00' — auto-set on update

-- Date literals use ISO 8601 format: YYYY-MM-DD
SELECT * FROM events WHERE event_date = '2026-12-25';

-- Compare date ranges
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-12-31 23:59:59';

ℹ️

Always store timestamps in UTC

Store all DATETIME/TIMESTAMP values in UTC in the database. Convert to the user's local timezone only at the application layer. This prevents bugs when servers change timezones or when users are in different regions. In MySQL, TIMESTAMP columns are automatically stored in UTC and converted on retrieval based on the session timezone.

BOOLEAN Type

SQL's handling of true/false values varies by database engine.

SQL

-- PostgreSQL: native BOOLEAN type
is_active  BOOLEAN DEFAULT TRUE
is_deleted BOOLEAN DEFAULT FALSE

-- MySQL: TINYINT(1) is used; TRUE=1, FALSE=0
is_active  TINYINT(1) DEFAULT 1
-- Or use BOOL/BOOLEAN alias (MySQL stores it as TINYINT(1) internally)
is_active  BOOL DEFAULT TRUE

-- SQLite: no native boolean; use INTEGER 0/1
is_active  INTEGER DEFAULT 1

-- Querying booleans
SELECT * FROM users WHERE is_active = TRUE;   -- PostgreSQL
SELECT * FROM users WHERE is_active = 1;      -- MySQL / SQLite
SELECT * FROM users WHERE is_active;          -- shorthand in PostgreSQL

How to Choose the Right Type

Follow these practical rules when designing your schema:

Use the smallest type that fits. TINYINT for a 0–100 rating saves storage compared to INT, especially across millions of rows.
Use DECIMAL for money — never FLOAT or DOUBLE. A common convention is DECIMAL(10,2) for most currencies.
Use VARCHAR over CHAR unless the value is truly fixed-length (e.g., two-letter country codes).
Use TEXT for long content (articles, comments). Be aware that TEXT columns often cannot be fully indexed in MySQL — use a prefix index if needed.
Use TIMESTAMP over DATETIME when you need timezone-awareness in MySQL. Use TIMESTAMPTZ in PostgreSQL.
Avoid storing numbers as strings. Sorting '9' > '10' alphabetically is a classic bug. Store numeric values in numeric columns.

Dialect Comparison: MySQL vs PostgreSQL vs SQLite

Concept	MySQL	PostgreSQL	SQLite
Auto-increment integer	`INT AUTO_INCREMENT`	`SERIAL` or `GENERATED ALWAYS AS IDENTITY`	`INTEGER PRIMARY KEY`
Variable string	`VARCHAR(n)`	`VARCHAR(n)` or `TEXT`	`TEXT` (SQLite is flexible)
Unlimited text	`TEXT`, `MEDIUMTEXT`, `LONGTEXT`	`TEXT`	`TEXT`
Boolean	`TINYINT(1)`	`BOOLEAN`	`INTEGER` (0/1)
Date + time with timezone	`TIMESTAMP` (converts to UTC)	`TIMESTAMPTZ`	Stored as TEXT/INTEGER/REAL
Exact decimal	`DECIMAL(p,s)`	`NUMERIC(p,s)`	`NUMERIC` (stored as REAL)
Large integer	`BIGINT`	`BIGINT`	`INTEGER` (always 8-byte)

ℹ️

SQLite uses dynamic typing

SQLite uses a concept called type affinity rather than strict types. You can declare a column as INTEGER and still store text in it — SQLite will just store what you give it. This makes SQLite very forgiving during development, but it means type errors won't be caught at the database level. Use strict typing in production databases (MySQL, PostgreSQL).

Summary

📋 Summary

Numeric: INT for most IDs/counts, BIGINT for very large numbers, DECIMAL(p,s) for money, FLOAT/DOUBLE only for scientific values where small rounding errors are acceptable.
String: VARCHAR(n) is the default choice for most text; CHAR(n) only for fixed-length codes; TEXT for long, unbounded content.
Date/Time: DATE for calendar dates, TIMESTAMP for date+time (store in UTC), TIME for time-of-day only.
Boolean: BOOLEAN in PostgreSQL, TINYINT(1) in MySQL, INTEGER (0/1) in SQLite.
Choose the smallest type that fits your data to save storage and improve query performance.

FAQ

What is the difference between DECIMAL and NUMERIC? +

In SQL standard and most databases, DECIMAL and NUMERIC are interchangeable synonyms for exact numeric types. Both accept a precision (total digits) and scale (decimal places). PostgreSQL treats them identically. MySQL has a minor internal distinction but they behave the same in practice. Use whichever your team prefers — DECIMAL is more common in MySQL/SQL Server contexts, NUMERIC in PostgreSQL.

Should I use VARCHAR(255) everywhere? +

It is tempting to use VARCHAR(255) as a lazy default, but it is better to choose a meaningful limit. Use VARCHAR(255) for email addresses (the RFC 5321 maximum is 254 characters), but VARCHAR(100) for a first name and VARCHAR(500) for a URL. Setting sensible limits lets the database enforce data integrity — a 10,000-character "first name" is likely a bug. In most databases, declaring a longer VARCHAR does not use more storage for short values; the limit is a constraint, not a reservation.

Why does TIMESTAMP have a year 2038 problem? +

In MySQL, TIMESTAMP is stored internally as a 32-bit Unix timestamp (seconds since 1970-01-01 UTC). A 32-bit signed integer overflows in January 2038. MySQL's maximum TIMESTAMP value is 2038-01-19 03:14:07 UTC. For dates beyond 2038, use DATETIME in MySQL (which supports years up to 9999) or TIMESTAMPTZ in PostgreSQL. Modern MySQL versions (8.x) and PostgreSQL are not affected for new columns because they use wider storage.

Can I change a column's data type after the table is created? +

Yes — use ALTER TABLE ... MODIFY COLUMN (MySQL) or ALTER TABLE ... ALTER COLUMN ... TYPE (PostgreSQL). However, changing to an incompatible type (e.g., VARCHAR to INT when the column contains non-numeric strings) will fail. Some changes — especially on large tables — lock the table and cause downtime. Plan type changes carefully in production, ideally through a migration tool.