Inserting dummy data into an empty table having a primary key integer field GENERATED ALWAYS AS IDENTITY

Question

I'm trying to generate dummy tables with dummy data for testing performance with some SQL tasks.

Based on this old thread: Is there a way to insert multiple rows into a table with default values for all columns?

I've noticed that with an IDENTITY type, it's no more possible to populate a table with dummy data using the suggested generate_series(1, N) solution.

So, how could I insert 1'000 dummy data into, for example, the following table (PG 14), taking benefit from the default value for the field name without overriding the system values for the field id:

CREATE TABLE foo (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT DEFAULT md5(random()::text)
);

Trying the following fails:

INSERT INTO foo (id) 
  SELECT generate_series(1, 1000);

-- which results in:
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.
SQL state: 428C9

As a workaround, I'm now creating the table with an extra field i as a simple INTEGER to feed this one instead of the id and then drop it after data insertion, but it's not 100% clean to me.

I was hoping for something simple and clear as this for example:

INSERT INTO foo DEFAULT VALUES (1000);

but this is obviously an invalid syntax.

Answer 1

INSERT INTO foo (name) 
  SELECT md5(random()::text) FROM generate_series(1, 1000);

Answer 2

Another method that can be used even when all columns have default expressions is to provide one column and use the OVERRIDING USER VALUE option in the INSERT in order to ignore those provided values.

insert into foo (id)
  OVERRIDING USER VALUE
select null
from generate_series(1, 10) as gs(i) ;

Tested in dbfiddle.uk (1)

---

One more method that uses MERGE - available only in Postgres version 15 (to be released next month). Thank you to Paul White who located the similar questions for SQL Server and Martin Smith and AndriyM for the answers:

• how do I insert a default row?
• Insert multiple rows into a table with only an IDENTITY column

Rewritten here with the necessary changes for Postgres and tested in dbfiddle.uk (2):

MERGE INTO
  foo AS tgt
USING
  generate_series(1, 10) AS src  -- << use your source row set here
ON
  FALSE
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES
;

---

(Wish note updated)
It would be nice if we could provide a 0-column table as input but this has not been implemented yet - the select works on its own but not the insert into foo ():

insert into foo ()
select
from generate_series(1, 10) as gs(i) ;

But this works since version 9.4. The trick is to use insert into foo - without the () - which accepts an input table with arbitrary number of columns (from zero up to the table's columns number):

insert into foo
select
from generate_series(1, 10) as gs(i) ;

---

Testing all three methods in dbfiddle.uk (3):

select version();

version
PostgreSQL 15beta3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

SELECT 1

CREATE TABLE foo (
    id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    name TEXT DEFAULT md5(random()::text)
);

CREATE TABLE

insert into foo (id)
  OVERRIDING USER VALUE
select null
from generate_series(1, 3) as gs(i) ;   -- add 3 rows

select * from foo ;

INSERT 0 3

id	name
1	08a153d31bafbef82d67c63b959ede87
2	5295cbb135445f954616fbf18031097a
3	5997bfd8e71c09bf244104797369c573

SELECT 3

MERGE INTO
  foo AS tgt
USING
  generate_series(1, 5) AS src   -- add 5 more rows
ON
  FALSE
WHEN NOT MATCHED THEN
  INSERT DEFAULT VALUES ;

select * from foo ;

MERGE 5

id	name
1	08a153d31bafbef82d67c63b959ede87
2	5295cbb135445f954616fbf18031097a
3	5997bfd8e71c09bf244104797369c573
4	73c644ac045b8dbdb4b17b2fb624538a
5	fb0ddc243709b3e3629838268ba5407f
6	af517dc84cb68ac0a5bd8c0cd2ef2ba5
7	fe33bf8a25bce0dbae0e8b80ff23af97
8	e35566ed57b7e0fe70878a03319fbb52

SELECT 8

Alternatively

MERGE INTO
  foo AS tgt
USING
  generate_series(1, 5) AS src   -- add 5 more rows
ON
  FALSE
WHEN NOT MATCHED THEN   
  INSERT (id) VALUES (DEFAULT);

insert into foo
select
from generate_series(1, 2) as gs(i) ;

select * from foo ;

INSERT 0 2

id	name
1	08a153d31bafbef82d67c63b959ede87
2	5295cbb135445f954616fbf18031097a
3	5997bfd8e71c09bf244104797369c573
4	73c644ac045b8dbdb4b17b2fb624538a
5	fb0ddc243709b3e3629838268ba5407f
6	af517dc84cb68ac0a5bd8c0cd2ef2ba5
7	fe33bf8a25bce0dbae0e8b80ff23af97
8	e35566ed57b7e0fe70878a03319fbb52
9	6c71ffb4347d6593358cda63e802ae5e
10	a07ab009a6838533df51b02ca24d26fe

SELECT 10

fiddle