The following bug has been logged online:

Bug reference: 1617
Logged by: Dennis Vshivkov
Email address: walrus (AT) amur (DOT) ru
PostgreSQL version: 7.4.6, 8.0.1
Operating system: Debian GNU/Linux, kernel 2.4
Description: TIME WITH TIME ZONE comparison is partially broken
Details:

SELECT MAX(time) FROM (
SELECT '04:00:01+02'::TIMETZ
UNION
SELECT '05:00:00+03'::TIMETZ
) AS tbl(time);
max
-------------
05:00:00+03
(1 row)

[8.0.1|src/backend/utils/adt/date.c] timetz_cmp_internal():

double t1,
t2;

/* Primary sort is by true (GMT-equivalent) time */
t1 = time1->time + time1->zone;
t2 = time2->time + time2->zone;

if (t1 > t2)
return 1;
if (t1 < t2)
return -1;

This code is only valid when HAVE_INT64_TIMESTAMP is NOT the case. When it
is (in Debian builds, see versions below), seconds (->zone) are added to
microseconds (->time), which makes no sense. I checked Release Notes for
8.0.2, they don't seem to mention this bug.

SELECT version():
PostgreSQL 7.4.6 on i386-pc-linux-gnu, compiled by GCC i386-linux-gcc (GCC)
3.3.5 (Debian 1:3.3.5-6)
PostgreSQL 8.0.1 on i386-pc-linux-gnu, compiled by GCC cc (GCC) 3.3.5
(Debian 1:3.3.5-5)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Dennis Vshivkov" <walrus (AT) amur (DOT) ru> writes:
Fixed, thanks for the report.

Note that this fix will invalidate any existing indexes on TIMETZ
columns in --enable-integer-datetimes installations; they'll have
to be REINDEXed after application of the patch. I suspect such
indexes are pretty few and far between, so I went ahead and back-patched
the fix into back branches despite the risk of incompatibility.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings