Re: Binary data migration from MSSQL - Mailing list pgsql-general

From Joe Conway
Subject Re: Binary data migration from MSSQL
Date
Msg-id 3E1B3F9E.5050102@joeconway.com
Whole thread Raw
In response to Binary data migration from MSSQL  ("Roman Fail" <rfail@posportal.com>)
List pgsql-general
Roman Fail wrote:
> ** bcp: I'd like to use bcp to do a text import, but I can't figure out how
> to make the binary data work on either end.  It appears to me that Postgres
> COPY requires literal binary data to be in octal format (e.g. '\\047').
> bcp outputs the binary data as an non-escaped ASCII string of hex values
> (e.g. DF9B52A3). I guess I could write a Java program to convert the hex to
> escaped octal, then run COPY. This would probably be pretty slow (both from
> a 20-million row performance standpoint and my personal productivity).
>
> Is there a better way to do this?
>

I'd lean toward bcp as the fastest method, even if you need to do some data
massaging. In MSSQL 2000 it appears that bcp can bulk copy a query in similar
fashion to a table. Perhaps you could do the binary-to-octal transformation in
the export query from MSSQL?

Joe


pgsql-general by date:

Previous
From: Joe Conway
Date:
Subject: Re: release prepared statement
Next
From: Bruce Momjian
Date:
Subject: Re: table schema causes crash