Subject: Re: Insert multiple Rows
From: Oliver Elphick <olly@lfix.co.uk>
To: Saint X <saintxplo@yahoo.com.ar>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <6f079210.0303030838.21f184af@posting.google.com>
References: <6f079210.0303030838.21f184af@posting.google.com>
Content-Type: text/plain
Organization: LFIX Limited
Message-Id: <1046713959.2173.144.camel@linda.lfix.co.uk>
Mime-Version: 1.0
Date: 03 Mar 2003 17:52:41 +0000
Content-Transfer-Encoding: 7bit

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