Adrian Klaver <adrian.klaver@gmail.com> Thursday 17 March 2011 19:18:25
> 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. "
Actually binary mode is faster in some situations, and slower with other, in
any case it should save space in backup files or during transmission (e.g.
binary tz takes 8 bytes, text takes more)
But this may be due to encoding of timestamptz, you could have 8 version
compiled with float timestamps, and 9 with integer tiemstamps or vice versa.
Regards,
Radek
