SQL 7 🛢️ Data Types: Numeric, Character, Date, and Boolean
Every column in a table has a data type. The data type defines what kind of values the column can hold, how much space those values consume, and what operations are valid on them. A column declared as INTEGER accepts whole numbers and rejects text. A column declared as VARCHAR(100) accepts strings up to 100 characters and rejects values that exceed the limit. The data type is the first layer of validation. Before any constraint runs, the database checks whether the value fits the column’s type.
The SQL standard defines a core set of data types, but each RDBMS implements them differently. PostgreSQL has the richest type system. MySQL makes different trade-offs. SQL Server uses the BIT type for booleans because it has no native BOOLEAN. Oracle uses NUMBER as its universal numeric type. This chapter covers the four fundamental categories: numeric, character, date/time, and boolean.
Key point: The choice of data type is not just about storage. It determines precision, performance, and correctness. Storing money in a FLOAT column introduces rounding errors that compound over time. Storing a date in a VARCHAR column makes date arithmetic impossible. Storing a boolean in a CHAR(1) column wastes a byte and allows values the boolean domain does not include. The data type is the contract that the database enforces.
Why data types matter
A database is not a spreadsheet. A spreadsheet column can hold a number in one row and a string in the next. A database column cannot. The data type is the rule that makes the column meaningful.
The precision problem. A FLOAT column stores an approximation. The value 0.1 is not exactly representable in binary floating point. When you store 0.1 and read it back, you may get 0.100000001490116. For scientific measurements, this is acceptable. For financial calculations, it is a bug. The NUMERIC and DECIMAL types store exact values. They are slower to compute, but the result is correct .
The range problem. An INTEGER column holds values from -2,147,483,648 to 2,147,483,647 . If a value outside this range is inserted, the database raises an error. The BIGINT type extends the range to -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 . Choosing the right integer type prevents overflow without wasting space.
The character problem. A CHAR(n) column pads values to exactly n characters. A VARCHAR(n) column stores only the characters used, up to n . A TEXT column stores unlimited-length strings. The choice affects storage and comparison behavior. Trailing spaces in CHAR values are semantically insignificant in comparisons, while trailing spaces in VARCHAR values are significant .
The time zone problem. A DATE column stores a calendar date. A TIMESTAMP column stores a date and time. A TIMESTAMP WITH TIME ZONE column stores the offset from UTC . A global application that stores timestamps without time zone information cannot correctly order events that occur in different time zones.
The trade-off. More precise types consume more storage and are slower to process. NUMERIC is slower than INTEGER. TIMESTAMP WITH TIME ZONE is larger than DATE. TEXT is more flexible than VARCHAR but cannot be indexed as efficiently in some databases. The choice depends on what the data represents and what operations it needs to support.
a. Numeric Data Types
Numeric types are divided into three categories: integers, exact decimals, and approximate floating-point numbers .
Integer types store whole numbers. The standard defines SMALLINT, INTEGER, and BIGINT. PostgreSQL also provides SMALLSERIAL, SERIAL, and BIGSERIAL as auto-incrementing integer types . The INTEGER type is the common choice because it balances range, storage, and performance .
| Type | Storage | Range |
|---|---|---|
SMALLINT | 2 bytes | -32,768 to 32,767 |
INTEGER | 4 bytes | -2,147,483,648 to 2,147,483,647 |
BIGINT | 8 bytes | -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 |
SERIAL | 4 bytes | Auto-incrementing integer |
Exact decimal types store numbers with a fixed number of digits before and after the decimal point. The NUMERIC(precision, scale) type is the standard. The precision is the total number of significant digits. The scale is the number of digits after the decimal point. The number 23.5141 has a precision of 6 and a scale of 4 . DECIMAL is a synonym for NUMERIC. These types are recommended for monetary amounts and other values where exactness is required .
-- A column for prices with 2 decimal places
price NUMERIC(10, 2)
-- Values from -99,999,999.99 to 99,999,999.99Approximate numeric types store floating-point numbers. The REAL type has 6 decimal digits of precision. The DOUBLE PRECISION type has 15 decimal digits of precision . These types are used for scientific measurements where approximate values are acceptable. They should not be used for financial data because calculations introduce rounding errors .
b. Character Data Types
Character types store strings. The standard defines CHAR, VARCHAR, and TEXT. The choice depends on whether the length is fixed or variable, and whether the column needs to be indexed.
CHAR(n) stores a fixed-length string. If the value is shorter than n, the database pads it with spaces. If the value is longer than n, the database raises an error (or truncates trailing spaces in some implementations) . The trailing spaces are semantically insignificant in comparisons. CHAR(2) for a US state code like 'CA' is a common use case.
VARCHAR(n) stores a variable-length string up to n characters. The database stores only the characters used. No padding is added . The VARCHAR type is the standard choice for names, emails, and other variable-length text. MySQL stores a one-byte or two-byte length prefix with each value, so the maximum effective length is slightly less than n for multi-byte characters .
TEXT stores unlimited-length strings. PostgreSQL’s TEXT has no length limit. MySQL’s TEXT is limited to 65,535 characters . The TEXT type is used for descriptions, articles, and other long-form content. In some databases, TEXT columns cannot be indexed as efficiently as VARCHAR columns.
-- Fixed-length state code
state CHAR(2)
-- Variable-length name
name VARCHAR(100)
-- Long description
description TEXTc. Date, Time, and Boolean Types
Date and time types store temporal data. Boolean types store true/false values.
Date and time types are divided into two groups: datetimes, which represent points in time, and intervals, which represent durations . The standard types are DATE, TIME, and TIMESTAMP. PostgreSQL and Oracle add variants with time zone support.
| Type | Stores |
|---|---|
DATE | Year, month, day |
TIME | Hour, minute, second |
TIMESTAMP | Date and time |
TIMESTAMP WITH TIME ZONE | Date, time, and UTC offset |
INTERVAL | Duration |
The TIMESTAMP WITH TIME ZONE type stores the time zone offset along with the date and time . It is the correct type for applications that operate across time zones. The TIMESTAMP WITH LOCAL TIME ZONE type normalizes the value to the database’s time zone and returns it in the user’s session time zone .
Boolean types store true/false values. The SQL standard defines a BOOLEAN type, but not every database implements it. PostgreSQL has a native BOOLEAN type that accepts TRUE, FALSE, and NULL . MySQL does not have a native boolean type — the BOOLEAN keyword is a synonym for TINYINT(1), which stores 0 or 1 . SQL Server uses the BIT type, which stores 1, 0, or NULL . Oracle 23c introduced a native BOOLEAN type, but older versions use NUMBER(1) or CHAR(1) .
-- PostgreSQL
is_active BOOLEAN
-- MySQL
is_active BOOLEAN -- actually TINYINT(1)
-- SQL Server
is_active BITComplete Example Session
This session creates a table that uses each of the four data type categories.
-- ============================================
-- PART 1: THE PRODUCTS TABLE
-- ============================================
CREATE TABLE products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(200) NOT NULL,
sku CHAR(12) NOT NULL,
description TEXT,
price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
weight_kg REAL,
stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
discontinued_on DATE
);
-- product_id: auto-incrementing integer
-- product_name: variable-length string, required
-- sku: fixed-length 12-character code
-- description: unlimited text
-- price: exact decimal with 2 places
-- weight_kg: approximate floating-point
-- stock: integer with default and check
-- is_active: boolean with default
-- created_at: timestamp with time zone
-- discontinued_on: date, nullable
-- ============================================
-- PART 2: INSERT DATA
-- ============================================
INSERT INTO products
(product_name, sku, description, price, weight_kg, stock, is_active)
VALUES
('Wireless Mouse', 'WM-2026-001', 'Ergonomic wireless mouse', 29.99, 0.085, 150, TRUE),
('Mechanical Keyboard', 'MK-2026-002', 'RGB mechanical keyboard', 89.99, 0.950, 75, TRUE),
('USB-C Cable', 'UC-2026-003', '2-meter USB-C cable', 12.99, 0.050, 500, FALSE);
-- ============================================
-- PART 3: QUERY THE DATA
-- ============================================
SELECT
product_id,
product_name,
price,
stock,
is_active,
created_at
FROM products
WHERE is_active = TRUE
ORDER BY price DESC;
-- Output:
-- product_id | product_name | price | stock | is_active | created_at
-- ------------+----------------------+-------+-------+-----------+-------------------------------
-- 2 | Mechanical Keyboard | 89.99 | 75 | t | 2026-10-01 12:00:00+00
-- 1 | Wireless Mouse | 29.99 | 150 | t | 2026-10-01 12:00:00+00
-- ============================================
-- PART 4: TEST THE CHECK CONSTRAINT
-- ============================================
INSERT INTO products (product_name, sku, price)
VALUES ('Invalid Product', 'IP-2026-004', -10.00);
-- Error: new row for relation "products" violates check constraint
-- "products_price_check"
-- DETAIL: Failing row contains (4, Invalid Product, IP-2026-004, null, -10.00, ...).
-- ============================================
-- PART 5: TEST THE CHARACTER LENGTH
-- ============================================
INSERT INTO products (product_name, sku, price)
VALUES ('Test', 'THIS-IS-TOO-LONG', 9.99);
-- Error: value too long for type character(12)
-- ============================================
-- PART 6: QUERY WITH DATE ARITHMETIC
-- ============================================
SELECT
product_name,
created_at,
created_at + INTERVAL '30 days' AS review_date
FROM products
WHERE is_active = TRUE;
-- Output:
-- product_name | created_at | review_date
-- ---------------------+-------------------------------+-------------------------------
-- Wireless Mouse | 2026-10-01 12:00:00+00 | 2026-10-31 12:00:00+00
-- Mechanical Keyboard | 2026-10-01 12:00:00+00 | 2026-10-31 12:00:00+00
-- ============================================
-- PART 7: THE NUMERIC PRECISION
-- ============================================
-- NUMERIC(10, 2) stores exactly 2 decimal places.
-- The value 29.999 is rounded to 30.00.
INSERT INTO products (product_name, sku, price)
VALUES ('Test Rounding', 'TR-2026-005', 29.999);
SELECT product_name, price FROM products WHERE sku = 'TR-2026-005';
-- Output:
-- product_name | price
-- ---------------+-------
-- Test Rounding | 30.00
-- ============================================
-- PART 8: THE BOOLEAN OPERATIONS
-- ============================================
SELECT
product_name,
is_active,
NOT is_active AS is_discontinued
FROM products;
-- Output:
-- product_name | is_active | is_discontinued
-- ---------------------+-----------+-----------------
-- Wireless Mouse | t | f
-- Mechanical Keyboard | t | f
-- USB-C Cable | f | t
-- Test Rounding | t | f
-- ============================================
-- PART 9: THE APPROXIMATE TYPE
-- ============================================
SELECT product_name, weight_kg FROM products WHERE weight_kg IS NOT NULL;
-- Output:
-- product_name | weight_kg
-- ---------------------+-----------
-- Wireless Mouse | 0.085
-- Mechanical Keyboard | 0.95
-- USB-C Cable | 0.05
-- REAL stores approximately 6 decimal digits of precision.
-- ============================================
-- PART 10: THE DATA TYPE SUMMARY
-- ============================================
-- INTEGER: whole numbers
-- NUMERIC: exact decimals for money
-- REAL/DOUBLE PRECISION: approximate decimals
-- CHAR: fixed-length strings
-- VARCHAR: variable-length strings
-- TEXT: unlimited strings
-- DATE: calendar dates
-- TIMESTAMP: date and time
-- TIMESTAMP WITH TIME ZONE: date, time, and offset
-- BOOLEAN: true/falseThe ten parts cover creating the products table, inserting data, querying with a boolean filter, testing the check constraint, testing the character length, querying with date arithmetic, testing numeric precision, using boolean operations, querying the approximate type, and the data type summary.
Quick Reference
The Numeric Types
| Type | Purpose | Example |
|---|---|---|
SMALLINT | Small integers | SMALLINT |
INTEGER | Standard integers | INTEGER |
BIGINT | Large integers | BIGINT |
SERIAL | Auto-incrementing | SERIAL |
NUMERIC(p, s) | Exact decimals | NUMERIC(10, 2) |
DECIMAL(p, s) | Synonym for NUMERIC | DECIMAL(8, 3) |
REAL | Approximate, 6 digits | REAL |
DOUBLE PRECISION | Approximate, 15 digits | DOUBLE PRECISION |
The Character Types
| Type | Purpose | Notes |
|---|---|---|
CHAR(n) | Fixed-length | Padded with spaces |
VARCHAR(n) | Variable-length | Up to n characters |
TEXT | Unlimited | Not always indexable |
The Date/Time Types
| Type | Purpose |
|---|---|
DATE | Calendar date |
TIME | Time of day |
TIMESTAMP | Date and time |
TIMESTAMP WITH TIME ZONE | Date, time, and offset |
INTERVAL | Duration |
The Boolean Types
| Database | Type | Values |
|---|---|---|
| PostgreSQL | BOOLEAN | TRUE, FALSE, NULL |
| MySQL | TINYINT(1) | 0, 1, NULL |
| SQL Server | BIT | 1, 0, NULL |
| Oracle 23c+ | BOOLEAN | TRUE, FALSE, NULL |
The Type Selection Guide
| Data | Recommended Type |
|---|---|
| Money | NUMERIC(10, 2) |
| Counts | INTEGER |
| Names | VARCHAR(100) |
| Codes | CHAR(2) or VARCHAR(10) |
| Descriptions | TEXT |
| Dates | DATE |
| Timestamps | TIMESTAMP WITH TIME ZONE |
| Flags | BOOLEAN |
Best Practices
✅ Do This:
-- Use NUMERIC for money
price NUMERIC(10, 2) -- ✅-- Use VARCHAR for variable-length text
name VARCHAR(100) -- ✅-- Use TIMESTAMP WITH TIME ZONE for global timestamps
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- ✅-- Use BOOLEAN for true/false
is_active BOOLEAN NOT NULL DEFAULT TRUE -- ✅-- Use CHECK constraints with data types
price NUMERIC(10, 2) CHECK (price >= 0) -- ✅❌ Don’t Do This:
-- Don't use FLOAT for money
price FLOAT -- rounding errors -- ❌-- Don't use VARCHAR without a length limit
name VARCHAR -- implementation-dependent -- ❌-- Don't use CHAR for variable-length data
name CHAR(100) -- pads with spaces -- ❌-- Don't use TINYINT(1) for new MySQL schemas
is_active TINYINT(1) -- use BOOLEAN synonym -- ⚠️Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Rounding errors | FLOAT for money | Use NUMERIC |
| Trailing spaces | CHAR padding | Use VARCHAR |
| Time zone confusion | TIMESTAMP without zone | Use TIMESTAMP WITH TIME ZONE |
| Value out of range | SMALLINT for large counts | Use INTEGER or BIGINT |
| Boolean portability | MySQL TINYINT | Use BOOLEAN synonym |
Real-World Examples
1. Integer
product_id SERIAL PRIMARY KEY2. Exact Decimal
price NUMERIC(10, 2)3. Approximate Float
weight_kg REAL4. Fixed Character
state CHAR(2)5. Variable Character
name VARCHAR(100)6. Text
description TEXT7. Date
birth_date DATE8. Timestamp with Zone
created_at TIMESTAMP WITH TIME ZONE9. Boolean
is_active BOOLEAN DEFAULT TRUE10. Interval
created_at + INTERVAL '30 days'Visual
The Numeric Type Hierarchy
┌──────────────────────────────────────────────┐
│ NUMERIC TYPES │
│ │
│ Integers: │
│ SMALLINT (2 bytes) │
│ INTEGER (4 bytes) │
│ BIGINT (8 bytes) │
│ SERIAL (auto-increment) │
│ │
│ Exact decimals: │
│ NUMERIC(p, s) │
│ DECIMAL(p, s) │
│ │
│ Approximate: │
│ REAL (6 digits) │
│ DOUBLE PRECISION (15 digits) │
│ │
└──────────────────────────────────────────────┘The Character Types
┌──────────────────────────────────────────────┐
│ CHARACTER TYPES │
│ │
│ CHAR(n): │
│ ├─ Fixed length │
│ ├─ Padded with spaces │
│ └─ 'CA' stored as 'CA' (n=2) │
│ │
│ VARCHAR(n): │
│ ├─ Variable length │
│ ├─ No padding │
│ └─ 'California' stored as 'California' │
│ │
│ TEXT: │
│ ├─ Unlimited length │
│ └─ No padding │
│ │
└──────────────────────────────────────────────┘The Date/Time Types
┌──────────────────────────────────────────────┐
│ DATE/TIME TYPES │
│ │
│ DATE: │
│ └─ 2026-10-01 │
│ │
│ TIMESTAMP: │
│ └─ 2026-10-01 12:00:00 │
│ │
│ TIMESTAMP WITH TIME ZONE: │
│ └─ 2026-10-01 12:00:00+00 │
│ │
│ INTERVAL: │
│ └─ '30 days' │
│ │
└──────────────────────────────────────────────┘The Boolean Implementation
┌──────────────────────────────────────────────┐
│ BOOLEAN BY DATABASE │
│ │
│ PostgreSQL: │
│ BOOLEAN → TRUE, FALSE, NULL │
│ │
│ MySQL: │
│ BOOLEAN → TINYINT(1) → 1, 0, NULL │
│ │
│ SQL Server: │
│ BIT → 1, 0, NULL │
│ │
│ Oracle (23c+): │
│ BOOLEAN → TRUE, FALSE, NULL │
│ │
└──────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| Integer types | SMALLINT, INTEGER, BIGINT, SERIAL |
| Exact decimal | NUMERIC(p, s), DECIMAL(p, s) |
| Approximate | REAL, DOUBLE PRECISION |
| Character types | CHAR(n), VARCHAR(n), TEXT |
| Date/time types | DATE, TIME, TIMESTAMP, INTERVAL |
| Time zone | TIMESTAMP WITH TIME ZONE |
| Boolean | BOOLEAN (PostgreSQL), TINYINT(1) (MySQL), BIT (SQL Server) |
| Money | NUMERIC(10, 2) |
| Global timestamps | TIMESTAMP WITH TIME ZONE |
| True/false | BOOLEAN |
Key takeaways:
- Numeric types are divided into integers, exact decimals, and approximate floats. Integers are fast and precise.
NUMERICis exact but slower.FLOATis fast but approximate. UseNUMERICfor money,INTEGERfor counts, andFLOATfor scientific measurements . - Character types differ in length behavior.
CHAR(n)pads with spaces and has semantically insignificant trailing spaces.VARCHAR(n)stores only the characters used.TEXTstores unlimited-length strings . - Date and time types represent points in time and durations.
DATEstores a calendar date.TIMESTAMPstores a date and time.TIMESTAMP WITH TIME ZONEstores the offset.INTERVALstores a duration . - Boolean types are not universal. PostgreSQL has a native
BOOLEAN. MySQL usesTINYINT(1). SQL Server usesBIT. Oracle 23c introducedBOOLEAN. - The data type is the first layer of validation. The database checks whether the value fits the column’s type before any constraint runs. A value that does not fit is rejected.
- The choice of data type affects precision, performance, and correctness.
NUMERICis slower thanINTEGERbut exact.VARCHARsaves space compared toCHARfor variable-length data.TIMESTAMP WITH TIME ZONEis larger thanDATEbut correct for global applications. - The RDBMS implementation matters. PostgreSQL, MySQL, SQL Server, and Oracle all implement the standard types differently. The differences are most visible in booleans, auto-incrementing integers, and time zone support.
Remember: Every column has a data type. The type defines what the column can hold and how the database treats it. Integers are fast and precise. Exact decimals are slow and precise. Floats are fast and approximate. Fixed-length strings are padded. Variable-length strings are not. Dates are calendar dates. Timestamps are points in time. Booleans are true or false. The choice is not just about storage. It is about what the data means and what operations it needs to support.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!