Thread: psql COPY with JDBC
Hi All-
I’ve been trying over at the general postgres list with no luck, so I thought I’d try here. I need to bulk load results from a Java application on one machine to a postgres db on a remote server. I know I can use psql interactively to accomplish this, but I’d like to be able to do it programmatically:
1) I noticed that there was a COPY patch floating around a couple of years ago – does this still exist. If so, has it been updated for the latest JDBC driver?
2) Is there a better way to accomplish this than the above patch – with or without JDBC?
Thanks for your time!
Chris
Hi All-
I’ve been trying over at the general postgres list with no luck, so I thought I’d try here. I need to bulk load results from a Java application on one machine to a postgres db on a remote server. I know I can use psql interactively to accomplish this, but I’d like to be able to do it programmatically:
1) I noticed that there was a COPY patch floating around a couple of years ago – does this still exist. If so, has it been updated for the latest JDBC driver?
2) Is there a better way to accomplish this than the above patch – with or without JDBC?
Thanks for your time!
Chris
Hi Dave-
Thanks for the patch. Is it best to use the code base from this gz or should I attempt to apply the patch to the current code base?
Since the patch is so old, is there a preferred way to get at this functionality?
-Chris
From: Dave Cramer [mailto:pg@fastcrypt.com]
Sent: Monday, April 03, 2006 4:04 PM
To: Christopher Condit
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] psql COPY with JDBC
Yeah, there is a patch, this is rather old now but will do copy.
Dave
On 3-Apr-06, at 4:36 PM, Christopher Condit wrote:
Hi All-
I’ve been trying over at the general postgres list with no luck, so I thought I’d try here. I need to bulk load results from a Java application on one machine to a postgres db on a remote server. I know I can use psql interactively to accomplish this, but I’d like to be able to do it programmatically:
1) Is there a better way to accomplish this than the above patch – with or without JDBC?
Thanks for your time!
Chris
Chris, I have a patched version of the JDBC driver which appears to work. However I built a wrapper round it to make it even easierto use, to make it more like pure a JDBC prepared statement - I'll gladly send you this. > Yeah, there is a patch, this is rather old now but will do copy. > 2) Is there a better way to accomplish this than the above patch > with or without JDBC?
Hi Dave-
Thanks for the patch. Is it best to use the code base from this gz or should I attempt to apply the patch to the current code base?
Since the patch is so old, is there a preferred way to get at this functionality?
-Chris
From: Dave Cramer [mailto:pg@fastcrypt.com]
Sent: Monday, April 03, 2006 4:04 PM
To: Christopher Condit
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] psql COPY with JDBC
Yeah, there is a patch, this is rather old now but will do copy.
Dave
On 3-Apr-06, at 4:36 PM, Christopher Condit wrote:
Hi All-
I’ve been trying over at the general postgres list with no luck, so I thought I’d try here. I need to bulk load results from a Java application on one machine to a postgres db on a remote server. I know I can use psql interactively to accomplish this, but I’d like to be able to do it programmatically:
1) Is there a better way to accomplish this than the above patch – with or without JDBC?
Thanks for your time!
Chris
Christopher Condit wrote: > Hi All- > I've been trying over at the general postgres list with no luck, so I > thought I'd try here. I need to bulk load results from a Java > application on one machine to a postgres db on a remote server. I > know I can use psql interactively to accomplish this, but I'd like to > be able to do it programmatically: 1) I noticed that there was > a COPY patch floating around a couple of years ago - does this still > exist. If so, has it been updated for the latest JDBC driver? 2) > Is there a better way to accomplish this than the above patch - with > or without JDBC? Well, since you are using a Java application, you are not limited to COPY, correct? You can generate whatever output format you want. In that case, you can generate INSERT statements, right? -- Guy Rouillier
Yes, that's true, but isn't it significantly faster to use the bulk
loader for many records.
-Chris
-----Original Message-----
From: pgsql-jdbc-owner@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Guy Rouillier
Sent: Monday, April 03, 2006 4:37 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] psql COPY with JDBC
Christopher Condit wrote:
> Hi All-
> I've been trying over at the general postgres list with no luck, so I
> thought I'd try here. I need to bulk load results from a Java
> application on one machine to a postgres db on a remote server. I
> know I can use psql interactively to accomplish this, but I'd like to
> be able to do it programmatically: 1) I noticed that there was
> a COPY patch floating around a couple of years ago - does this still
> exist. If so, has it been updated for the latest JDBC driver? 2)
> Is there a better way to accomplish this than the above patch - with
> or without JDBC?
Well, since you are using a Java application, you are not limited to
COPY, correct? You can generate whatever output format you want. In
that case, you can generate INSERT statements, right?
--
Guy Rouillier
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Christopher Condit wrote: > Yes, that's true, but isn't it significantly faster to use the bulk > loader for many records. That all depends, are you including the time to customize and maintain the code? The INSERT method will work without changing any driver code. I dumped an Oracle database using ora2pg, which generates INSERT statements, and I inserted 8 million rows fairly fast on mediocre hardware (dual 1 GHz Pentium III). > > -Chris > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org > [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Guy Rouillier > Sent: Monday, April 03, 2006 4:37 PM > To: pgsql-jdbc@postgresql.org > Subject: Re: [JDBC] psql COPY with JDBC > > Christopher Condit wrote: >> Hi All- >> I've been trying over at the general postgres list with no luck, so I >> thought I'd try here. I need to bulk load results from a Java >> application on one machine to a postgres db on a remote server. I >> know I can use psql interactively to accomplish this, but I'd like to >> be able to do it programmatically: 1) I noticed that there was >> a COPY patch floating around a couple of years ago - does this still >> exist. If so, has it been updated for the latest JDBC driver? 2) >> Is there a better way to accomplish this than the above patch - with >> or without JDBC? > > Well, since you are using a Java application, you are not limited to > COPY, correct? You can generate whatever output format you want. In > that case, you can generate INSERT statements, right? -- Guy Rouillier
Hi, Christopher, Christopher Condit wrote: > I’ve been trying over at the general postgres list with no luck, so I > thought I’d try here. I need to bulk load results from a Java > application on one machine to a postgres db on a remote server. I know > I can use psql interactively to accomplish this, but I’d like to be able > to do it programmatically: Some ideas for "ugly hacks": - Use a java.lang.ProcessBuilder and pipe into psql - Use some secure channel (e. G. libjsch or https upload) to copy the date into a file on the server, and then issue "COPY table FROM file". HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Hi, Christopher, Christopher Condit wrote: > Yes, that's true, but isn't it significantly faster to use the bulk > loader for many records. Have you tried the following: Use a Prepared Statement for the insert, then addBatch() it and executeBatch() it at the end, with autocommit off? This should be the fastest non proprietary way to load data into a database. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
Probably a dumb question, but is there any reason this patch hasn't been committed to the base JDBC code? On Apr 3, 2006, at 6:15 PM, Dave Cramer wrote: > Chris, > > It's not *that* old, it should apply to the latest code base > without too much effort. If you do can you send me the most current > patch. > > Either way it will work > > Dave > On 3-Apr-06, at 7:07 PM, Christopher Condit wrote: > >> Hi Dave- >> >> Thanks for the patch. Is it best to use the code base from this >> gz or should I attempt to apply the patch to the current code base? >> >> Since the patch is so old, is there a preferred way to get at this >> functionality? >> >> >> >> -Chris >> >> >> >> From: Dave Cramer [mailto:pg@fastcrypt.com] >> Sent: Monday, April 03, 2006 4:04 PM >> To: Christopher Condit >> Cc: pgsql-jdbc@postgresql.org >> Subject: Re: [JDBC] psql COPY with JDBC >> >> >> >> Yeah, there is a patch, this is rather old now but will do copy. >> >> >> >> http://download.postgresintl.com/copy.tar.gz >> >> >> >> Dave >> >> On 3-Apr-06, at 4:36 PM, Christopher Condit wrote: >> >> >> >> >> Hi All- >> >> I’ve been trying over at the general postgres list with no luck, >> so I thought I’d try here. I need to bulk load results from a >> Java application on one machine to a postgres db on a remote >> server. I know I can use psql interactively to accomplish this, >> but I’d like to be able to do it programmatically: >> >> 1) Is there a better way to accomplish this than the above >> patch – with or without JDBC? >> >> >> >> Thanks for your time! >> >> >> >> Chris >> >> >> >> >> >> >> > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Jim, Yeah, it duplicates the executor code so that it is two places. Ideally it should only be in one place. Dave On 6-Apr-06, at 10:26 PM, Jim Nasby wrote: > Probably a dumb question, but is there any reason this patch hasn't > been committed to the base JDBC code? > > On Apr 3, 2006, at 6:15 PM, Dave Cramer wrote: >> Chris, >> >> It's not *that* old, it should apply to the latest code base >> without too much effort. If you do can you send me the most >> current patch. >> >> Either way it will work >> >> Dave >> On 3-Apr-06, at 7:07 PM, Christopher Condit wrote: >> >>> Hi Dave- >>> >>> Thanks for the patch. Is it best to use the code base from this >>> gz or should I attempt to apply the patch to the current code base? >>> >>> Since the patch is so old, is there a preferred way to get at >>> this functionality? >>> >>> >>> >>> -Chris >>> >>> >>> >>> From: Dave Cramer [mailto:pg@fastcrypt.com] >>> Sent: Monday, April 03, 2006 4:04 PM >>> To: Christopher Condit >>> Cc: pgsql-jdbc@postgresql.org >>> Subject: Re: [JDBC] psql COPY with JDBC >>> >>> >>> >>> Yeah, there is a patch, this is rather old now but will do copy. >>> >>> >>> >>> http://download.postgresintl.com/copy.tar.gz >>> >>> >>> >>> Dave >>> >>> On 3-Apr-06, at 4:36 PM, Christopher Condit wrote: >>> >>> >>> >>> >>> Hi All- >>> >>> I’ve been trying over at the general postgres list with no luck, >>> so I thought I’d try here. I need to bulk load results from a >>> Java application on one machine to a postgres db on a remote >>> server. I know I can use psql interactively to accomplish this, >>> but I’d like to be able to do it programmatically: >>> >>> 1) Is there a better way to accomplish this than the above >>> patch – with or without JDBC? >>> >>> >>> >>> Thanks for your time! >>> >>> >>> >>> Chris >>> >>> >>> >>> >>> >>> >>> >> > > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > >
> I have a patched version of the JDBC driver which appears to work. > However I built a wrapper round it to make it even easier to use, to > make it more like pure a JDBC prepared statement - I'll gladly send > you this. Does anyone have a patched version of the 8.2 JDBC3 driver that can do COPY? Also: I haven't seen evidence of a JDBC prepared statement approach for 8.2's multiple-row INSERTs (using multiple VALUES arguments). Does such a beast exist, or will it? Also: what exactly does the PostgreSQL driver do with INSERTs that are batched with addBatch? I must confess I haven't tried this yet. I would really love it if there were an officially supported fast way to do bulk inserts with the PostgreSQL JDBC driver. JDBC hackers, you will be rewarded by my great joy and enthusiastic praise if you work on this ;-) Our application uses Lucene in combination with PostgreSQL, and we often need to take a few tens or hundreds of thousands of record IDs from the Lucene side and do something with them in PostgreSQL. I'd love to use tsearch2, but our text database is not transactional, so Lucene totally kicks tsearch2's butt, performance-wise (even with the new GIN index). Thanks, Kevin Murphy
Kevin Murphy wrote: > Also: I haven't seen evidence of a JDBC prepared statement approach for > 8.2's multiple-row INSERTs (using multiple VALUES arguments). Does such > a beast exist, or will it? I assume you can do this without driver changes by preparing a suitable statement. Presumably you'd do something like prepare a 20-row-insert statement, use it until you have less than 20 rows left, then prepare and use single-row-inserts for the remainder. > Also: what exactly does the PostgreSQL driver do with INSERTs that are > batched with addBatch? I must confess I haven't tried this yet. It prepares them once and sends a pair of Bind/Execute messages for each set of parameter values, with a Sync at the end. You can see the details with loglevel=2 -O