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(dot)Elphick(at)lfix(dot)co(dot)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