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 | Yes | No | (as smallint) | No |
| SMALLINT | Small integer | Yes | Yes | Yes | Yes |
| INT / INTEGER | Standard integer | Yes | Yes | Yes | Yes |
| BIGINT | Very large integer | Yes | Yes | Yes | Yes |
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 | No | Yes | No | No |
| Full GIS | Medium | PostGIS | Medium | Strong |
