Re: COPY <table> FROM STDIN BINARY

Hi,

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/)
My table is:

create table bulk_data (
id integer, ts timestamp, ...
);

which I try to populate from Java app using COPY command:

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.

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

Thanks a lot for any suggestions,

Vlad

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/

Владимир Баранов wrote:

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

There are two possible formats depending on whether or not
integer_datetimes is set or not. (This is a compile-time option, you can
query the server to see which is set via SHOW, I think -- it's also
autoreported on connection startup, but you can't get at that as a JDBC
client)

I can't remember the details of the formats though -- you may need to
check the server source code for details. One is a 64-bit integer from
memory.

-O