I can insert multiple rows into a table with default values for all columns the [RBAR](https://www.simple-talk.com/sql/t-sql-programming/rbar--row-by-agonizing-row/) 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?
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);
Using `generate_series()` and ctes. Tested in [rextester.com][2]: 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][3], 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][4]: 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) ; [1]: http://www.sqlfiddle.com/#!12/21b75/3 "SQLfiddle.com" [2]: http://rextester.com/OSXKIT61579 "rextester.com" [3]: https://dba.stackexchange.com/questions/317108/ "dba.stackexchange.com" [4]: https://dbfiddle.uk/8qgfwMPm "dbfiddle.uk"