Thread

Loading binary data into the database

Adriaan Joubert <a.joubert@albourne.com> — 2000-07-20T11:36:34Z

Hi,

    I'm having serious problems trying to load large amounts of data
into the database. I have this data in binary, database compatible,
form, but there seems no way left to upload this data except to turn it
all back into strings (since 7.0.2 binary copy has been disabled, making
quite a few libpq functions superfluous). This is quite a serious
deficiency in my view.

So, I want to know what my options are. For this type of thing I would
have thought that Karel's stored queries would be useful, combined with
some way of uploading binary data to the database. Something along the
lines of

prepare insert into my_table (col1, col2) values (?,?);

execute <handle to the query> 3, 4;

To upload binary data there would have to be a libpq call that uploads
the data in the execute statement as binary (there is a specification
for this already in the current libpq), and executes the prepared plan.
For any application that generates a lot of data (especially floating
point data), this would be a huge win. An added advantage would be that
this type of schema would allow a serial value on the table to be
incremented as in any normal insert, which has always been annoying when
using copy.

I have no idea how hard this is and whether I'm the only person in the
world that will find this useful. I seem to be the only one who moaned
about the binary copy vanishing, and breaking code, so perhaps nobody
else sees this as a problem?

Adriaan