Re: Date format for bulk copy

On Wed, Oct 13, 2004 at 01:32:01PM -0400, David Rysdam wrote:
> Michael Fuhr wrote:
> >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.

In your case that's a reasonable argument against filtering the
data with a script. Using a regular expression in the script might
reduce or eliminate the need for some of the logic, but then you'd
run the risk of reformatting data that shouldn't have been touched.

> I'm still leaning towards just making postgres accept at ':'
> delimiter for milliseconds.

Based on your requirements, that might indeed be a better solution.
I'd probably choose to extend PostgreSQL rather than hack what
already exists, though. Doing the latter might break something
else and you have to remember to add the hack every time you upgrade
the server software. That can cause headaches for whoever inherits
the system from you unless it's well-documented.

> Also, how much would a secondary script slow down the bulk copy,
> if any?

Probably some, but perhaps not enough to be significant. I'd expect
the database to be the bottleneck, but I'd have to run tests to say
for certain.

> >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?

Yes -- see the "Binary Data Types" documentation:

http://www.postgresql.org/docs/7.4/static/datatype-binary.html

> Why not the user-defined type with associated user-defined input function?

If filtering the data is awkward, then that might be a better way
to go.

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