Re: PG COPY from version 8 to 9 issue with timezonetz

On Thursday, March 17, 2011 10:10:49 am Brent Gulanowski wrote:
> We use PG COPY to successfully in PG 8 to copy a database between two
> servers. Works perfectly.
>
> When the target server is PG 9, *some* fields of type timezonetz end up
> garbled. Basically the beginning of the string is wrong:
>
> 152037-01-10 16:53:56.719616-05
>
> It should be 2011-03-16 or similar.
>
> In this case, the source computer is running Mac OS X 10.6.6 on x86_64
> (MacBook Pro Core i5), and the destination computer is running Debian Lenny
> on Xeon (Core i7).
>
> I looked at the documentation on the copy command, and the PG9 release
> notes, but I didn't see anything that might explain this problem.
>
> We are using the WITH BINARY option. It has been suggested to disable that.
> What are the down sides of that? I'm guessing just performance with binary
> columns.

I think the bigger downsides come from using it:) See below for more
information:

http://www.postgresql.org/docs/9.0/interactive/sql-copy.html

"Binary Format
The binary format option causes all data to be stored/read as binary format
rather than as text. It is somewhat faster than the text and CSV formats, but a
binary-format file is less portable across machine architectures and PostgreSQL
versions. Also, the binary format is very data type specific; for example it will
not work to output binary data from a smallint column and read it into an
integer column, even though that would work fine in text format.
The binary file format consists of a file header, zero or more tuples containing
the row data, and a file trailer. Headers and data are in network byte order. "

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com