pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert multiple Rows
7+ messages / 7 participants
[nested] [flat]
* Insert multiple Rows
@ 2003-03-03 16:38 Saint X <saintxplo@yahoo.com.ar>
0 siblings, 4 replies; 7+ messages in thread
From: saintxplo@yahoo.com.ar @ 2003-03-03 16:38 UTC (permalink / raw)
To: pgsql-sql
Hi, I have a program that need to send a Postgresql database a lot of
rows, i'm doing it using FOR an INSERT, something like this
for i = 0 to 1000
{
insert into table(item1) VALUES (i);
}
And so on, as you can imagine these consume a lot of resources and
move so slowly, that's why I'm looking for a command to send more than
one row at the time, something like
insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
The data I want to send come from variables on the program, coping
local variables is fast so i can make my query on a variable with all
the data and then send just one query to the database.
I was looking on PostgreSQL documentation and find nothing,, I think
that what I'm looking for doesn't exist,, you are my last resource, it
possible to do what I want?
BTW: I found a command on Postgre to pass a file with all the info but
the database is not on local machine and its a little hard to send it
a file.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Insert multiple Rows
@ 2003-03-03 17:32 Victor Yegorov <viy@pirmabanka.lv>
parent: Saint X <saintxplo@yahoo.com.ar>
3 siblings, 0 replies; 7+ messages in thread
From: Victor Yegorov @ 2003-03-03 17:32 UTC (permalink / raw)
To: pgsql-sql
* Saint X <saintxplo@yahoo.com.ar> [03.03.2003 19:21]:
> I was looking on PostgreSQL documentation and find nothing,, I think
> that what I'm looking for doesn't exist,, you are my last resource, it
> possible to do what I want?
Search documentation for COPY command.
Note 'stdin' and 'stdout':
...
stdin
Specifies that input comes from the client application.
stdout
Specifies that output goes to the client application.
...
--
Victor Yegorov
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Insert multiple Rows
@ 2003-03-03 17:52 Oliver Elphick <olly@lfix.co.uk>
parent: Saint X <saintxplo@yahoo.com.ar>
3 siblings, 0 replies; 7+ messages in thread
From: Oliver Elphick @ 2003-03-03 17:52 UTC (permalink / raw)
To: Saint X <saintxplo@yahoo.com.ar>; +Cc: pgsql-sql
On Mon, 2003-03-03 at 16:38, Saint X wrote:
> Hi, I have a program that need to send a Postgresql database a lot of
> rows, i'm doing it using FOR an INSERT, something like this
> for i = 0 to 1000
> {
> insert into table(item1) VALUES (i);
> }
>
> And so on, as you can imagine these consume a lot of resources and
> move so slowly, that's why I'm looking for a command to send more than
> one row at the time, something like
>
> insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
Have you started a transaction first? If not, every INSERT will be a
separate transaction, which will take much longer.
Do:
BEGIN
INSERT ...
INSERT ...
... many times ...
COMMIT
> The data I want to send come from variables on the program, coping
> local variables is fast so i can make my query on a variable with all
> the data and then send just one query to the database.
>
> I was looking on PostgreSQL documentation and find nothing,, I think
> that what I'm looking for doesn't exist,, you are my last resource, it
> possible to do what I want?
>
> BTW: I found a command on Postgre to pass a file with all the info but
> the database is not on local machine and its a little hard to send it
> a file.
Using COPY will be even faster.
To send a file from the remote system, run psql on the remote system and
use \copy rather than COPY.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"A new commandment I give to you, that you love one
another, even as I have loved you."
John 13:34
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Insert multiple Rows
@ 2003-03-04 10:54 Oleg Samoylov <olleg@telecom.mipt.ru>
parent: Saint X <saintxplo@yahoo.com.ar>
3 siblings, 1 reply; 7+ messages in thread
From: Oleg Samoylov @ 2003-03-04 10:54 UTC (permalink / raw)
To: pgsql-sql
Hi,
Saint X wrote:
> Hi, I have a program that need to send a Postgresql database a lot of
> rows, i'm doing it using FOR an INSERT, something like this
> for i = 0 to 1000
> {
> insert into table(item1) VALUES (i);
> }
>
> And so on, as you can imagine these consume a lot of resources and
> move so slowly, that's why I'm looking for a command to send more than
> one row at the time, something like
>
> insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
You can do instead:
insert into table (item1) values ('1');
insert into table (item1) values ('2');
....
insert into table (item1) values ('1000');
As single query.
But better use COPY command or prepared statment.
Inserts slow commonly due to indexis and foreing keys.
--
Olleg Samoylov
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Insert multiple Rows
@ 2003-03-04 11:19 Gary Stainburn <gary.stainburn@ringways.co.uk>
parent: Oleg Samoylov <olleg@telecom.mipt.ru>
0 siblings, 0 replies; 7+ messages in thread
From: Gary Stainburn @ 2003-03-04 11:19 UTC (permalink / raw)
To: Oleg Samoylov <olleg@telecom.mipt.ru>; pgsql-sql
On Tuesday 04 Mar 2003 10:54 am, Oleg Samoylov wrote:
> Hi,
>
> Saint X wrote:
> > Hi, I have a program that need to send a Postgresql database a lot of
> > rows, i'm doing it using FOR an INSERT, something like this
> > for i = 0 to 1000
> > {
> > insert into table(item1) VALUES (i);
> > }
> >
> > And so on, as you can imagine these consume a lot of resources and
> > move so slowly, that's why I'm looking for a command to send more than
> > one row at the time, something like
> >
> > insert into table (item1) values ('1' / '2' / '3' / '4' ... / '1000');
>
> You can do instead:
>
> insert into table (item1) values ('1');
> insert into table (item1) values ('2');
> ....
> insert into table (item1) values ('1000');
>
> As single query.
>
> But better use COPY command or prepared statment.
>
> Inserts slow commonly due to indexis and foreing keys.
If you use a 'prepared' insert within a transaction, would that speed things
up - maybe by defering index updates?
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Insert multiple Rows
@ 2003-03-05 08:38 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 7+ messages in thread
From: Christoph Haller @ 2003-03-05 08:38 UTC (permalink / raw)
To: pgsql-sql; +Cc: gary.stainburn@ringways.co.uk
>
> If you use a 'prepared' insert within a transaction, would that speed
things
> up - maybe by defering index updates?
>
I doubt it.
From: Neil Conway <neilc@samurai.com>
... I'd suspect that for most INSERT queries, the
parsing/rewriting/planning stages are relatively inexpensive, and the
bulk of the runtime is spent in the executor (and writing WAL records,
etc). So my guess would be that you won't see that much of a performance
improvement from this...
I've noticed a real performance boost using COPY
something like ...
PQexec("COPY xxx FROM stdin");
for (...)
{
sprintf(buf, "%d\t%d\t...\n",
values[0], values[1], ... values[n]);
PQputline(conn, buf);
}
PQputline(conn, "\\.\n");
PQendcopy(conn);
runs like hell.
Regards, Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Insert multiple Rows
@ 2003-03-09 05:58 mark carew <markcarew@bigpond.com>
parent: Saint X <saintxplo@yahoo.com.ar>
3 siblings, 0 replies; 7+ messages in thread
From: mark carew @ 2003-03-09 05:58 UTC (permalink / raw)
To: pgsql-sql
Hi
HTH
COPY [ BINARY ] table [ WITH OIDS]
FROM { 'filename' | stdin}
[ [ USING ] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null_string' ]
COPY [ BINARY ] table [ WITH OIDS]
TO { 'filename' | stdin}
[ [ USING ] DELIMITERS 'delimiter' ]
[ WITH NULL AS 'null_string' ]
Regards Mark Carew
Brisbane Australia
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-03-09 05:58 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-03 16:38 Insert multiple Rows Saint X <saintxplo@yahoo.com.ar>
2003-03-03 17:32 ` Victor Yegorov <viy@pirmabanka.lv>
2003-03-03 17:52 ` Oliver Elphick <olly@lfix.co.uk>
2003-03-04 10:54 ` Oleg Samoylov <olleg@telecom.mipt.ru>
2003-03-04 11:19 ` Gary Stainburn <gary.stainburn@ringways.co.uk>
2003-03-09 05:58 ` mark carew <markcarew@bigpond.com>
2003-03-05 08:38 Re: Insert multiple Rows Christoph Haller <ch@rodos.fzk.de>
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