invalid input syntax for type date

Category: Data ExceptionVersions: All Postgres versions

What this means

Postgres attempted to parse a string as a date, time, or timestamp and could not match it against any recognised format. The type input function (date_in, timestamp_in, etc.) rejects the string before any storage occurs.

Why it happens

1Date string formatted for a different locale (e.g., DD/MM/YYYY when Postgres expects YYYY-MM-DD)
2Two-digit year ambiguity or missing year in the input string
3Timestamp string contains a timezone offset in an unexpected format
4Passing a text value from an application that formats dates differently from ISO 8601

How to reproduce

A date string in a non-ISO format is cast to DATE.

trigger — this will ERROR

SELECT '25/12/2024'::DATE;

ERROR: invalid input syntax for type date: "25/12/2024" LINE 1: SELECT '25/12/2024'::DATE;

Fix 1: Use ISO 8601 format (YYYY-MM-DD)

When you control the input format.

fix

SELECT '2024-12-25'::DATE; -- ISO 8601 always works regardless of DateStyle

SELECT '2024-12-25 14:30:00'::TIMESTAMP;
SELECT '2024-12-25T14:30:00Z'::TIMESTAMPTZ;

Why this works

Postgres date parsing in date_in() always accepts ISO 8601 format regardless of the DateStyle setting. ISO format is unambiguous and is the safest choice for application-generated date strings.

Fix 2: Use TO_DATE / TO_TIMESTAMP with explicit format

When the input format is fixed but non-ISO (e.g., from a legacy system).

fix

SELECT TO_DATE('25/12/2024', 'DD/MM/YYYY');

SELECT TO_TIMESTAMP('25-12-2024 14:30', 'DD-MM-YYYY HH24:MI');

Why this works

TO_DATE and TO_TIMESTAMP parse strings according to an explicit format template, bypassing the automatic format detection in date_in(). The format template uses Postgres format codes (DD, MM, YYYY, HH24, MI, SS) which are unambiguous.

What not to do

✗

Set DateStyle to 'SQL, DMY' globally to make European dates parse

Why it's wrong: Makes date string interpretation session-dependent and fragile; ISO 8601 is unambiguous and universally accepted.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/datatype-datetime.html

🔧 Source ref: src/backend/utils/adt/date.c — date_in()

📖 Further reading: Date/Time Types

📖 Further reading: Date/Time Input

Confidence assessment

✅ HIGH confidence

Stable and well-documented. ISO 8601 acceptance is guaranteed across all versions. Edge case: timezone-aware timestamps (TIMESTAMPTZ) require a timezone suffix; the same string without a suffix parses as TIMESTAMP (no timezone) and is then assumed to be in the server timezone.