Multiple inserts without COPY

[Mark Lubratt]

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


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Post by Mark Lubratt
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.

Post by Mark Lubratt
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.

Post by Mark Lubratt
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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)

[Mark Lubratt]

Post by Tom Lane

Post by Mark Lubratt
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.

Post by Tom Lane

Post by Mark Lubratt
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


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Tom Lane]

Post by Mark Lubratt

Post by Tom Lane
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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Mark Lubratt]

Post by Tom Lane

Post by Mark Lubratt

Post by Tom Lane
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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org