Skip site navigation (1) Skip section navigation (2)

Re: Populating large tables with occasional bad values

From: Oliver Jowett <oliver(at)opencloud(dot)com>
To: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
Cc: "John T(dot) Dow" <john(at)johntdow(dot)com>, "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Populating large tables with occasional bad values
Date: 2008-06-12 01:23:50
Message-ID: 48507AA6.6060902@opencloud.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Craig Ringer wrote:
> John T. Dow wrote:
> 
>> If I did this regularly, three options seem easiest.
>>
>> 1 - Load locally to get clean data and then COPY. This requires the 
>> server to have access local access to the file to be copied, and if 
>> the server is hosted by an isp, it depends on them whether you can do 
>> this easily.
> 
> You can COPY over a PostgreSQL network connection. See the \copy support 
> in psql for one example of how it works.
> 
> I've never had cause to try to use it with JDBC so I don't know how/if 
> it works in that context.

It's not supported in the standard JDBC driver unfortunately.

> However, batched inserts can be quite fast enough. If you do one INSERT 
> INTO per 1000 rows you'll already be seeing a massive performance boost:
> 
> INSERT INTO mytable
> VALUES
> (blah, blah),
> (blah, blah),
> (blah, blah),
> -- hundreds of rows later
> (blah, blah);
> 
> ... will be a *LOT* faster. If you have to do special processing or 
> error handling you can do it once you have the data in a server-side 
> staging table - and you can get it there quickly with multi-row inserts 
> or (at least using psql) a \copy .

You can get the same effect via JDBC batched inserts (addBatch() / 
executeBatch()) without having to actually do a multi-row INSERT 
statement. That's probably the most portable approach if you're using JDBC.

-O

In response to

pgsql-jdbc by date

Next:From: tivvpgsqljdbcDate: 2008-06-12 08:56:02
Subject: Re: Populating large tables with occasional bad values
Previous:From: Craig RingerDate: 2008-06-11 17:22:14
Subject: Re: Populating large tables with occasional bad values

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group