TECH **SQL for Data Analysis: Casting Data Types (CAST, ::) & COALESCE, NULLIF – Zero-Fluff Study Guide**

SQL for Data Analysis: Casting Data Types (CAST, ::) & COALESCE, NULLIF – Zero-Fluff Study Guide

1. What This Is & Why It Matters

You’re analyzing sales data in a PostgreSQL database. Your query joins orders (stored as VARCHAR) with customers (stored as INTEGER). The query fails with:

ERROR: operator does not exist: character varying = integer

Or worse—it doesn’t fail, but silently returns wrong results because '123' (string) and 123 (integer) are treated as different values.

This is where casting comes in.

• Casting (CAST, ::) forces data into the correct type so operations (joins, math, comparisons) work as expected.
• COALESCE replaces NULL with a fallback value (e.g., 0 for missing sales).
• NULLIF prevents division-by-zero errors by converting problematic values to NULL.

Why this matters in production:
- Broken reports: If you don’t cast, aggregations (SUM, AVG) may fail or return nonsense.
- Performance hits: Implicit casting (where the database guesses types) slows down queries.
- Data integrity: NULL values can skew results if not handled explicitly.

Real-world scenario:
You’re building a dashboard for a retail client. The revenue column is stored as TEXT (because legacy systems exported it that way). You need to: 1. Cast it to DECIMAL to calculate totals.
2. Replace NULL with 0 to avoid gaps in charts.
3. Use NULLIF to prevent errors when dividing by zero (e.g., revenue / units_sold).

2. Core Concepts & Components

1. CAST(value AS type)

• Definition: Explicitly converts a value from one data type to another.
• Production insight: Always prefer explicit casting over implicit (e.g., WHERE id = '123' vs. WHERE id = CAST('123' AS INTEGER)). Implicit casting can fail silently or slow down queries.

2. value::type (PostgreSQL shorthand)

• Definition: Same as CAST, but shorter syntax (e.g., '123'::INTEGER).
• Production insight: Faster to type, but less portable (works in PostgreSQL, not MySQL/SQL Server).

3. COALESCE(value1, value2, ...)

• Definition: Returns the first non-NULL value in a list. If all are NULL, returns NULL.
• Production insight: Use to replace NULL with defaults (e.g., COALESCE(revenue, 0)). Critical for dashboards where NULL breaks visualizations.

4. NULLIF(value1, value2)

• Definition: Returns NULL if value1 = value2; otherwise returns value1.
• Production insight: Prevents errors like division by zero (e.g., revenue / NULLIF(units_sold, 0)).

5. Implicit Casting (Danger Zone)

• Definition: The database automatically converts types (e.g., '123' + 1 → 124 in PostgreSQL).
• Production insight: Avoid. It’s unpredictable across databases and can cause silent failures.

6. Common Data Types for Casting

3. Step-by-Step Hands-On

Prerequisites

• A PostgreSQL database (use ElephantSQL for free or docker run -p 5432:5432 -e POSTGRES_PASSWORD=pass postgres).
• A table with messy data (run this first):

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_name VARCHAR(100),
    revenue TEXT,  -- Stored as text (legacy system)
    units_sold TEXT,  -- Stored as text
    sale_date TEXT  -- Stored as 'YYYY-MM-DD'
);

INSERT INTO sales (product_name, revenue, units_sold, sale_date) VALUES
    ('Widget A', '100.50', '5', '2023-01-15'),
    ('Widget B', '200.00', '0', '2023-01-16'),  -- units_sold = 0 (division risk)
    ('Widget C', NULL, '3', '2023-01-17'),  -- NULL revenue
    ('Widget D', '50.25', NULL, '2023-01-18');  -- NULL units_sold

Task: Clean and Analyze the Data

1. Cast revenue to DECIMAL and units_sold to INTEGER:
   sql
SELECT
product_name,
CAST(revenue AS DECIMAL) AS revenue_decimal,
units_sold::INTEGER AS units_sold_int
FROM sales;
   Output:
   product_name | revenue_decimal | units_sold_int
-------------+-----------------+---------------
Widget A | 100.50 | 5
Widget B | 200.00 | 0
Widget C | NULL | 3
Widget D | 50.25 | NULL

2. Replace NULL revenue with 0 using COALESCE:
   sql
SELECT
product_name,
COALESCE(CAST(revenue AS DECIMAL), 0) AS revenue_clean
FROM sales;
   Output:
   product_name | revenue_clean
-------------+--------------
Widget A | 100.50
Widget B | 200.00
Widget C | 0.00
Widget D | 50.25

3. Calculate revenue per unit, avoiding division by zero with NULLIF:
   sql
