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

Question

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

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)
Can you point me to a good online resource to learn the syntax i should be using?

Thanks.

Why the DO block? Your example can be simplified to a single insert into widgets (widgetnumber) values (5); — user330315
– user330315, Commented Dec 23, 2016 at 16:28

user330315 · Accepted Answer · 2016-12-23 16:27:41Z

20

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

answered Dec 23, 2016 at 16:27

user330315

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

5 Comments

Happydevdays Over a year ago

how do i handle the "DEFAULT" value that autogenerates the pk?

Happydevdays Over a year ago

and do i still need to declare the variable widgetnumber?

xzilla Over a year ago

If you omit the id column from your insert (as the suggested generate series command does) then any DEFAULT values will be automatically handled by the database.

xzilla Over a year ago

you don't need plpgsql at all, just use the generate_series command in straight sql.

Happydevdays Over a year ago

xzilla, i see. but i still want to know the answer about how to insert another value before the "i" variable ... because I have to populate a few other tables in similar way. But I guess it's best to just ask another question. Thanks for the help

Kishore Uppala · Accepted Answer · 2021-05-07 18:30:31Z

1

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

answered May 7, 2021 at 18:30

Kishore Uppala

3792 silver badges7 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related