Thread: Multiple inserts without COPY

Hello!

I have a routine in my application where I have the potential to
generate hundreds of inserts or deletes at one time.  Right now, I
issue each insert and delete separately.  I'm hoping there might be a
way of generating a single SQL statement to send to the backend for
each.  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.

The inserts might look like

insert into CL (CO_id, PE_ID) values (j, k)

where j and k are also integers and I could have a list of several
hundred pairs of j and k.

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?  I was hoping I might be able to use COPY, but I see that's
really only for psql.

Are there any options?  Or, do I simply send a bunch of queries?

Thanks!
Mark

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

On Mar 3, 2004, at 10:22 PM, Tom Lane wrote:

> 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.
>

Yeah, that's what I was looking for!  I thought I might be able to do
that.  Cool.

>> 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?
>

Actually, I'm using REALbasic.  All the communication is happening
through a TCP connection.  I tried emulating what the command might
look like in pgAdmin.  But, of course, after the semi-colon, the parser
got confused when it hit the actual data.  I tried:

COPY MyTable (id, val) FROM STDIN;
2    Hello There!
\.

It choked at the 2.  I was just trying to see if the backend suspended
parsing and would just start copying like psql does.  But, I guess not.
  How does psql shovel a COPY at the backend?

Oooh.  I just remembered.  There is a new method in the REALbasic
classes that provide the PostgreSQL functionality.  I'll have to check
it out...

I was hoping that there might be a syntax trick  with INs or something
like the delete command above.  Something that might expand in the
parser to do what I want to do.

Thanks!

Mark

Mark Lubratt <mark.lubratt@indeq.com> writes:
>> Huh?  You can use COPY FROM STDIN in most of our client libraries,
>> certainly so with libpq.  What are you using?

> Actually, I'm using REALbasic.

Um.  I have no idea what sort of support they have, but you do need a
client interface library that knows about the COPY data protocol.
Typically there will be separate API calls for pushing COPY data through
after you issue the COPY command.

            regards, tom lane

On Mar 3, 2004, at 11:20 PM, Tom Lane wrote:

> Mark Lubratt <mark.lubratt@indeq.com> writes:
>>> Huh?  You can use COPY FROM STDIN in most of our client libraries,
>>> certainly so with libpq.  What are you using?
>
>> Actually, I'm using REALbasic.
>
> Um.  I have no idea what sort of support they have, but you do need a
> client interface library that knows about the COPY data protocol.
> Typically there will be separate API calls for pushing COPY data
> through
> after you issue the COPY command.
>
>

I figured it out.  They do provide a string to push COPY FROM STDIN
through.

Thanks anyway!
Mark