Jack Douglas (imported from SE)
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 $$
      for i in 1..100000 loop
        insert into course default values;
      end loop;

Is there a way of doing the same with a single SQL statement?
Top Answer
Jasen (imported from SE)
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);

Answer #2
ypercubeᵀᴹ (imported from SE)
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)
      ins.i, ins.name
      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
      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)
    select null
    from generate_series(1, 10) as gs(i) ;

 [1]: http://www.sqlfiddle.com/#!12/21b75/3

 [2]: http://rextester.com/OSXKIT61579

 [3]: https://dba.stackexchange.com/questions/317108/

 [4]: https://dbfiddle.uk/8qgfwMPm

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.