DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22015 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22015: interval field overflow

PostgreSQL error code 22015 (interval_field_overflow) occurs when an INTERVAL value exceeds the internal storage limits of PostgreSQL's interval type. Internally, intervals are stored as months, days, and microseconds (as a 64-bit integer), allowing a range of approximately ±292,271 years. Any operation that pushes beyond this boundary will immediately raise this error.

Top 3 Causes

1. Converting Extremely Large Numbers to INTERVAL

The most common cause is attempting to cast an astronomically large numeric value directly into an interval type. Since microseconds are stored as int64, values representing more than ~292,271 years in microseconds will overflow instantly.

-- This will fail
SELECT '9999999999999 seconds'::interval;
-- ERROR:  interval field overflow

-- Safe approach: validate before casting
SELECT
    CASE
        WHEN abs(val) <= 9223372036
        THEN (val || ' seconds')::interval
        ELSE NULL
    END AS safe_interval
FROM (VALUES (3600), (86400), (9999999999999)) AS t(val);

-- Wrap in a function with exception handling
CREATE OR REPLACE FUNCTION safe_to_interval(p_seconds BIGINT)
RETURNS INTERVAL AS $$
BEGIN
    RETURN (p_seconds || ' seconds')::INTERVAL;
EXCEPTION
    WHEN interval_field_overflow THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql;
Enter fullscreen mode Exit fullscreen mode

2. Cumulative Overflow During INTERVAL Arithmetic

Aggregating or multiplying intervals in loops or SUM() operations can silently accumulate until the total exceeds the internal limit. This is particularly dangerous in batch jobs processing large time-series datasets.

-- Risky pattern
-- SELECT SUM(duration) FROM massive_log_table; -- may overflow

-- Safer: aggregate as epoch seconds, then convert
SELECT make_interval(secs => SUM(EXTRACT(EPOCH FROM duration)))
FROM log_table
WHERE duration IS NOT NULL;

-- Clamp individual values before aggregation
SELECT SUM(
    CASE
        WHEN duration > interval '10 years' THEN interval '10 years'
        ELSE duration
    END
) AS total_clamped
FROM log_table;

-- Safe interval multiplication
-- Bad:  SELECT interval '1 day' * 9999999999;
-- Good:
SELECT make_interval(days => 365 * 50); -- 50 years, safely constructed
Enter fullscreen mode Exit fullscreen mode

3. Invalid Interval Strings from External Sources

ETL pipelines and external APIs often deliver raw string data that gets cast to INTERVAL without validation. A single malformed or out-of-range value can cause an entire batch transaction to roll back.

-- Stage raw data as TEXT first
CREATE TABLE staging_raw (
    id          BIGINT,
    duration_str TEXT
);

-- Insert only valid rows into the production table
INSERT INTO production_events (id, duration)
SELECT
    id,
    safe_to_interval(duration_str::BIGINT)
FROM staging_raw
WHERE duration_str ~ '^\d+$'
  AND duration_str::BIGINT BETWEEN 0 AND 9223372036;

-- Log rejected rows for review
INSERT INTO error_log (source_id, raw_value, reason, logged_at)
SELECT id, duration_str, 'interval_field_overflow risk', NOW()
FROM staging_raw
WHERE duration_str !~ '^\d+$'
   OR duration_str::BIGINT > 9223372036;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Wrap unsafe casts in a PL/pgSQL function with EXCEPTION WHEN interval_field_overflow to gracefully return NULL instead of crashing.
  • Use make_interval() instead of string casting when constructing intervals from numeric inputs — it gives you field-level control and is easier to validate.
  • Convert to epoch first: when aggregating, use EXTRACT(EPOCH FROM interval_col) to work in plain FLOAT8, then convert back with make_interval(secs => ...).

Prevention Tips

  1. Add CHECK constraints at the table level to enforce a business-logic maximum on interval columns: 
ALTER TABLE task_logs
ADD CONSTRAINT chk_duration_sane
CHECK (duration BETWEEN interval '0' AND interval '50 years');
Enter fullscreen mode Exit fullscreen mode
  1. Always handle the exception in PL/pgSQL batch procedures so one bad row doesn't kill the whole job: 
BEGIN
    INSERT INTO events VALUES (r.id, (r.raw_seconds || ' seconds')::interval);
EXCEPTION
    WHEN interval_field_overflow THEN
        INSERT INTO error_log VALUES (r.id, r.raw_seconds, NOW());
END;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 22003 numeric_value_out_of_range — often triggers alongside 22015 when large numeric values are involved in interval construction.
  • 22008 datetime_field_overflow — occurs when adding an interval to a timestamp pushes the result beyond TIMESTAMP limits.
  • 22007 invalid_datetime_format — commonly appears with 22015 in ETL pipelines when raw strings are malformed before even reaching the overflow check.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)