How does postgresql insert multiple rows with default values for all columns?
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?
Postgresql insert multiple rows with default values for all columns- 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.namefrom
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) ;