BUG #19738: `uuidv7(interval)` rejects sub-millisecond timestamps within the final 48-bit millisecond

On Fri, 2026-10-02 at 22:36 +0000, PG Bug reporting form wrote:

PostgreSQL version: 18.6

UUIDv7 stores a 48-bit Unix-millisecond timestamp, and PostgreSQL's
documentation says its timestamp also includes sub-millisecond precision. A
shifted timestamp 250 microseconds into the final representable millisecond
is
rejected as out of range, although its millisecond field is still the
maximum
valid value.

**Reproduction:**

```sql
SELECT uuidv7(
    (timestamptz '1970-01-01 UTC'
     + interval '281474976710655 milliseconds'
     + interval '250 microseconds')
    - clock_timestamp()
);
```

**Actual result:** SQLSTATE `22008`, `timestamp out of range for UUID
version 7`.

**Expected result:** Generate a UUIDv7 whose 48-bit millisecond timestamp is
`2^48 - 1`, preserving the permitted sub-millisecond component.

I think that is a non-bug.

uuidv7(interval) gets the actual current timestamp (just like clock_timestamp
does) and adds that to the interval argument. That call is a bit later than
the clock_timestamp in your statement, so the result is maybe bigger than the
timestamp you specified, so it might exceed the maximum possible timestamp.

And anyway, discussing the oddities of uuidv7() for a timestamp in the year
10889 is somewhat irrelevant...

Yours,
Laurenz Albe

On 4 Oct 2026, Tom Lane wrote:

it's bandying around not two but three(!) different clock precisions,

The interval argument was intended to spread concurrent insert streams
over different index hot spots, keeping locality within each stream.
It shifts the generator's clock rather than taking an application
timestamp to encode. We discussed that distinction at some length
during development [0]/messages/by-id/1012137874.340418.1721776188406@mail.yahoo.com.

The precisions are deliberate: milliseconds for the UUID field,
microseconds for interval arithmetic, and finer clock precision for
the sub-millisecond bits. RFC 9562's Method 3 uses that extra precision
to reduce collisions [1]https://www.rfc-editor.org/rfc/rfc9562.html#section-6.2, so we preserve the nanosecond remainder
across the interval calculation.

As for the original report, storing an arbitrary application timestamp
in a UUID is not the intended use. In our interpretation of the RFC,
these bits serve uniqueness and ordering, not timestamp storage. The
function deliberately uses its own clock, so
uuidv7(target - clock_timestamp()) does not promise to preserve the
exact microseconds of target.

Thank you!

Best regards, Andrey Borodin.

[0]: /messages/by-id/1012137874.340418.1721776188406@mail.yahoo.com
[1]: https://www.rfc-editor.org/rfc/rfc9562.html#section-6.2