Skip site navigation (1) Skip section navigation (2)

update by one transaction

From: Lendvary Gyorgy <gyurika(at)prolan(dot)hu>
To: pgsql-hackers(at)postgresql(dot)org
Subject: update by one transaction
Date: 1998-06-12 08:30:17
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

I have a table for instance boci (row_number int, col2 float, col3
float). The boci table has about 10000 rows. 
I want to update col3 column of the table in every row with different
values. Desired values are in an array has 10000 elements. I want to be
the value of the col3 column in the first row equal to the first element
of the array and so on. 
I've done it with a 'for' cycle, but it is very slow. I want to do
theese updates with one transaction. 

Here is a little piece of my program:

PQexec(conn, "BEGIN");
for (i=0; i<10000; i++)
    sprintf(buff, "UPDATE boci SET col3 = %f WHERE row_number=%d",
array[i], i);
    PQexec(conn, buff);
PQexec(conn, "END");

I can't solve this problem with COPY command becouse I have to update
col3 column in every minute, but I don't want to change row_number and
col2 columns. My problem is the updating and not the creation of the
table. Creation is fast enough.
This program is very, very slow. Is there any way making this program
much faster (for instance with CURSOR or 'block write' or something
else)? Please write me a little program that describes your ideas!

Thanks for your help in advance!

Please help me!!!


pgsql-hackers by date

Next:From: Jose' Soares Da SilvaDate: 1998-06-12 10:53:12
Subject: COALESCE() or NVL()
Previous:From: Vadim MikheevDate: 1998-06-12 07:53:02
Subject: Re: AW: [HACKERS] now 6.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group