Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDjXS-00000000fy1-1Tml for pgsql-bugs@arkaria.postgresql.org; Mon, 05 Oct 2026 14:19:34 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xDjXR-0000000B584-0H1s for pgsql-bugs@arkaria.postgresql.org; Mon, 05 Oct 2026 14:19:33 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDEct-000000043BJ-0SRG for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:07 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDEcr-00000000GSI-1Up7 for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: Content-Transfer-Encoding:MIME-Version:Content-Type:Sender:Content-ID: Content-Description:In-Reply-To:References; bh=AiPLbVlc6+ul6IyjlHvnPsGQQ6Oj7RLxQ3X/g26mXPo=; b=rNcRPLdyPkdw5D8/0TELRM8QcR vXIonH67a5F1c8XJZf5GHM3QwY9Q4wJhY5mRclcPVc360nkVjsm81lwIfxemxj7pq6U+mYCl4Xi6F 5/LnV+dby5ETHapUgykQiUE/elNyDf8E0vQ65McjQ5hQXXOj7CdQM2R3a32t+C5X2hyEug1g7WVVi ciRBHDngOI+7ufvRFVy0On9aW5i+hh4eNlvFZ/8ewptqFJAzTVQgIDZYl4LvyyQSAE8P914sdRCk3 6+dqx/CkBW9zaaICndgEUlZVslYaqSRlmBgWArJySiCOeg9MlHqVLnuPJmbkIpYI684n10mS2lefq 7VPoJPpA==; Received: from wrigleys.postgresql.org ([2a02:16a8:dc51::60]) by mahout.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xDEcr-00000000uia-04hj for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:05 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xDEcp-00000002S8H-3zjp for pgsql-bugs@lists.postgresql.org; Sun, 04 Oct 2026 05:19:03 +0000 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Subject: BUG #19746: interval with INT64_MIN microseconds prints a value interval_in rejects To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: kehan5800@gmail.com Reply-To: kehan5800@gmail.com, pgsql-bugs@lists.postgresql.org Date: Sun, 04 Oct 2026 05:18:39 +0000 Message-ID: <19746-cf1e9c2c7f390d44@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk The following bug has been logged on the website: Bug reference: 19746 Logged by: Ke Email address: kehan5800@gmail.com PostgreSQL version: 18.6 Operating system: Ubuntu 22.04.2 x86_64 Description: =20 An interval whose time part is -9223372036854775808 microseconds is a legal, finite value (it is not -infinity unless months and days are also at their minimum). It can be entered and is produced by ordinary arithmetic, but its output cannot be read back: SELECT '-9223372036854775808 microseconds'::interval; interval -------------------------- -2562047788:00:54.775808 SELECT '-9223372036854775807 microseconds'::interval - '1 microsecond'::interval; -2562047788:00:54.775808 SELECT '-2562047788:00:54.775808'::interval; ERROR: 22007: invalid input syntax for type interval: "-2562047788:00:54.775808" LOCATION: DateTimeParseError, datetime.c:4289 SELECT '-2562047788:00:54.775807'::interval; -- one us less: fine SELECT '-2562047788 hours -54.775808 secs'::interval; -- fine The regression suite already prints this value as expected output (src/test/regress/expected/interval.out:1745, "-178956970 years -7 mons -2147483648 days -2562047788:00:54.775808") but never feeds the text back in. pg_dump consequence (pg_dump forces IntervalStyle =3D postgres, which is one of the affected styles): createdb src; createdb dst psql -d src -c "CREATE TABLE ivmin (id int PRIMARY KEY, i interval); INSERT INTO ivmin VALUES (1, '-9223372036854775807 us'::interval - '1 us'::interval), (2, '1 day');" pg_dump -d src | psql -d dst ... ERROR: invalid input syntax for type interval: "-2562047788:00:54.775808" CONTEXT: COPY ivmin, line 1, column i: "-2562047788:00:54.775808" psql -d dst -c "SELECT count(*) FROM ivmin" -- 0 (both rows lost) Per IntervalStyle (from the attached transcript): value postgres postgres_verbose sql_standard iso_8601 -9223372036854775808 us FAILS FAILS FAILS ok -2147483648 days ok FAILS ok ok Cause: in DecodeInterval() (src/backend/utils/adt/datetime.c), a field such as "-2562047788:00:54.775808" is tokenized as DTK_TZ, and the code at datetime.c:3592 decodes the unsigned part first: if (strchr(field[i] + 1, ':') !=3D NULL && DecodeTimeForInterval(field[i] + 1, fmask, range, &tmask, itm_in) =3D=3D 0) { if (*field[i] =3D=3D '-') { /* flip the sign on time field */ if (itm_in->tm_usec =3D=3D PG_INT64_MIN) return DTERR_FIELD_OVERFLOW; itm_in->tm_usec =3D -itm_in->tm_usec; } DecodeTimeForInterval() (datetime.c:2781) accumulates the magnitude 2562047788 h * 3600000000 + 54775808 =3D 9223372036854775808 =3D INT64_MAX = + 1 with int64_multiply_add(), which overflows, so the condition is false and the field falls through to the generic path, which reports "invalid input syntax". The signed result would have been representable. The postgres_verbose case has the same shape on the days field: AddVerboseIntPart() (datetime.c:4700-4709) prints i64abs(value) and appends "ago", so -2147483648 days prints as "@ 2147483648 days ago", and 2147483648 does not fit the int32 days field on input: SELECT '@ 2147483648 days ago'::interval; ERROR: interval field value out of range: "@ 2147483648 days ago" Expected: interval_out() output is accepted by interval_in() for every finite interval value (at least in the default IntervalStyle that pg_dump uses), so dumps restore. Actual: the minimum time value (and, in postgres_verbose, the minimum days value) prints as text that interval_in() rejects; a table holding it cannot be restored from pg_dump, and the whole COPY for that table fails. Possible fix: let the input side accept a magnitude of exactly INT64_MAX + 1 when it is going to be negated (e.g. decode the hh:mm:ss magnitude into a uint64 or negate while accumulating, as the "ago" and sign handling already does elsewhere). That would also make dumps already written with this value restorable. Changing EncodeInterval() instead would only help dumps taken after the fix.