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.kla...@gmail.com
