CODEWITHSUNDEEP
postgresql
Happydevdays
Happydevdays

Reputation: 2082

how to create a range of values and then use them to insert data into postgresql database

Background Information:

I need to auto generate a bunch of records in a table. The only piece of information I have is a start range and an end range. Let's say my table looks like this:

     id 
     widgetnumber

The logic needs to be contained within a .sql file.
I'm running postgresql

Code

This is what I have so far... as a test... and it seems to be working: 

DO $$
DECLARE widgetnum text;
BEGIN
    SELECT 5 INTO widgetnum;

    INSERT INTO widgets VALUES(DEFAULT, widgetnum);
END $$;

And then to run it, I do this from a command line on my database server: 

testbox:/tmp# psql -U myuser -d widgets -f addwidgets.sql 
DO

Questions

  1. How would I modify this code to loop through a range of widget numbers and insert them all? for example, I would be provided with a start range and an end range (100 to 150 let's say)

  2. Can you point me to a good online resource to learn the syntax i should be using?

Thanks.

Upvotes: 9

Views: 12358

Answers (2)

Kishore Uppala
Kishore Uppala

Reputation: 349

dvdrental=# \d test
                       Table "public.test"
 Column |          Type          | Collation | Nullable | Default
--------+------------------------+-----------+----------+---------
 id     | integer                |           |          |
 name   | character varying(250) |           |          |

dvdrental=# begin;
BEGIN
dvdrental=# insert into test(id,name) select generate_series(1,100000),'Kishore';
INSERT 0 100000

Upvotes: 1

user330315
user330315

Reputation:

How would I modify this code to loop through a range of widget numbers and insert them all?

You can use generate_series() for that. 

insert into widgets (widgetnumber)
select i
from generate_series(100, 150) as t(i);

Can you point me to a good online resource to learn the syntax i should be using?

https://www.postgresql.org/docs/current/static/index.html

Upvotes: 20

Related Questions