pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMultiple inserts without COPY
5+ messages / 2 participants
[nested] [flat]
* Multiple inserts without COPY
@ 2004-03-04 02:50 Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 04:22 ` Re: Multiple inserts without COPY Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Lubratt @ 2004-03-04 02:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple inserts without COPY
2004-03-04 02:50 Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
@ 2004-03-04 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 05:12 ` Re: Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-03-04 04:22 UTC (permalink / raw)
To: Mark Lubratt <mark.lubratt@indeq.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple inserts without COPY
2004-03-04 02:50 Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 04:22 ` Re: Multiple inserts without COPY Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-04 05:12 ` Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 05:20 ` Re: Multiple inserts without COPY Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Lubratt @ 2004-03-04 05:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple inserts without COPY
2004-03-04 02:50 Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 04:22 ` Re: Multiple inserts without COPY Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 05:12 ` Re: Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
@ 2004-03-04 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 06:01 ` Re: Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2004-03-04 05:20 UTC (permalink / raw)
To: Mark Lubratt <mark.lubratt@indeq.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Multiple inserts without COPY
2004-03-04 02:50 Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 04:22 ` Re: Multiple inserts without COPY Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 05:12 ` Re: Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 05:20 ` Re: Multiple inserts without COPY Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-04 06:01 ` Mark Lubratt <mark.lubratt@indeq.com>
0 siblings, 0 replies; 5+ messages in thread
From: Mark Lubratt @ 2004-03-04 06:01 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-03-04 06:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-04 02:50 Multiple inserts without COPY Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 04:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 05:12 ` Mark Lubratt <mark.lubratt@indeq.com>
2004-03-04 05:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-04 06:01 ` Mark Lubratt <mark.lubratt@indeq.com>
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