17

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?

1

2 Answers 2

14
+150

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) ;
0
10
+300

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);
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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