Automatically Generate PostgreSQL Data

Automatically Generate PostgreSQL Data February 28, 2023 by ~glorifiedgluer

In order to generate PostgreSQL data you can take advantage of the generate_series function and Data-Modyfing Statements in WITH. Consider the following structure:

  CREATE TABLE Product(
      ProductID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      Model TEXT NOT NULL,
      Color TEXT NOT NULL
  );

  CREATE TABLE Review(
      ReviewID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      ProductID INTEGER NOT NULL,
      Rating INTEGER NOT NULL,
      FOREIGN KEY (ProductID) REFERENCES Product(ProductID)
  );

  CREATE TABLE UserReviewVote(
      UserReviewVoteID INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
      ReviewID INTEGER NOT NULL,
  );

You could generate the data with this:

  WITH
    insProduct AS (
        INSERT INTO Product (Model, Color)
        SELECT
            md5(random()::text),
            md5(random()::text)
        FROM generate_series(1, 1000000) s
        RETURNING ProductID
    ),
    insReview AS (
        INSERT INTO Review(ProductID, Rating)
        SELECT p.ProductID, random() * 5
        FROM insProduct p
        RETURNING ReviewID
    )
    INSERT INTO UserReviewVote (ReviewID, ReviewType)
    SELECT r.ReviewID, 'Helpful'
    FROM insReview r