How to fix 22013: invalid_preceding_or_following_size in PostgreSQL

-- Example that triggers 22013
SELECT
    order_id,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN -1 PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM orders;
-- ERROR: 22013: invalid_preceding_or_following_size

-- INVALID: negative offset
SELECT
    product_id,
    SUM(sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN -7 PRECEDING AND CURRENT ROW
    )
FROM sales;

-- VALID: use positive offset
SELECT
    product_id,
    SUM(sales) OVER (
        ORDER BY sale_date
        ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
    )
FROM sales;

-- INVALID: dynamic offset from column that might be negative
SELECT
    user_id,
    AVG(score) OVER (
        ORDER BY game_date
        ROWS BETWEEN window_size PRECEDING AND CURRENT ROW
    )
FROM game_scores;

-- VALID: ensure offset is non-negative
SELECT
    user_id,
    AVG(score) OVER (
        ORDER BY game_date
        ROWS BETWEEN GREATEST(window_size, 0) PRECEDING AND CURRENT ROW
    )
FROM game_scores
WHERE window_size IS NOT NULL;

-- INVALID: offset might be NULL
SELECT
    transaction_id,
    SUM(amount) OVER (
        ORDER BY tx_date
        ROWS BETWEEN days_back PRECEDING AND CURRENT ROW
    )
FROM transactions;

-- VALID: provide default for NULL
SELECT
    transaction_id,
    SUM(amount) OVER (
        ORDER BY tx_date
        ROWS BETWEEN COALESCE(days_back, 0) PRECEDING AND CURRENT ROW
    )
FROM transactions;

-- Test with various offset values
SELECT
    id, value,
    AVG(value) OVER (
        ORDER BY id
        ROWS BETWEEN GREATEST(offset_col, 0) PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM test_data
WHERE offset_col IS NOT NULL
ORDER BY id
LIMIT 10;