45

I'm using Postgres, and I have a large number of rows that need to be inserted into the database, that differ only in terms of an integer that is incremented. Forgive what may be a silly question, but I'm not much of a database guru. Is it possible to directly enter a SQL query that will use a loop to programatically insert the rows?

Example in pseudo-code of what I'm trying to do:

for i in 1..10000000 LOOP
  INSERT INTO articles VALUES(i)
end loop;
William Jones
  • 18,089
  • 17
  • 63
  • 98
  • 1
    It'd help to know what version of PostgreSQL you're using. – OMG Ponies Sep 21 '10 at 21:11
  • 1
    You need to use a LOOP - see the examples: http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x20238_002.htm – OMG Ponies Sep 21 '10 at 21:28
  • @OMG Ponies Ouch thanks for the input. I don't suppose you could provide any pointers on the general idea of the syntax I'll need? I was looking at the loop documentation originally before posting this, which is how I ended up with that pseudo-code that I wrote in above, but I can't seem to get it into any sort of shape that Postgres seems to understand. – William Jones Sep 21 '10 at 21:30
  • @William Jones: The link provides PostgreSQL syntax - your FOR loop syntax is identical. – OMG Ponies Sep 21 '10 at 21:48
  • Also, doing this is straight forward(albeit not that efficient) using a shell and the psql command line tool. Are you on a *nix ? – nos Sep 21 '10 at 22:15

4 Answers4

75

Hopefully I've understood what you need (tested on 8.2):

INSERT INTO articles (id, name)
SELECT x.id, 'article #' || x.id
  FROM generate_series(1,10000000) AS x(id);
vyegorov
  • 21,787
  • 7
  • 59
  • 73
Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
14

In SQL Server you can do:

DECLARE @i int
SET @i = 1

WHILE @i<1000000
    BEGIN
        INSERT INTO articles
        VALUES @i
        SET @i=@i+1
    END
JNK
  • 63,321
  • 15
  • 122
  • 138
  • 2
    Someone like OMGPonies can tell us if this will work in postgre – JNK Sep 21 '10 at 20:27
  • My pgPLSQL is weak, but: http://www.linuxtopia.org/online_books/database_guides/Practical_PostgreSQL_database/PostgreSQL_x20238_002.htm I'd use the recursive WITH if possible (like you see in nos' answer), but I don't have an instance to test so it looks like a loop is required (for prior to 8.4, at a minimum). But with PostgreSQL v9 adding anonymous pgPLSQL blocks (finally, dunno how long Oracle supported), it's easier for one time use. – OMG Ponies Sep 21 '10 at 21:13
4

Afaik, you can't write a loop directly as SQL, you'd have to create a stored procedure to do it.

This will do though (but someone can probably make it cleaner)

INSERT INTO articles WITH RECURSIVE i AS
(
 SELECT 1 x
  UNION ALL
 SELECT x + 1
  FROM i
 WHERE x < 10000000 
)
 SELECT x
 FROM i;
nos
  • 223,662
  • 58
  • 417
  • 506
  • The [recursive WITH is 8.4+](http://www.postgresql.org/docs/8.4/static/queries-with.html), but there's [nothing in the documentation about supporting it in an INSERT statement](http://www.postgresql.org/docs/9.0/static/sql-insert.html). That's not necessarily definitive... – OMG Ponies Sep 21 '10 at 21:09
  • OP confirmed using v8.3, can't use recursive WITH :( – OMG Ponies Sep 21 '10 at 21:26
1

To people still visiting and want proper answer in the author's format, here is the answer.

do $$
begin
for i in 1..1000000 loop
insert into articles values(i);
end loop;
end;
$$;
Nick
  • 11
  • 3