PostgreSQL – seed table with ids

Is there an elegant way to fill table with dummy data in this manner.

I need to create this table:

CREATE TEMP TABLE counter(
    id int,
    userID int,
    dateCreated date
);

Seed sequence should be:

Id – from 1 to 100000000
UserId – 1 to 1000000, and for each 1M Id userid should start over from 1. So f.e id 1000000 userid 1000000, then when id passes 1M userid should start over from 1. f.e id = 1000001, userid = 1
dateCreated should also increase every 1M id, so when counter passes 1M the next date is added. F.e id = 1000000 dateCreated 1900.01.01, id = 1000001 dateCreated = 1900.01.02.

I’ve tried with generate_series, and thats fine for id. Also with while loops, but it takes way too long.

Answer

You can use two generate_series() calls and some math to get populate the id column:

insert into counter(id, userid, datecreated)
select (t.userid - 1) * 100 + x.counter, 
        t.userid, 
        date '1900-01-01' + (t.userid - 1)
from generate_series(1,1000000) as t(userid)
  cross join generate_series(1,100) x(counter)

Advertisement

Answer