How to avoid multiple insert in PostgreSQL

Question

In my query im using for loop. Each and every time when for loop is executed, at the end some values has to be inserted into table. This is time consuming because for loop has many records. Due to this each and every time when for loop is executed, insertion is happening. Is there any other way to perform insertion at the end after the for loop is executed.

For i in 1..10000 loop .... 
    --coding 
    insert into datas.tb values(j,predictednode); -- j and predictednode are variables which will change for every loop
End loop;

Instead of inserting each and every time i want the insertion should happen at the end.

how your variables are changing? it could be possible to do this in one insert — roman
– roman, Commented Sep 14, 2013 at 12:08
j variable will have the value from another for loop and predicted node will have the value based on same calculation — user2659199
– user2659199, Commented Sep 14, 2013 at 12:14
You could use a before insert trigger to raise an error or redirect to the correct table. — Navin Nair Kottayil
– Navin Nair Kottayil, Commented Sep 14, 2013 at 12:16
@user2659199 it highly depends on type of calculation your' doing. It could be aggregate or some simple calculation or anything else. It would be easier for you to get good answer if your question would be more specific — roman
– roman, Commented Sep 14, 2013 at 12:27

Clodoaldo Neto · Accepted Answer · 2013-09-14 12:10:05Z

1

If you show how the variables are calculated it could be possible to build something like this:

insert into datas.tb
select
    calculate_j_here,
    calculate_predicted_node_here
from generate_series(1, 10000)

answered Sep 14, 2013 at 12:10

Clodoaldo Neto

127k30 gold badges250 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2659199 Over a year ago

j variable will have the value from another for loop and predicted node will have the value based on same calculation

Clodoaldo Neto Over a year ago

@user If you don't show it you will only get very generic answers.

user2659199 Over a year ago

For j in 1..10000 loop -- 10000 for example For i in 1..10000 loop .... predictednode = 34; -- consider some value insert into datas.tb values(j,predictednode); -- j and predictednode are variables which will change for every loop End loop; End Loop;

Andrew Lazarus · Accepted Answer · 2013-09-18 17:17:54Z

One possible solution is to build a large VALUES String. In Java, something like

StringBuffer buf = new StringBuffer(100000); // big enough?
for ( int i=1; i<=10000; ++i ) {
    buf.append("(")
       .append(j)
       .append(",")
       .append(predicted_node)
       .append("),"); // whatever j and predict_node are
}
buf.setCharAt(buf.length()-1, ' '); // kill last comma
String query = "INSERT INTO datas.tb VALUES " + buf.toString() + ";"
// send query to DB, just once

The fact j and predict_node appear to be constant has me a little worried, though. Why are you putting a constant in 100000 times?

Another approach is to do the predicting in a Postgres procedural language, and have the DB itself calculate the value on insert.

Collectives™ on Stack Overflow

How to avoid multiple insert in PostgreSQL

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related