16

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?

Jack Douglas
  • 37,076
  • 14
  • 93
  • 173
  • Extensive answer to a closely related question on SO: http://stackoverflow.com/questions/23794405/generate-default-values-in-a-cte-upsert-using-postgresql-9-3/23797357#23797357 – Erwin Brandstetter Jan 18 '15 at 02:05

1 Answers1

9

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.
ypercubeᵀᴹ
  • 92,106
  • 13
  • 189
  • 284
  • 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 08 '16 at 01:09