pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Lubratt <mark.lubratt@indeq.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Multiple inserts without COPY
Date: Wed, 03 Mar 2004 23:22:35 -0500
Message-ID: <21228.1078374155@sss.pgh.pa.us> (raw)
In-Reply-To: <B1385542-6D86-11D8-BC23-000A9579AF50@indeq.com>
References: <B1385542-6D86-11D8-BC23-000A9579AF50@indeq.com>

Mark Lubratt <mark.lubratt@indeq.com> writes:
> The deletes look something like
> delete from CL where CL_id = i
> where i could be a list of several hundred integers.  Again, right now 
> I iterate through the list.

Consider
	delete from CL where CL_id in (i,j,k,...);
If you have hundreds of target values, it might be better to put them in
a temp table and go
	delete from CL where CL_id in (select id from temp_table);
The latter should be reasonably quick in 7.4, but be warned that it'll
suck in prior releases.

> MySQL has a multiple insert feature where you simply append a bunch of 
> (j, k)'s separated by a comma.  Does PostgreSQL have anything like 
> this?

That is SQL-spec syntax, but we've not gotten around to implementing it.
COPY is a lot faster for bulk inserts.

> I was hoping I might be able to use COPY, but I see that's 
> really only for psql.

Huh?  You can use COPY FROM STDIN in most of our client libraries,
certainly so with libpq.  What are you using?

			regards, tom lane



view thread (5+ messages)  latest in thread

Message-ID: <21228.1078374155@sss.pgh.pa.us>
Permalink:  ../21228.1078374155@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/21228.1078374155@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mark.lubratt@indeq.com
  Subject: Re: Multiple inserts without COPY
  In-Reply-To: <21228.1078374155@sss.pgh.pa.us>
  References: <B1385542-6D86-11D8-BC23-000A9579AF50@indeq.com> <21228.1078374155@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox