13-postgresql-data-types.sql

-- ============================================================
-- SQL Masterclass — Chapter 13: PostgreSQL Data Types & Casting
-- ============================================================
-- 🟣 POSTGRESQL SPECIFIC
--
-- In this chapter you will learn:
--   • PostgreSQL-specific type casting (::)
--   • NUMERIC and precision control
--   • TEXT vs VARCHAR vs CHAR
--   • DATE, TIMESTAMP, INTERVAL
--   • BOOLEAN type
--   • Array data types
--   • JSONB basics
--   • Type conversion gotchas
-- ============================================================
-- ⚠️ This chapter requires PostgreSQL. It will NOT work in SQLite.
-- ============================================================


-- ============================================================
-- 13.1  TYPE CASTING WITH ::
-- ============================================================
-- PostgreSQL uses :: as a shorthand for CAST()

-- CAST syntax (ANSI standard)
SELECT CAST('123.45' AS NUMERIC);

-- PostgreSQL shorthand (preferred)
SELECT '123.45'::NUMERIC;
SELECT '2024-01-15'::DATE;
SELECT '42'::INTEGER;

-- Cast in a real query
SELECT
    order_id,
    price::NUMERIC(10,2) AS price_exact,
    price::INTEGER AS price_int
FROM order_items
LIMIT 10;

-- Date casting from text
SELECT
    order_id,
    order_purchase_timestamp::DATE AS order_date,
    order_purchase_timestamp::TIME AS order_time
FROM orders
LIMIT 10;


-- ============================================================
-- 13.2  NUMERIC PRECISION
-- ============================================================
-- NUMERIC(precision, scale) gives exact arithmetic.
-- FLOAT/REAL are approximate and can cause rounding errors!

-- Floating-point issue:
SELECT 0.1::FLOAT + 0.2::FLOAT;
-- Result: 0.30000000000000004 (not exact!)

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

-- ROUND requires NUMERIC in PostgreSQL
SELECT
    order_id,
    price,
    ROUND(price::NUMERIC, 0) AS rounded_price,
    ROUND(price::NUMERIC, -1) AS rounded_to_tens
FROM order_items
LIMIT 10;

-- Precise percentage calculation
SELECT
    payment_type,
    COUNT(*) AS cnt,
    ROUND(
        100.0 * COUNT(*)::NUMERIC / SUM(COUNT(*)) OVER (),
        2
    ) AS pct
FROM order_payments
GROUP BY payment_type
ORDER BY pct DESC;


-- ============================================================
-- 13.3  TEXT vs VARCHAR vs CHAR
-- ============================================================
-- TEXT: unlimited length (preferred in PostgreSQL)
-- VARCHAR(n): limited to n characters
-- CHAR(n): fixed-length, padded with spaces

-- Check actual stored lengths
SELECT
    customer_state,
    LENGTH(customer_state) AS state_length,  -- always 2
    customer_city,
    LENGTH(customer_city)  AS city_length     -- varies
FROM customers
LIMIT 10;


-- ============================================================
-- 13.4  DATE, TIMESTAMP, and INTERVAL
-- ============================================================

-- Current date/time functions
SELECT
    CURRENT_DATE          AS today,
    CURRENT_TIMESTAMP     AS right_now,
    NOW()                 AS also_right_now,
    CURRENT_TIME          AS time_only;

-- Date arithmetic with INTERVAL
SELECT
    CURRENT_DATE                          AS today,
    CURRENT_DATE + INTERVAL '7 days'      AS next_week,
    CURRENT_DATE - INTERVAL '1 month'     AS last_month,
    CURRENT_DATE + INTERVAL '1 year'      AS next_year;

-- Delivery time using date subtraction (PostgreSQL native)
SELECT
    order_id,
    order_purchase_timestamp::DATE AS purchase_date,
    order_delivered_customer_date::DATE AS delivery_date,
    order_delivered_customer_date::DATE - order_purchase_timestamp::DATE AS delivery_days,
    AGE(order_delivered_customer_date::TIMESTAMP,
        order_purchase_timestamp::TIMESTAMP) AS delivery_interval
FROM orders
WHERE order_status = 'delivered'
  AND order_delivered_customer_date IS NOT NULL
LIMIT 10;

-- Average delivery time with proper date arithmetic
SELECT
    c.customer_state,
    COUNT(*) AS num_orders,
    AVG(o.order_delivered_customer_date::DATE -
        o.order_purchase_timestamp::DATE) AS avg_delivery_days
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_status = 'delivered'
  AND o.order_delivered_customer_date IS NOT NULL
GROUP BY c.customer_state
ORDER BY avg_delivery_days
LIMIT 10;


-- ============================================================
-- 13.5  BOOLEAN TYPE
-- ============================================================

