SQL Data Types Explained (2026)

Quick Answer: What Do SQL Data Types Actually Control?

The $340 Rounding Error

A finance table stores price as FLOAT instead of DECIMAL. Individually, each price looks fine — $19.99 shows up as $19.99. But floating-point numbers store an approximation of most decimal values in binary, not an exact value, and small rounding differences accumulate every time prices are summed across millions of rows. Nine months later, a quarterly revenue report is off by $340 against the accounting system, and nobody can find a bug in the logic — because there isn't one. The type itself was wrong from the start.

Data types feel like a formality during table design — something to click through quickly and move on. In practice, the type you pick for a column decides, months in advance, whether a report will be trustworthy, whether a leading zero survives, and whether a date behaves consistently across time zones.

Numeric Types: INTEGER, DECIMAL, and FLOAT

Numeric types split into two fundamentally different families: exact and approximate.

-- correct: exact, no accumulated rounding error
CREATE TABLE orders (
  order_id INTEGER,
  price DECIMAL(10,2)
);

-- risky at scale: FLOAT is an approximation
CREATE TABLE orders (
  order_id INTEGER,
  price FLOAT
);

String Types: VARCHAR, TEXT, and CHAR

Text storage has three common options, and the differences are smaller than most beginners expect.

In PostgreSQL specifically, performance between VARCHAR and TEXT is nearly identical — the practical reason to pick VARCHAR(n) over TEXT is to have the database enforce a maximum length as a constraint, not for any storage or speed advantage. CHAR is rare outside fixed-width codes like a 2-letter country code or a 3-letter currency code, since it pads shorter values with trailing spaces to reach its fixed length.

Date and Time Types

Getting date/time types wrong is one of the most common sources of subtle bugs in applications used across regions.

CREATE TABLE orders (
  order_date DATE,                     -- calendar date only, no time
  created_at TIMESTAMP,                -- date + time, no time zone
  updated_at TIMESTAMPTZ               -- date + time, time-zone aware
);

• DATE stores only a calendar date — correct for a birthday or a due date where time-of-day is irrelevant.
• TIMESTAMP stores date and time but has no concept of time zone — the same stored value can mean different real moments depending on how the application interprets it.
• TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ in PostgreSQL) normalizes incoming values to a consistent reference internally, which is the safer default for any system used across more than one time zone.

Boolean: Not Always a Native Type

True/false flags feel like the simplest possible data type, but support for a dedicated boolean type is inconsistent across databases.

In MySQL, a column declared BOOLEAN actually stores as TINYINT(1) under the hood — it accepts 0 and 1 and behaves close to a boolean in practice, but tools and drivers occasionally surface it as a small integer rather than true/false. Worth knowing before you're debugging why a boolean-looking column shows up as 1 instead of true in some client.

Implicit Conversion: When SQL Guesses for You

Comparing values of different types doesn't always error — many databases silently convert one side to match the other, called implicit conversion. It can work in your favor or quietly produce a wrong result.

-- risky: relies on the database silently converting types
SELECT * FROM orders WHERE order_id = '1024';

-- explicit: cast deliberately instead of hoping
SELECT * FROM orders WHERE order_id = CAST('1024' AS INTEGER);

-- PostgreSQL shorthand cast syntax
SELECT * FROM orders WHERE order_id = '1024'::INTEGER;

The specific rules for what converts to what, and whether a mismatch errors or silently coerces, differ across database systems and even across versions of the same one. Rather than memorizing every rule, cast explicitly with CAST() or the :: shorthand whenever you're comparing values that didn't originate as the same type — it removes the guesswork entirely.

Choosing the Right Type: A Practical Checklist

Mistakes That Give Away a Beginner

• Storing money as FLOAT. The $340 rounding error from this article's opening — invisible at small scale, expensive once it compounds.
• Storing phone numbers or zip codes as INTEGER. A leading zero has no meaning in a number and gets silently dropped.
• Using naive TIMESTAMP for a multi-region application. Works fine until users in two time zones compare the same stored value and get different real moments.
• Comparing a string column to a number without casting and trusting whatever the database's implicit conversion rules happen to do.
• Assuming BOOLEAN behaves identically everywhere. It's a real type in PostgreSQL and BigQuery, but an alias for TINYINT(1) in MySQL.

Practice: Pick the Right Type for Each Column

For a new employees table, decide the correct data type and briefly justify it for each of these:

If your answers were DECIMAL, VARCHAR, and TIMESTAMPTZ — in that order — you've got the core decision logic down.

Frequently Asked Questions

Conclusion: The Fundamentals Tier Is Complete

Data types decide how a column behaves long before anyone notices — they're the quiet foundation under everything covered so far in this cluster. Use DECIMAL for money, keep identifiers with leading zeros as text, pick a time-zone-aware timestamp when regions are involved, and cast explicitly instead of trusting implicit conversion. Get these four decisions right at table design time, and entire categories of bugs simply never happen.

That closes out the fundamentals tier: SELECT, WHERE, ORDER BY/LIMIT, and data types. The next article starts the intermediate tier with GROUP BY and HAVING — turning individual rows into aggregated summaries.