Re: Date format for bulk copy

Michael Fuhr wrote:

>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.
>
>
>
Right, I *can* do this. But then I have to build knowledge into that
script so it can find each of these date fields (there's like 20 of them
across 10 different files) and then update that knowledge each time it
changes. I'm still leaning towards just making postgres accept at ':'
delimiter for milliseconds. Also, how much would a secondary script
slow down the bulk copy, if any?

>>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.
>
>
>
Oh, so I can load binary data into PG if it's ASCII-encoded octal? Why
not the user-defined type with associated user-defined input function?