Re: Insert multiple Rows

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
haveloved you."                                       John 13:34