Problem: the external representation of time and timestamp are less precise than the internal representation.
We are using postgresql 7.1.3
The timestamp and time types support resolving microseconds (6 places beyond the decimal), however the output routines
roundthe value to only 2 decimal places.
This causes data degradation, if a table with timestamps is copied out and then copied back in, as the timestamps lose
precision.
We feel this is a data integrity issue. Copy out (ascii) does not maintain the consistency of the data it copies.
In our application, we depend on millisecond resolution timestamps and often need to copy out/copy back tables. The
currenttimestamp formating in postgresql 7.1.x breaks this badly.
A work around for display might be to use to_char(). But for copy the only workaround we have found is to use binary
copy.Alas, binary copy does not work for server to client copies.
Unfortunately, we need to copy to the client machine. The client copy does not support binary copies so we lose
precision.
Our suggested fix to this problem is to change the encoding of the fractional seconds part of the datetime and time
typesin datetime.c
(called by timestamp_out, time_out) to represent least 6 digits beyond the decimal (ie "%0.6f"). A configurable format
wouldalso work.
If there is another way to force the encoding to be precise we'd love to hear about it. Otherwise this appears to be a
silentdata integrity bug with unacceptable workarounds.
Thanks!
Laurette Cisneros (laurette@nextbus.com)
Elein Mustain
NextBus Information Systems