Is there a way to insert multiple rows into a table with default values for all columns?

Question

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 $$
begin
  for i in 1..100000 loop
    insert into course default values;
  end loop;
end;$$;

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

Answer 1

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)
select 
  ins.i, ins.name
from 
  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
  (course_id)
select
  nextval('course_course_id_seq')
from
  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.

---

An idea to improve came from the similar question: Inserting dummy data into an empty table having a primary key integer field GENERATED ALWAYS AS IDENTITY, using the OVERRIDING USER VALUE option in the INSERT statement. This is available only in versions 10+, not in 9.6 and previous.
Note: it does NOT work if the table has a single column which is serial.

Tested in dbfiddle.uk:

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

insert into t (i)
  OVERRIDING USER VALUE
select null
from generate_series(1, 10) as gs(i) ;

Answer 2

There's no rule that select statements must return one or more columns.

If the select returns zero columns in several rows you will get rows inserted with all default values.

INSERT INTO table_name 
  SELECT FROM generate_series(1,10);