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?
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