SELECT
product_name,
CAST(revenue AS DECIMAL) / NULLIF(units_sold::INTEGER, 0) AS revenue_per_unit
FROM sales;
   Output:
   product_name | revenue_per_unit
-------------+------------------
Widget A | 20.10
Widget B | NULL -- Division by zero avoided
Widget C | NULL -- NULL revenue
Widget D | NULL -- NULL units_sold

4. Combine all fixes in one query:
   sql
SELECT
product_name,
COALESCE(CAST(revenue AS DECIMAL), 0) AS revenue_clean,
COALESCE(units_sold::INTEGER, 0) AS units_sold_clean,
COALESCE(CAST(revenue AS DECIMAL), 0) /
NULLIF(COALESCE(units_sold::INTEGER, 0), 0) AS revenue_per_unit
FROM sales;
   Output:
   product_name | revenue_clean | units_sold_clean | revenue_per_unit
-------------+---------------+------------------+------------------
Widget A | 100.50 | 5 | 20.10
Widget B | 200.00 | 0 | NULL -- Still NULL (0/0)
Widget C | 0.00 | 3 | 0.00
Widget D | 50.25 | 0 | NULL -- 50.25/0

4. ? Production-Ready Best Practices

Security

• Never cast user input directly: Sanitize first (e.g., WHERE id = CAST(user_input AS INTEGER) is unsafe; use parameterized queries).
• Log casting errors: Use TRY_CAST (SQL Server) or CASE WHEN ... THEN ... ELSE NULL END to handle failures gracefully.

Performance

• Cast early: Filter or join on casted columns to avoid full-table scans.
  ```sql -- Bad: Casts after filtering SELECT * FROM sales WHERE CAST(revenue AS DECIMAL) > 100;

-- Good: Cast before filtering (if possible) SELECT * FROM sales WHERE revenue::DECIMAL > 100; `` - Avoid casting inJOIN` conditions: Pre-cast columns in a CTE or subquery.

Reliability

• Use COALESCE for defaults: Always handle NULL in aggregations (e.g., SUM(COALESCE(revenue, 0))).
• Document casting logic: Add comments like -- revenue::DECIMAL(10,2) for currency calculations.

Observability

• Monitor casting errors: Log queries that fail due to type mismatches (e.g., ERROR: invalid input syntax for type integer).
• Test edge cases: Try casting 'abc' to INTEGER to see how your database handles it.

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. Casting syntax:

2. "Which of these casts `'2023-01-01' to a DATE?

   
   
   • A) CAST('2023-01-01' AS DATE)
   • B) '2023-01-01'::DATE
   • C) TO_DATE('2023-01-01')
   • D) All of the above Answer: D (PostgreSQL supports all three).

3. COALESCE vs. NULLIF:

4. "You need to replace NULL with 0 in a column. Which function do you use?"

   
   
   • A) NULLIF
   • B) COALESCE
   • C) ISNULL
   • D) CASE WHEN Answer: B (COALESCE(revenue, 0)).

5. Division by zero:

6. "How do you prevent revenue / units_sold from failing when units_sold = 0?"
   • A) revenue / (units_sold + 1)
   • B) revenue / NULLIF(units_sold, 0)
   • C) revenue / COALESCE(units_sold, 1)
   • D) CASE WHEN units_sold = 0 THEN NULL ELSE revenue / units_sold END Answer: B or D (both work; NULLIF is shorter).

Key Traps

• COALESCE returns the first non-NULL, not the first value. COALESCE(NULL, 1, 2) returns 1, not NULL.
• NULLIF returns NULL only if values match. NULLIF(5, 5) → NULL, but NULLIF(5, 6) → 5.
• Casting truncates, not rounds. '3.99'::INTEGER → 3 (not 4).

7. ? Hands-On Challenge

Challenge:
You have a table employees with a salary column stored as TEXT. Some values are 'N/A' (not a number). Write a query to: 1. Cast salary to DECIMAL.
2. Replace 'N/A' with NULL.
3. Calculate the average salary, ignoring NULL values.

Solution:

SELECT AVG(
    CASE
        WHEN salary = 'N/A' THEN NULL
        ELSE CAST(salary AS DECIMAL)
    END
) AS avg_salary
FROM employees;

Why it works:
- CASE converts 'N/A' to NULL before casting.
- AVG ignores NULL values by default.

8. ? Rapid-Reference Crib Sheet

9. ? Where to Go Next

1. PostgreSQL Data Type Formatting – Official docs on casting rules.
2. SQLZoo: COALESCE and NULLIF – Interactive exercises.
3. Mode Analytics: SQL Casting Guide – Practical examples.
4. Book: SQL for Data Analysis by O’Reilly – Chapter 4 (Data Cleaning).