Re: Date format for bulk copy

On Wed, Oct 13, 2004 at 10:06:58AM -0400, David Rysdam wrote:

> Sybase bulk copies the date fields out in this format:
>
> Mar 4 1973 10:28:00:000AM
>
> Postgresql's COPY (or psql \copy) doesn't like that format.

You could filter the data through a script that reformats certain
fields, then feed the reformatted data to PostgreSQL. This is
usually a trivial task for Perl, awk, sed, or the like.

> I have a similarish problem with another field type. In Sybase it's a
> binary format. In postgres it is a binary format (bytea). But Sybase
> bcps the data out in ASCII. Sybase recognizes that when it is a binary
> field and auto-converts the ASCII back to binary. Postgres doesn't.
> Again, I created a temporary table and did a decode(field, 'hex') to the
> real table.

Sounds like Sybase is dumping in hex, whereas PostgreSQL expects
octal. If you can't change the dump format, then again, filtering
the data through a script might work.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/