13-postgresql-data-types.md

Chapter 13: PostgreSQL Data Types & Casting

While standard SQL covers the basics, PostgreSQL offers a significantly richer set of data types and powerful shorthand for data manipulation. This chapter focuses on PostgreSQL-specific features that make it a favorite for data engineers.

Warning

The examples in this chapter are specific to PostgreSQL and may not work in other databases like SQLite or MySQL.

13.1 Type Casting with the :: Shorthand

PostgreSQL allows you to cast data types using the double-colon :: operator, which is much more concise than the standard CAST() function.

SELECT '123.45'::NUMERIC;
SELECT '2024-01-15'::DATE;
SELECT price::NUMERIC(10,2) FROM order_items;

13.2 Exact vs. Approximate Numeric Types

One of PostgreSQL's strengths is the NUMERIC (or DECIMAL) type, which provides exact arithmetic. FLOAT and REAL are approximate and can lead to precision errors in financial calculations.

-- Floating-point (approximate)
SELECT 0.1::FLOAT + 0.2::FLOAT; -- 0.30000000000000004

-- NUMERIC (exact)
SELECT 0.1::NUMERIC + 0.2::NUMERIC; -- 0.3

13.3 Advanced Date Arithmetic with INTERVAL

Postgres makes date manipulation incredibly intuitive using the INTERVAL type.

SELECT
    CURRENT_DATE + INTERVAL '7 days' AS next_week,
    CURRENT_DATE - INTERVAL '1 month' AS last_month;

-- Detailed delivery interval
SELECT
    AGE(delivered_date, purchased_date) AS delivery_interval
FROM order_analysis;

13.4 Arrays and JSONB

PostgreSQL treats arrays and JSON as first-class citizens, allowing you to store and query complex, semi-structured data directly.

-- Aggregate into a JSON array of objects
SELECT
    order_id,
    jsonb_agg(
        jsonb_build_object('product', product_id, 'price', price)
    ) AS items_json
FROM order_items
GROUP BY order_id;

13.5 Boolean Logic

PostgreSQL has a dedicated BOOLEAN type. You can perform logic directly in your SELECT or WHERE clauses.

SELECT
    order_id,
    order_delivered_customer_date::DATE <= order_estimated_delivery_date::DATE AS on_time
FROM orders;

---

Exercises

1. Calculate the precise average order value, rounded to 2 decimal places, using NUMERIC casting.
2. Find orders where the delivery took longer than 30 days using direct DATE subtraction.
3. Return the current date, 30 days ago, and 90 days from now using INTERVAL.
4. Use ARRAY_AGG to show each order with an array of all payment types used.

Solutions

-- Exercise 1
SELECT ROUND(AVG(payment_value)::NUMERIC, 2) FROM order_payments;

-- Exercise 2
SELECT order_id, (order_delivered_customer_date::DATE - order_purchase_timestamp::DATE) AS days FROM orders WHERE (order_delivered_customer_date::DATE - order_purchase_timestamp::DATE) > 30;

-- Exercise 3
SELECT CURRENT_DATE, CURRENT_DATE - INTERVAL '30 days', CURRENT_DATE + INTERVAL '90 days';

-- Exercise 4
SELECT order_id, ARRAY_AGG(DISTINCT payment_type) FROM order_payments GROUP BY 1;