How to bulk INSERT to a table with a loop in Postgres

Question

To test a report against data, I want to INSERT test data into a table in Postgres SQL.

For example:

INSERT INTO call_logs (phonenumber,timeofcall) VALUES ('+12121001001','2014-12-23T07:01:00.000+00:00')

But I want to loop, say, 59 times, to have phone numbers 2121001001, 1002, 1003, etc with times of call 07:01, 07:02, 07:03, etc.

How do I do that with a loop?

Thanks.

Clodoaldo Neto · Accepted Answer · 2014-12-25 12:00:46Z

3

You do not need a loop. Use generate_series:

insert into call_logs (column1, phonenumber,timeofcall)
select
    'constant_value',
    '+' || generate_series(12121001001, 12121001059),
    generate_series(
        '2014-12-23t07:01:00.000+00:00'::timestamp,
        '2014-12-23t07:59:00.000+00:00',
        '1 minute'
    )

edited Dec 25, 2014 at 12:00

answered Dec 24, 2014 at 20:23

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

Amarundo Over a year ago

Will that increase the phone number too? 212-100-1001, 212-100-1002, etc. And also, I left a few columns out for simplicity, but I do have a few other columns that will not change the value, though. How will the SQL statement be then? Thanks.

Denis de Bernardy Over a year ago

A few things to be wary about when using generate_series() in a select statement.

Collectives™ on Stack Overflow

How to bulk INSERT to a table with a loop in Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related