COPY BINARY 8.3 to 8.4 timestamp incorrect

"Chase, John" <jchase@xxxxxxxxx> · Wed, 14 Oct 2009 10:03:21 -0400

Hello,

I
am working on upgrading from 8.3.7 to 8.4.1. One of the functions of our
application is to "export" and "import" data, and to
accomplish this I've written some functions that use COPY ... TO ... BINARY and
COPY ... FROM ... BINARY. In testing the upgrade to 8.4.1 I noticed that when I
"import" from export files that were created under 8.3.7 the
timestamps are not brought in correctly. I boiled it down to this simple test
to discover where the break-down occurs:

On
the 8.3.7 installation I run this:

CREATE
TABLE test (

testtime timestamp

);

INSERT
INTO test VALUES(now());

COPY
test TO 'C:/Temp/test.backup' BINARY;

then,
on the 8.4.1 installation I run this:

CREATE
TABLE test (

testtime timestamp

);

COPY
test FROM 'C:/Temp/test.backup' BINARY;

SELECT
* FROM test;

And
what goes into the 8.3.7 side (e.g. '2009-10-14 09:10:32.989') comes out wrong
on the 8.4.1 side ('152013-03-31 15:44:27.229979').

The
encoding in both databases is the same (WIN1252). I double-checked and both
columns are "timestamp without timezone". Just for kicks I ran my
test (above) using COPY ... CSV, which of course worked because it writes out
plain-text.

I've
attached two files, test.837 (the 8.3.7 BINARY COPY from my test above) and
test.841 (a BINARY COPY from 8.4.1 of the "test" table that had the
correct date in it). Both files were created with only one row in test, using
the exact same date/time. So in theory these two files should be identical. But
clearly, 8.3.7 does something differently than 8.4.1. Also, if I try to COPY
the 8.4.1 file into 8.3.7 the date is likewise not correct ('2000-01-01
00:00:00').

So
I'm wondering if this is a bug in 8.4.1, or if I've left some stone unturned.
Just if you’re wondering, the two installations are in different worlds
(VMs), both running XP sp3.

Thanks
so much... John

Attachment:
test.837

Description: test.837
Attachment:
test.841

Description: test.841
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general