Add multiple entries for sequential items postgresql

Question

I want to add multiple entries, with 1 column staying the same, and 1 column increasing by 1 every time. To do this manually I would have to do

INSERT INTO table (column1, column2) VALUES ('1'::integer, '1'::integer)returning column1, column2;
INSERT INTO table (column1, column2) VALUES ('1'::integer, '2'::integer)returning column1, column2;
INSERT INTO table (column1, column2) VALUES ('1'::integer, '3'::integer)returning column1, column2;

etc

Is there a way I could do the numbers 1 to 34000 in 1 query?

Gordon Linoff · Accepted Answer · 2020-12-24 14:39:36Z

1

Use generate_series():

INSERT INTO table (column1, column2) 
    SELECT 1, gs.n
    FROM generate_series(1, 34000) gs(n);

Note: There is no need to convert a string to an integer. In general, a number literal is fine. In any case, Postgres would convert the number literal to the correct type if needed (say int or numeric or float or whatever).

answered Dec 24, 2020 at 14:39

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Add multiple entries for sequential items postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related