From: Oliver Elphick <olly@lfix.co.uk>
To: Saint X <saintxplo@yahoo.com.ar>
Cc: pgsql-sql@postgresql.org
Subject: Re: Insert multiple Rows
Date: 03 Mar 2003 17:52:41 +0000
Message-ID: <1046713959.2173.144.camel@linda.lfix.co.uk> (raw)
In-Reply-To: <6f079210.0303030838.21f184af@posting.google.com>
References: <6f079210.0303030838.21f184af@posting.google.com>
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
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 using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olly@lfix.co.uk, saintxplo@yahoo.com.ar
Subject: Re: Insert multiple Rows
In-Reply-To: <1046713959.2173.144.camel@linda.lfix.co.uk>
* 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