SQL Data Types
Here is a clear, practical, and complete guide to SQL Data Types, covering all major database engines (MySQL, PostgreSQL, SQL Server, Oracle) with examples and best practices.
✅ What Are SQL Data Types?
SQL data types define what kind of values a column can store, such as:
-
Numbers
-
Text
-
Dates & times
-
Binary data
-
Boolean values
-
JSON
-
Special types (UUID, arrays, spatial, etc.)
Choosing the correct data type is critical for:
-
Performance
-
Storage efficiency
-
Validation
-
Indexing
-
Data consistency
🔹 1. Numeric Data Types
Integer Types
| Type | Description | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| TINYINT | Very small integer | ✔ | ❌ | ✔ (as smallint) | ❌ |
| SMALLINT | Small integer | ✔ | ✔ | ✔ | ✔ |
| INT / INTEGER | Standard integer | ✔ | ✔ | ✔ | ✔ |
| BIGINT | Very large integer | ✔ | ✔ | ✔ | ✔ |
Example:
Decimal / Floating Types
| Type | Description |
|---|---|
| DECIMAL(p,s) | Exact value, great for money |
| NUMERIC(p,s) | Same as DECIMAL |
| FLOAT | Approximate values |
| DOUBLE | Larger approximate values |
Example:
🔹 2. Character (Text) Data Types
| Type | Description |
|---|---|
| CHAR(n) | Fixed-length text |
| VARCHAR(n) | Variable-length text |
| TEXT | Long text |
| MEDIUMTEXT / LONGTEXT (MySQL) | Very long documents |
Example:
🔹 3. Date and Time Data Types
| Type | Description |
|---|---|
| DATE | YYYY-MM-DD |
| TIME | HH:MM:SS |
| DATETIME | Date + time |
| TIMESTAMP | UTC-based, auto-update options |
| INTERVAL (PostgreSQL) | Time spans |
Examples:
🔹 4. Boolean Data
| DB | Boolean Type |
|---|---|
| MySQL | TINYINT(1) (TRUE=1, FALSE=0) |
| PostgreSQL | BOOLEAN |
| SQL Server | BIT |
| Oracle | No BOOLEAN in tables; use NUMBER(1) |
Example:
🔹 5. Binary Data Types
| Type | Description |
|---|---|
| BLOB | Binary Large Object |
| VARBINARY | Variable-length binary |
| BYTEA (PostgreSQL) | Binary data |
| RAW (Oracle) | Raw binary |
Example:
🔹 6. JSON Data Types
| DB | JSON Support |
|---|---|
| MySQL | JSON |
| PostgreSQL | JSON, JSONB (binary optimized) |
| SQL Server | Stores JSON in NVARCHAR but has JSON functions |
| Oracle | JSON data type (21c+) |
Example:
PostgreSQL example:
🔹 7. UUID / GUID Types
| DB | Type |
|---|---|
| PostgreSQL | UUID |
| MySQL | CHAR(36) or BINARY(16) |
| SQL Server | UNIQUEIDENTIFIER |
| Oracle | RAW(16) |
Example:
🔹 8. Spatial / Geographic Types
Useful for GIS applications.
| DB | Example Types |
|---|---|
| MySQL | POINT, POLYGON |
| PostgreSQL (PostGIS) | geometry, geography |
| SQL Server | GEOMETRY, GEOGRAPHY |
| Oracle | SDO_GEOMETRY |
Example:
🔹 9. Array Types (PostgreSQL Only)
PostgreSQL supports arrays natively:
🔹 10. XML Data Types
-
SQL Server: XML
-
PostgreSQL: xml
-
Oracle: XMLTYPE
Example:
🔥 Full Example Table With Data Types
🧠 How to Choose the Best Data Type
✔ Choose smallest type that fits the use case
✔ Prefer INT over BIGINT unless needed
✔ Always use DECIMAL for money
✔ Use VARCHAR instead of TEXT when size is known
✔ Use TIMESTAMP for time data
✔ For globally unique IDs → use UUID
✔ Avoid storing large files in DB → use object storage
🧠 Data Type Differences Across Engines (Quick Summary)
| Feature | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|
| Boolean | TINYINT(1) | BOOLEAN | BIT | NUMBER(1) |
| JSON | Yes | JSON + JSONB | Yes (text-based) | Yes |
| UUID | via CHAR/BINARY | Native UUID | UNIQUEIDENTIFIER | RAW(16) |
| Arrays | ❌ | ✔ | ❌ | ❌ |
| Full GIS | Medium | ✔✔✔ PostGIS | Medium | Strong |