-- Create boolean expressions
SELECT
    order_id,
    order_status,
    order_status = 'delivered'                    AS is_delivered,
    order_delivered_customer_date IS NOT NULL      AS has_delivery_date,
    order_delivered_customer_date::DATE <=
        order_estimated_delivery_date::DATE       AS delivered_on_time
FROM orders
WHERE order_status = 'delivered'
LIMIT 10;

-- Filter with boolean expressions
SELECT COUNT(*) AS late_deliveries
FROM orders
WHERE order_status = 'delivered'
  AND (order_delivered_customer_date::DATE >
       order_estimated_delivery_date::DATE) = TRUE;


-- ============================================================
-- 13.6  ARRAYS
-- ============================================================
-- PostgreSQL supports array columns and operations.

-- Create arrays from grouped data
SELECT
    o.order_id,
    ARRAY_AGG(DISTINCT p.product_category_name) AS categories
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.product_category_name IS NOT NULL
GROUP BY o.order_id
HAVING COUNT(DISTINCT p.product_category_name) > 1
LIMIT 10;

-- Array functions
SELECT
    ARRAY[1, 2, 3]                    AS sample_array,
    ARRAY_LENGTH(ARRAY[1, 2, 3], 1)   AS arr_length,
    1 = ANY(ARRAY[1, 2, 3])           AS contains_one,
    4 = ANY(ARRAY[1, 2, 3])           AS contains_four;


-- ============================================================
-- 13.7  JSONB BASICS
-- ============================================================
-- JSONB is a binary JSON format — extremely useful for
-- semi-structured data.

-- Create JSON from query results
SELECT
    order_id,
    jsonb_build_object(
        'status', order_status,
        'purchased', order_purchase_timestamp,
        'delivered', order_delivered_customer_date
    ) AS order_json
FROM orders
LIMIT 5;

-- Aggregate into JSON arrays
SELECT
    o.order_id,
    jsonb_agg(
        jsonb_build_object(
            'product', oi.product_id,
            'price', oi.price,
            'freight', oi.freight_value
        )
    ) AS items_json
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id
HAVING COUNT(*) > 1
LIMIT 5;


-- ============================================================
-- 13.8  TYPE CONVERSION GOTCHAS
-- ============================================================

-- Integer floor division problem
SELECT 5 / 2;              -- Returns 2 (integer division!)
SELECT 5 / 2.0;            -- Returns 2.5 (correct!)
SELECT 5::NUMERIC / 2;     -- Returns 2.5 (correct!)

-- Date from string — be explicit about format
SELECT '2024-01-15'::DATE;         -- ISO format (always works)
SELECT '01/15/2024'::DATE;         -- Depends on locale! Risky!

-- NULL arithmetic
SELECT 5 + NULL;            -- Returns NULL!
SELECT COALESCE(NULL, 0);   -- Returns 0 (safe default)

-- Safe division (avoid divide-by-zero)
SELECT NULLIF(0, 0);               -- Returns NULL
SELECT 100.0 / NULLIF(0, 0);      -- Returns NULL instead of error


-- ============================================================
-- EXERCISES
-- ============================================================

-- Exercise 1: Calculate the precise average order value
--             (payment_value) rounded to 2 decimal places using
--             NUMERIC casting.

-- Exercise 2: Find orders where delivery took longer than 
--             30 days using DATE subtraction (not julianday).

-- Exercise 3: Create a query that returns the current date,
--             30 days ago, and 90 days from now using INTERVAL.

-- Exercise 4: Use ARRAY_AGG to show each order with an array
--             of all payment types used for that order.


-- ============================================================
-- SOLUTIONS
-- ============================================================

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

-- Exercise 2
SELECT
    order_id,
    order_purchase_timestamp::DATE AS purchase_date,
    order_delivered_customer_date::DATE AS delivery_date,
    (order_delivered_customer_date::DATE -
     order_purchase_timestamp::DATE) AS days_to_deliver
FROM orders
WHERE order_status = 'delivered'
  AND (order_delivered_customer_date::DATE -
       order_purchase_timestamp::DATE) > 30
ORDER BY days_to_deliver DESC
LIMIT 15;

-- Exercise 3
SELECT
    CURRENT_DATE AS today,
    CURRENT_DATE - INTERVAL '30 days' AS thirty_days_ago,
    CURRENT_DATE + INTERVAL '90 days' AS ninety_days_ahead;

-- Exercise 4
SELECT
    order_id,
    ARRAY_AGG(DISTINCT payment_type) AS payment_methods,
    SUM(payment_value) AS total_value
FROM order_payments
GROUP BY order_id
HAVING COUNT(DISTINCT payment_type) > 1
LIMIT 10;