3

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.

2 Answers 2

3
INSERT INTO foo (name) 
  SELECT md5(random()::text) FROM generate_series(1, 1000);
1
  • Thank you, it works, indeed, but then, it kind of "duplicates" the generation of the default name value. But I guess there isn't any better solution...
    – s.k
    Sep 19, 2022 at 19:34
5

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:

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.