Data Types
Understanding SQL data types across databases
Every column in a database has a data type that determines what kind of values it can store. Understanding data types helps you write better queries and avoid errors.
Numeric Types
| Type | Description | Example |
|---|---|---|
| INTEGER / INT | Whole numbers | 42, -17, 0 |
| BIGINT | Large whole numbers | 9223372036854775807 |
| DECIMAL / NUMERIC | Exact decimals (money) | 19.99, 1234.56 |
| FLOAT / REAL | Approximate decimals | 3.14159, 2.5e10 |
| BOOLEAN | True/False | TRUE, FALSE |
Text Types
| Type | Description | Use Case |
|---|---|---|
| CHAR(n) | Fixed-length string | Country codes (US, UK) |
| VARCHAR(n) | Variable-length string | Names, emails |
| TEXT | Unlimited text | Descriptions, articles |
Date & Time Types
| Type | Format | Example |
|---|---|---|
| DATE | YYYY-MM-DD | '2024-01-15' |
| TIME | HH:MM:SS | '14:30:00' |
| DATETIME / TIMESTAMP | Date + Time | '2024-01-15 14:30:00' |
Type Conversion
-- CAST syntax (standard SQL)
SELECT CAST(price AS INTEGER) FROM products;
SELECT CAST('2024-01-15' AS DATE);
-- PostgreSQL specific
SELECT price::integer FROM products;
-- Convert to string
SELECT CAST(user_id AS VARCHAR) FROM orders;SQLite Note: SQLite uses dynamic typing. It stores data as: NULL, INTEGER, REAL, TEXT, or BLOB.