COPY BINARY 8.3 to 8.4 timestamp incorrect
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
"Chase, John" <jchase@mtcsc.com> writes:
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.
Probably you've got 8.4 compiled with integer timestamps where the 8.3
DB used float timestamps, or perhaps vice-versa.
regards, tom lane
that's because by default 8.4 uses integer timestamps, instead of whatever
8.3 was using.
and you pretty much use something, that is suppose to be only used within
the scope of the same version and hardware type (and potentially even
build).
That makes sense, of course. I'm guessing this is because I formally
used the pgInstaller and since 8.4 is not supported yet by pgInstaller I
moved to the EnterpriseDB installer. The man behind the current must
have done the build with different options. Would you concur? Maybe I
should ask the man behind the curtain (Dave Page).
Thanks!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, October 14, 2009 10:21 AM
To: Chase, John
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
"Chase, John" <jchase@mtcsc.com> writes:
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.
Probably you've got 8.4 compiled with integer timestamps where the 8.3
DB used float timestamps, or perhaps vice-versa.
regards, tom lane
Wow, quick response from Dave Page. For those who may be interested,
here's his answer:
"pgInstaller used floating point, whilst the one-click installers use
(and will continue to use) the more accurate integer timestamps."
-----Original Message-----
From: Chase, John
Sent: Wednesday, October 14, 2009 10:29 AM
To: pgsql-general@postgresql.org
Subject: RE: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
That makes sense, of course. I'm guessing this is because I formally
used the pgInstaller and since 8.4 is not supported yet by pgInstaller I
moved to the EnterpriseDB installer. The man behind the current must
have done the build with different options. Would you concur? Maybe I
should ask the man behind the curtain (Dave Page).
Thanks!
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, October 14, 2009 10:21 AM
To: Chase, John
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] COPY BINARY 8.3 to 8.4 timestamp incorrect
"Chase, John" <jchase@mtcsc.com> writes:
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.
Probably you've got 8.4 compiled with integer timestamps where the 8.3
DB used float timestamps, or perhaps vice-versa.
regards, tom lane
Import Notes
Reply to msg id not found: E9ECD1E1F3011340AC2A18625FE3DCC815C83142@MAIL.ITSFAC.COM
"Chase, John" <jchase@mtcsc.com> writes:
That makes sense, of course. I'm guessing this is because I formally
used the pgInstaller and since 8.4 is not supported yet by pgInstaller I
moved to the EnterpriseDB installer. The man behind the current must
have done the build with different options. Would you concur?
Well, there's not much guessing or asking necessary --- try "show
integer_datetimes" on both servers.
regards, tom lane
On 14/10/2009 15:28, Chase, John wrote:
That makes sense, of course. I'm guessing this is because I formally
used the pgInstaller and since 8.4 is not supported yet by pgInstaller I
moved to the EnterpriseDB installer. The man behind the current must
As I understand it, pgInstaller is going to be maintained for pre-8.4
versions only; the only installer for 8.4+ is EnterpriseDB's one-click
installer.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------