I can insert multiple rows into a table with default values for all columns the RBAR way:

create table course(course_id serial primary key);

do $$
  for i in 1..100000 loop
    insert into course default values;
  end loop;

Is there a way of doing the same with a single SQL statement?


Using generate_series() and ctes. Tested in rextester.com:

create table t
( tid serial primary key,
  i int default 0,
  name text default 'Jack'
) ;

with ins as
  (insert into t (i, name)               -- all the columns except any serial
   values (default, default)
   returning i, name
insert into t 
  (i, name)
  ins.i, ins.name
  ins cross join generate_series(1, 9);  -- one less than you need

For the case when there is only one column and it's a serial, I see no way to use the default. Using the generate_series is straight-forward:

insert into course
  generate_series(1, 10);

  • If there are other, more "peculiar" default values, like a UUID function or the non-standard clock_timestamp(), the statement will have to be adjusted accordingly, like the serial case.
  • If the primary key is "not the first" column defined, in this example, if i is defined first, then you can somewhat get by with a simpler version like INSERT INTO t SELECT * FROM generate_series(1, 10) which basically assigns to the first column and gives defaults to all the rest, though I couldn't figure out any other easy ways. If it's just a one off then you could also do it with primary key first, for instance by generating a series of values you'll "probably never reuse" ex: INSERT INTO t SELECT * FROM generate_series(1000000, 1000000+10) then changing numbers manually after that.
    – rogerdpack
    Jul 8 '16 at 1:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.