Thread: 8.3b2: problem using "COPY ... TO/FROM .... BINARY"
hi there i'm not sure it its really a bug - the manual specifies that COPY ... BINARY between different PGSQL versions might be problematic. nevertheless: i've imported several tables from 8.2.5 to 8.3b2 without any problems, until one table produced an error on a timestamp field: from pgsql 8.2.5: COPY "users"."ratings" TO '/temp/ratings' BINARY; pgsql 8.3b2: COPY "users"."ratings" FROM '/temp/ratings' BINARY; 2007-11-17 20:17:55 CET ERROR: timestamp out of range 2007-11-17 20:17:55 CET CONTEXT: COPY ratings, line 15081, column r_date when using COPY ... CVS/TEXT; everything works. by using CVS output, i was able do determine that the failing record is: 447,883,0.0,1999-01-01 00:00:00,f it seems to be the only timestamp that is failing, i've removed it from the source table and was able to binary copy the remaining data without problems. is this a bug or a feature? :) - thomas ps: is there a way to convert the binary file to plain sql to directly check which record fails? i've tried to use pg_restore to read the file, but it said "does not appear to be a valid archive".
"Thomas H." <me@alternize.com> writes:
> i'm not sure it its really a bug - the manual specifies that COPY ...
> BINARY between different PGSQL versions might be problematic.
> nevertheless: i've imported several tables from 8.2.5 to 8.3b2 without
> any problems, until one table produced an error on a timestamp field:
I'll bet a nickel that you built one version with float timestamps and
the other with integer ...
regards, tom lane
tom lane wrote: >> i'm not sure it its really a bug - the manual specifies that COPY ... >> BINARY between different PGSQL versions might be problematic. > >> nevertheless: i've imported several tables from 8.2.5 to 8.3b2 without >> any problems, until one table produced an error on a timestamp field: > > I'll bet a nickel that you built one version with float timestamps and > the other with integer ... both versions are the official win32 builds from postgresl.org... - thomas
On Sat, 2007-11-17 at 21:13 +0100, Thomas H. wrote: > tom lane wrote: > >> i'm not sure it its really a bug - the manual specifies that COPY ... > >> BINARY between different PGSQL versions might be problematic. > > > >> nevertheless: i've imported several tables from 8.2.5 to 8.3b2 without > >> any problems, until one table produced an error on a timestamp field: > > > > I'll bet a nickel that you built one version with float timestamps and > > the other with integer ... > > both versions are the official win32 builds from postgresl.org... That means Tom is right, as usual :-) 8.3 is built with integer timestamps, 8.2 and earlier with float timestamps. //Magnus
Magnus Hagander <magnus@hagander.net> writes:
> On Sat, 2007-11-17 at 21:13 +0100, Thomas H. wrote:
>>> I'll bet a nickel that you built one version with float timestamps and
>>> the other with integer ...
>>
>> both versions are the official win32 builds from postgresl.org...
> That means Tom is right, as usual :-)
> 8.3 is built with integer timestamps, 8.2 and earlier with float
> timestamps.
Hmm, is that documented as a version discrepancy for the Win32 builds?
IIRC we had rough consensus for switching the default setting to integer
timestamps in 8.4. Perhaps it would be better for the Win32 build to
wait till 8.4 before switching, for consistency with other platforms.
regards, tom lane
On Sun, 2007-11-18 at 20:38 -0500, Tom Lane wrote: > Magnus Hagander <magnus@hagander.net> writes: > > On Sat, 2007-11-17 at 21:13 +0100, Thomas H. wrote: > >>> I'll bet a nickel that you built one version with float timestamps and > >>> the other with integer ... > >> > >> both versions are the official win32 builds from postgresl.org... > > > That means Tom is right, as usual :-) > > > 8.3 is built with integer timestamps, 8.2 and earlier with float > > timestamps. > > Hmm, is that documented as a version discrepancy for the Win32 builds? > > IIRC we had rough consensus for switching the default setting to integer > timestamps in 8.4. Perhaps it would be better for the Win32 build to > wait till 8.4 before switching, for consistency with other platforms. The switch is in that it's the default on the MSVC build. So two different decisions (making that the default on MSVC, and making the MSVC build the default) together made that third. I'm fine dropping the default on MSVC until 8.4, if it's going to happen then. The downside then is that people won't be able to upgrade to the next beta without initdb, right? Which isn't too good, but it's better to make such a change during beta than to have to do it during release... //Magnus