Re: COPY <table> FROM STDIN BINARY

Hi Vladimir and jdbc-list!

2007/8/17, Владимир Баранов <baranoff(at)inbox(dot)ru>:
> I have troubles with bulk insert of binary data via COPY command.
>
> I use JDBC 3.0 driver patched by Kalle Hallivuori (http://kato.iki.fi/)

Makes me happy that people are at least trying to use it :)

...
> copy bulk_data from stdin binary;
>
> All integer, varchar and bytea values are copied correctly. But when I put into stream System.currentTimeMillis() as the timestamp value, that value is interpreted by PostgreSQL in a manner I could not understand; as the result I get wrong timestamp value in the table.

Interesting. I haven't yet looked into binary copying myself, so I
can't really say what would be the appropriate value. Here are some
blind guesses:

1) byte order - does Java write the long value into the stream in the
same order that the server expects to read it?
2) time zone - which time zone is the java VM running in, and how much
is it different from that of the postgresql server process?

Could you post some values that you send to the server and what values
they produce at the server?

> My question is: what input binary format of a timestamp value should I use?

That would probably be specified in PostgreSQL binary format
documentation? I don't have the time to look deep into it right now,
but this seems pretty common problem, given the answers from google
for 'site:postgresql.org "binary format"'.

--
Kalle Hallivuori +358-41-5053073 http://korpiq.iki.fi/