SQL Data Types

Lesson content

SQL Data Types Data types define which values a column accepts and affect validation, storage, sorting, and calculations. Example CREATE TABLE products ( id INTEGER PRIMARY KEY, name VARCHAR(120) NOT NULL, price DECIMAL(10, 2), created_at TIMESTAMP ); Key point Use the smallest correct statement, test it with representative data, and verify constraints and performance before production use. Real-life example An online shop stores customers, products, orders, and order items. The schema must prevent duplicate identities, missing required values, and orders that reference customers that do not exist. Advanced example BEGIN; -- Preview the exact target set first. SELECT id FROM orders WHERE status = 'pending'; -- Apply the SQL Data Types operation, verify affected rows, then commit. COMMIT; Expected result The schema or operation satisfies the stated rule, rejects invalid data, and can be verified with a repeatable query. Production check Test with empty, duplicate, null, and boundary values. Use a transaction for related writes. Inspect the execution plan before adding an index. Use parameterized queries for application input. Continue with the PicoStore database This lesson reuses picostore . Relevant tables: customers, products, orders, order_items . Keep the starter rows from the Introduction lesson so results remain comparable. Another practical example ALTER TABLE products ADD COLUMN active BOOLEAN NOT NULL DEFAULT TRUE; SELECT product_id, name, active FROM products; Check the result Run the verification query, compare the returned rows with the starter data, and explain why every included or excluded row is correct. SQL reference catalog: SQL Data Types This catalog covers the practical public SQL surface. Check the exact server-version documentation before using a vendor-specific item. Exact numeric types SMALLINT — small whole numbers INTEGER / INT — ordinary whole numbers BIGINT — large whole numbers DECIMAL(p,s) / NUMERIC(p,s) — exact fixed-point values such as money Approximate numeric types REAL — single-precision approximate number DOUBLE PRECISION — double-precision approximate number FLOAT(p) — precision-based approximate number Character and text types CHAR(n) — fixed-length text VARCHAR(n) — variable-length bounded text TEXT — large variable text; common vendor extension NCHAR / NVARCHAR — national character types, mainly SQL Server/MySQL compatibility Binary types BINARY(n) — fixed-length bytes VARBINARY(n) — variable-length bytes BLOB — large binary object; MySQL BYTEA — variable binary data; PostgreSQL Date and time types DATE — calendar date TIME — time of day TIME WITH TIME ZONE — time with offset support TIMESTAMP — date and time TIMESTAMP WITH TIME ZONE — absolute instant; PostgreSQL timestamptz INTERVAL — duration; PostgreSQL and standard SQL YEAR — year value; MySQL Logical and structured types BOOLEAN / BOOL — true or false JSON — validated JSON document JSONB — binary searchable JSON; PostgreSQL XML — XML document; PostgreSQL/SQL Server UUID — 128-bit identifier; native PostgreSQL ARRAY — typed array; PostgreSQL ENUM — restricted label set; both vendors with different implementations SET — multiple labels; MySQL RANGE / MULTIRANGE — ranges of values; PostgreSQL INET / CIDR / MACADDR — network values; PostgreSQL GEOMETRY / GEOGRAPHY — spatial values with vendor extensions Auto-generated identifiers AUTO_INCREMENT — MySQL integer generation attribute GENERATED ... AS IDENTITY — standard identity syntax supported by PostgreSQL SERIAL / BIGSERIAL — legacy PostgreSQL sequence shorthand SEQUENCE — independent number generator; PostgreSQL and standard SQL Selection rules DECIMAL, not FLOAT — for exact money and accounting values TIMESTAMP WITH TIME ZONE — for absolute events shared across regions VARCHAR/TEXT — for human-readable text INTEGER/BIGINT — for counters and numeric identifiers JSON — only when attributes are genuinely flexible and do not need ordinary relational constraints Easy example Start with a small customer table and retrieve active customers in a predictable order. SELECT customer_id, name, email FROM customers WHERE status = 'active' ORDER BY name; How to verify the easy example Run it with representative input. Confirm the expected output. Try one missing, invalid, or boundary value. Advanced example Use a CTE and a window function to rank customer revenue while keeping the query readable and testable. WITH customer_revenue AS ( SELECT customer_id, SUM(total_amount) AS revenue FROM orders WHERE order_status = 'completed' GROUP BY customer_id ) SELECT customer_id, revenue, DENSE_RANK() OVER (ORDER BY revenue DESC) AS revenue_rank FROM customer_revenue ORDER BY revenue_rank, customer_id; Advanced review Explain the tradeoffs and assumptions. Test failure, scale, security, and recovery behavior. Capture evidence from tests, execution plans, logs, or review output. Additional practical guidance SQL Data Types: MySQL and PostgreSQL Use standard SQL first, then document any MySQL or PostgreSQL-specific syntax. Verify the statement with small sample data, an edge case, and the expected result before using it in production. Required verification Run the simple case. Test a NULL, duplicate, empty, or boundary case where relevant. Confirm the affected rows or query result. Use EXPLAIN for performance-sensitive queries.