COPY BINARY 8.3 to 8.4 timestamp incorrect

From: "Chase, John" <jchase(at)mtcsc(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: COPY BINARY 8.3 to 8.4 timestamp incorrect
Date: 2009-10-14 14:03:21
Message-ID: FB8E264F6469914FA02A3B847B448D2C032FC6A9@MAIL.ITSFAC.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 Content-Type Size
test.837 application/octet-stream 35 bytes
test.841 application/octet-stream 35 bytes

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-10-14 14:20:33 Re: COPY BINARY 8.3 to 8.4 timestamp incorrect
Previous Message Andale 2009-10-14 13:42:39 could not open process token: error code 5