Generate test data in PostgreSQL table

Question

I want with one SELECT query to create 100 test rows of data for this table:

CREATE TABLE DOCUMENT_TEMPLATE(
   ID INTEGER NOT NULL,
   NAME TEXT,
   SHORT_DESCRIPTION TEXT,
   AUTHOR TEXT,
   DESCRIPTION TEXT,
   CONTENT TEXT,
   LAST_UPDATED DATE,
   CREATED DATE
);

Can you give me some example?

Answer 1

The most roboust method is to use specific tools like SQL Data Generator.

Simple method to generate random data is to use random() and generate_series.

INSERT INTO DOCUMENT_TEMPLATE(id,name, short_description, author,
                              description,content, last_updated,created)
SELECT id, 'name', md5(random()::text), 'name2'
      ,md5(random()::text),md5(random()::text)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100)
      ,NOW() - '1 day'::INTERVAL * (RANDOM()::int * 100 + 100)
FROM generate_series(1,100) id;

---

You could always make it more realistic writing custom functions to generate first name/last name/numbers/city/lorem_ipsum etc.

Here is my quickly written inline query:

INSERT INTO DOCUMENT_TEMPLATE(id, name, short_description, author,
                              description, content, last_updated, created)
WITH base(id, n1,n2,n3,n4,n5,n6,n7) AS
(
  SELECT id
        ,MIN(CASE WHEN rn = 1 THEN nr END) 
        ,MIN(CASE WHEN rn = 2 THEN nr END) 
        ,MIN(CASE WHEN rn = 3 THEN nr END) 
        ,MIN(CASE WHEN rn = 4 THEN nr END) 
        ,MIN(CASE WHEN rn = 5 THEN nr END) 
        ,MIN(CASE WHEN rn = 6 THEN nr END) 
        ,MIN(CASE WHEN rn = 7 THEN nr END) 
  FROM generate_series(1,100) id     -- number of rows
  ,LATERAL( SELECT nr, ROW_NUMBER() OVER (ORDER BY id * random())
             FROM generate_series(1,900) nr
          ) sub(nr, rn)
   GROUP BY id
), dict(lorem_ipsum, names) AS
(
   SELECT 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris lacus arcu, blandit non semper elementum, fringilla sodales est. Ut porttitor blandit sapien pellentesque pretium. Donec ut diam sed urna venenatis hendrerit. Nulla eros arcu, mattis vitae congue cursus, tincidunt sed turpis. Curabitur non enim diam, eget elementum dolor. Vivamus enim tortor, tempor at vehicula ac, malesuada id est. Praesent at nibh eget metus dapibus dapibus. Donec arcu orci, sagittis eu interdum vitae, facilisis quis nibh.
Mauris luctus molestie velit, at vestibulum magna cursus sit amet. Nulla in accumsan libero. Donec sed sem lectus. Mauris congue sapien et diam euismod vitae scelerisque diam tincidunt. Praesent a justo enim, vitae venenatis dolor. Donec in tortor at magna dapibus suscipit sit amet a libero. Vivamus porttitor rhoncus tellus, at luctus nisl semper bibendum. Fusce eget accumsan orci. Qout'
         ,'{"James","John","Jimmy","Jessica","Jeffrey","Jonathan","Justin","Jaclyn","Jodie"}'::text[]
)
SELECT b.id, sub.*
FROM base b
,LATERAL (
     SELECT names[b.n1 % 9+1]
           ,substring(lorem_ipsum::text, b.n2, 20)
           ,names[b.n3 % 9+1]
           ,substring(lorem_ipsum::text, b.n4, 100)
           ,substring(lorem_ipsum::text, b.n5, 200)
           ,NOW() - '1 day'::INTERVAL * (b.n6 % 365)
           ,(NOW() - '1 day'::INTERVAL * (b.n7 % 365)) - '1 year' :: INTERVAL
      FROM dict
) AS sub(name,short_description, author,descriptionm,content, last_updated, created);

db<>fiddle demo

Warning: I am aware that it could be vastly improved. Please treat it at most as a starting point.