The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/18/datatype-datetime.html
Description:
Section 8.5.1 says date/time input is accepted "in almost any reasonable
format, including ISO 8601".
That holds for years 0001..9999 but not outside them, and I could not find
the limit stated anywhere.
ISO 8601 writes years outside that range with an explicit sign and more than
four digits. PostgreSQL
rejects those, while holding and printing the very same values in its own
spelling:
SELECT '10000-01-02'::date; -- 10000-01-02
SELECT '+10000-01-02'::date; -- ERROR: time zone displacement out of
range: "+10000-01-02"
SELECT '-0001-01-02'::date; -- ERROR: invalid input syntax for type
date: "-0001-01-02"
SELECT '0000-01-02'::date; -- ERROR: date/time field value out of
range: "0000-01-02"
Per B.1, a token starting with + or - is read as a numeric time zone, and
the first error names that directly. The negative forms fail differently -
as plain syntax rather than as a displacement - so I have not assumed the
same cause for them. Either way, ISO 8601 also counts through a year zero
where PostgreSQL counts BC from one, so ISO -0001 (2 BC) has no ISO spelling
PostgreSQL accepts.
I ran into this writing a PostgreSQL driver for Kotlin: for such a year, the
ISO 8601 that Kotlin's date library produces is a string PostgreSQL will not
read back — for a date it stores and prints happily.
Suggested wording — qualify the claim rather than describe the parser, e.g.:
"...including ISO 8601 (for years 0001-9999; ISO 8601 expanded years
carry an explicit sign, which
is read as a time zone offset — write 10000-01-02 or 0002-01-02 BC
instead), SQL-compatible,
traditional POSTGRES, and others."
