Date Time Field Value Out Of Range On Load

A bare cast such as raw_date::date hands the text to PostgreSQL's date/time parser, which decides field order from the session DateStyle setting. With DateStyle MDY the parser expects month/day/year, so '25/01/2025′ is read as month=25, which is out of range, and 22008 is raised. The exact same parser, given a string where both leading fields are 12 or less, has no way to know the intended order: it applies MDY and quietly produces a valid but wrong date. That is why the symptom is split in two -- a subset errors loudly (leading field over 12) and a much larger subset is corrupted silently.

The order of the fields is a property of the DATA, not of the session, so reading it through a session setting is the root mistake. to_date(text, format) removes the ambiguity by stating the field order explicitly, independent of DateStyle. The order of the fields is a property of the DATA, not of the session, so reading it through a session setting is the root mistake. to_date(text, format) removes the ambiguity by stating the field order explicitly, independent of DateStyle.

Free Tier -- Basic Approach

First question: how many rows cannot parse at all? Count the strings whose leading field is greater than 12, because under MDY that field is read as a month and 13..31 is impossible. This is the loudly-failing subset.

SELECT count(*) AS day_gt_12_rows FROM staging_events WHERE split_part(raw_date,'/',1)::int > 12;

day_gt_12_rows
----------------
              5
(1 row)

SELECT count(*) FROM staging_events WHERE split_part(raw_date,'/',1)::int > 12; here it returns 5 -- the day-25 rows.

Parse with an explicit format instead of a bare cast. to_date(raw_date, 'DD/MM/YYYY') reads the fields in the stated order no matter what DateStyle is set, so every row -- including the day-25 strings -- parses correctly and the load completes. This is the literal capture.

TRUNCATE events_clean RESTART IDENTITY;
INSERT INTO events_clean(event_date) SELECT to_date(raw_date,'DD/MM/YYYY') FROM staging_events;

TRUNCATE TABLE
INSERT 0 1000

TRUNCATE events_clean RESTART IDENTITY; then INSERT INTO events_clean(event_date) SELECT to_date(raw_date,'DD/MM/YYYY') FROM staging_events; the result is INSERT 0 1000 -- all rows loaded, no ambiguity.

Confirm the healthy state: all 1000 rows loaded, the day-25 strings became real January and May dates (proving DD/MM was honoured), and a bare MDY cast of a day-25 string STILL raises 22008 -- showing the fix was the explicit format, not a quietly changed global setting.

SET datestyle TO 'ISO, MDY';

loaded_rows
-------------
        1000
(1 row)

SELECT count(*) AS loaded_rows FROM events_clean;

loaded_rows
-------------
        1000
(1 row)

SELECT event_date FROM events_clean WHERE event_date IN (DATE '2025-01-25', DATE '2025-05-25') ORDER BY event_date;

event_date
------------
 2025-01-25
 2025-05-25
(2 rows)

SELECT '25/01/2025'::date;

ERROR:  22008: date/time field value out of range: "25/01/2025"
LINE 1: SELECT '25/01/2025'::date;
               ^
HINT:  Perhaps you need a different "datestyle" setting.
LOCATION:  DateTimeParseError, datetime.c:3992

Check count(*) = 1000; confirm the loaded dates 2025-01-25 and 2025-05-25 exist; then SELECT '25/01/2025′::date still errors with 22008, which is the correct, expected behaviour for an ambiguous bare cast.

📦 Version Matrix (PG 14--18)

Run the same break-and-fix on PostgreSQL 14, 15, 16, 17, and 18: a bare cast under MDY raises the same date/time range error on every version, and the explicit-format load completes identically (INSERT 0 100 on the 100-row matrix set). The behaviour is uniform across all supported releases.

Frequently Asked Questions

References

• PostgreSQL: Date/Time Types and input
• PostgreSQL: DateStyle run-time configuration
• PostgreSQL: Data Type Formatting Functions (to_date / to_timestamp)
• PostgreSQL Error Codes (Class 22 -- Data Exception)