PostgreSQL Data Types Explained

# CHAPTER 5

PostgreSQL Data Types Explained #

1. Introduction #

2. Learning Objectives #

• Differentiate between Numeric types (INTEGER, DECIMAL).

• Differentiate between String types (VARCHAR, TEXT).

• Store temporal data using DATE and TIMESTAMP.

• Understand the SERIAL auto-incrementing type.

• Understand advanced Postgres types like UUID and JSONB.

3. Numeric Data Types #

• INTEGER (or INT): Stores standard whole numbers from -2 billion to +2 billion.

• SMALLINT / BIGINT: Use SMALLINT for tiny numbers to save hard drive space. Use BIGINT for massive numbers (like global view counts).

• SERIAL: This is a special Postgres type. It creates an INTEGER column, but automatically sets up a sequence generator behind the scenes so the number auto-increments (1, 2, 3...) on every new row. Perfect for Primary Keys!

• DECIMAL(M, D) or NUMERIC(M, D): Stores exact decimal values. M is total digits, D is digits after the dot. NUMERIC(10, 2) means 10 total digits, 2 after the dot (e.g., 12345678.99). Always use this for Financial Data/Money!

4. String (Text) Data Types #

• VARCHAR(n): Variable-length string. The n specifies the maximum characters allowed. VARCHAR(50) blocks strings longer than 50 characters.

• CHAR(n): Fixed-length string. If you specify CHAR(2) and insert "A", it pads the rest with a blank space. Good for 2-letter Country Codes (US, UK).

• TEXT: Variable-length string with unlimited length. In PostgreSQL, TEXT is highly optimized. Many developers use TEXT for everything (even emails) unless they strictly *need* to enforce a length limit with VARCHAR.

5. Date and Time Data Types #

• DATE: Stores just the date (YYYY-MM-DD). Good for birthdays.

• TIME: Stores just the time (HH:MM:SS).

• TIMESTAMP: Combines Date and Time (YYYY-MM-DD HH:MM:SS). Good for "Account Created At" fields.

• TIMESTAMPTZ: Timestamp *with Time Zone*. Always use this in production! It automatically converts the time to UTC before saving, preventing massive bugs when users log in from different countries.

6. Boolean Data Type #

• BOOLEAN: Stores TRUE, FALSE, or NULL.

• *Best for:* isactive, isadmin, has_paid.

7. Advanced PostgreSQL Types (The Secret Weapons) #

• UUID: A Universally Unique Identifier (e.g., 550e8400-e29b-41d4-a716-446655440000). It is a mathematically random 128-bit string. Modern apps use UUIDs instead of SERIAL integers for Primary Keys so hackers cannot guess how many users the app has (e.g., user/5 vs user/550e...).

• JSONB: The "JSON Binary" type. It allows you to store entire JSON documents (like a NoSQL MongoDB database!) directly inside a relational PostgreSQL column, and you can even search/index the keys inside the JSON!

8. Mini Project: A Modern User Table #

CREATE TABLE employees (
    -- Modern apps often use UUIDs, but we will use SERIAL for simplicity here
    emp_id SERIAL PRIMARY KEY,                   
    first_name VARCHAR(50),                      
    email TEXT,                                  -- Unlimited length
    salary NUMERIC(10, 2),                       -- Exact money math!
    hire_date DATE,                              
    is_manager BOOLEAN,                          
    settings JSONB,                              -- Store unstructured app settings!
    created_at TIMESTAMPTZ DEFAULT NOW()         -- Auto-saves the exact UTC time
);

9. Common Mistakes #

• Using FLOAT or REAL for Money: Never do this! Floating-point numbers are mathematical approximations. If you do heavy math on them, they generate microscopic rounding errors (e.g., $10.00 becomes $9.9999999). Always use NUMERIC for currency.

• Wasting Space with BIGINT: Using a BIGINT (8 bytes) to store a person's age is a waste of RAM when a SMALLINT (2 bytes) works perfectly.

10. Best Practices #

• Prefer TEXT over VARCHAR: In older databases, VARCHAR was faster. In modern PostgreSQL, there is absolutely zero performance difference between VARCHAR(255) and TEXT. Unless you have a strict business rule to enforce a character limit, just use TEXT.

11. Exercises #

1. 1. What data type should you use to store an exact financial transaction of $1,500.50?

1. 2. Write the SQL syntax to create a column named is_verified that holds a True or False value.

12. SQL Challenges #

CREATE TABLE logs (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the difference between SERIAL and UUID for a Primary Key. What are the security benefits of using a UUID in a REST API?

• Q: Why is the NUMERIC (or DECIMAL) data type strictly required for financial data, rather than using FLOAT or DOUBLE PRECISION?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #