About Database Data Types

Choosing the right data type is crucial for database performance, storage efficiency, and data integrity. Different database systems offer similar but not identical data types.

Data Type Selection Guidelines

Numeric Data

• Small Integers (0-255): Use TINYINT or SMALLINT
• Standard Integers: Use INT or INTEGER
• Large Integers: Use BIGINT for values over 2 billion
• Money/Prices: Use DECIMAL(10,2) to avoid rounding errors
• Scientific Data: Use FLOAT or DOUBLE for approximate values

String Data

• Fixed Length (e.g., codes): Use CHAR(n)
• Variable Length (up to 255): Use VARCHAR(n)
• Long Text: Use TEXT or CLOB
• Unicode: Use NVARCHAR or specify UTF-8 charset

Date and Time

• Date Only: Use DATE
• Time Only: Use TIME
• Date + Time: Use DATETIME or TIMESTAMP
• Auto-Update Timestamps: Use TIMESTAMP with default CURRENT_TIMESTAMP

Storage Size Comparison

Data Type	Storage Size	Range
TINYINT	1 byte	0 to 255 (unsigned) or -128 to 127
SMALLINT	2 bytes	0 to 65,535 (unsigned) or -32,768 to 32,767
INT	4 bytes	0 to 4,294,967,295 (unsigned) or -2,147,483,648 to 2,147,483,647
BIGINT	8 bytes	0 to 18,446,744,073,709,551,615 (unsigned)
CHAR(n)	n bytes	Fixed length
VARCHAR(n)	Variable	Up to n characters
DATE	3-4 bytes	Varies by database
TIMESTAMP	4-8 bytes	Varies by database

Common Migration Mappings

MySQL to PostgreSQL

MySQL	PostgreSQL
TINYINT(1)	BOOLEAN
TINYINT	SMALLINT
DATETIME	TIMESTAMP
BLOB	BYTEA
AUTO_INCREMENT	SERIAL or IDENTITY

SQL Server to PostgreSQL

SQL Server	PostgreSQL
BIT	BOOLEAN
NVARCHAR	VARCHAR
DATETIME2	TIMESTAMP
UNIQUEIDENTIFIER	UUID
IDENTITY	SERIAL or IDENTITY

Best Practices

• Use Appropriate Size: Don't use BIGINT when INT will suffice
• Consider Indexing: Smaller data types index faster
• Use DECIMAL for Money: Avoid FLOAT/DOUBLE for financial calculations
• VARCHAR vs TEXT: Use VARCHAR for known max length, TEXT for unlimited
• NULL vs NOT NULL: Explicitly define NULL handling
• Default Values: Set sensible defaults when possible
• Unicode Support: Plan for international characters from the start

Special Data Types

JSON/JSONB (PostgreSQL)

Store and query JSON data natively with indexing support.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL
);

SELECT data->>'name' FROM products WHERE data->>'category' = 'electronics';

ENUM Types

Define a list of allowed values for a column.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    status ENUM('pending', 'processing', 'shipped', 'delivered') NOT NULL
);

UUID/GUID

Universally unique identifiers for distributed systems.

CREATE TABLE sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Performance Considerations

• Storage: Smaller types use less disk space and memory
• Indexing: Smaller indexes are faster to search
• Sorting: Numeric types sort faster than strings
• Joins: Matching data types perform better in joins
• TEXT vs VARCHAR: Some databases store them differently