How to Generate Random Data in PostgreSQL

A screen built against four hand-typed rows tells you almost nothing. Pagination never triggers, and the join that will be slow comes back instantly. The first apostrophe in a surname then arrives from a real user. The DbSchema Data Generator fills PostgreSQL tables from a pattern per column, in an order you set so that foreign keys point at rows that exist. The examples use two tables:

CREATE TABLE teams (
  team_id int PRIMARY KEY,
  name    text NOT NULL
);

CREATE TABLE players (
  player_id int PRIMARY KEY,
  team_id   int NOT NULL REFERENCES teams,
  name      text NOT NULL,
  position  text
);

Generate random data with DbSchema

Put both tables on a diagram first, because the generator works from a diagram. Then open the Data Generator from Data Tools → Generate Random Data in the DbSchema menu, or right-click the header of a table on the diagram and choose Generate Random Data.

The dialog lists the tables you brought in. For each one you set how many rows to generate, and DbSchema shows whether the table is already empty or already holds data. The order of the list is the order the tables are filled, and it is the part worth checking before anything else: teams has to be populated before players, because every players.team_id has to match a team_id that already exists. Reorder the list so teams sits above players.

Clicking Generate writes the rows into the connected PostgreSQL database. DbSchema asks first whether to drop the existing data, and on a production database the answer to that question is no.

Choosing the pattern for each column

Double-click a table in the dialog to open its column pattern editor. DbSchema has already guessed a pattern for every column from its name and its data type, and the editor is where you correct the guesses. Each column carries three settings: the Pattern that produces the value, Nulls as the percentage of rows left empty, and Seed, an integer that makes the values repeatable, so two columns given the same seed produce the same sequence.

A pattern that starts with a recognised keyword generates a value of that kind. sequence:from=1;step=1; counts up from 1 in steps of 1. int:from=0;to=100 picks a number between those two bounds. date:from='01.01.2020';to='01.01.2024'; picks a day inside that range. identity: marks the column as database-managed, so the generator skips it and PostgreSQL fills it.

Anything else is read as a reverse regular expression, which generates text matching the expression, so (goalkeeper|defender|midfielder|forward) on players.position puts one of those four words in every row. Literal text takes a repository pattern inline: Team-$Sequence on teams.name produces Team-1, Team-2 and so on.

The ... button opens the DbSchema pattern repository, a library of ready patterns for first names, last names, cities, email addresses and phone numbers. Pick one, adjust it if you want, and it is applied to the column.

The foreign key gets its own keyword. load_values_from_pk on players.team_id reads the primary key values already in teams and picks one at random for each row, which is why the table order matters. Every pattern you set is stored in the .dbs model file, together with the diagram, so the same generator configuration is there tomorrow and can be committed for the rest of the team.

Generating three rows in teams with sequence:from=1;step=1; and Team-$Sequence gives:

Look at the rows before you trust them

Generated data is worth having only if it looks like the real thing. The way to find out is to read some of it. Hold Shift+Ctrl and click a table header on the DbSchema diagram: the Relational Data Editor opens inline on that table, where you can sort and filter the rows without leaving the diagram.

Selecting a row in teams refilters the players pane to the players of that team, which is the check that matters after a generation run: if load_values_from_pk did its job, every team has players and no player points at a team that is not there. When a column is wrong, correct its pattern and generate again.

The Data Generator and the Relational Data Editor are in the DbSchema Pro edition, and the Architect trial runs 15 days. Download DbSchema at https://dbschema.com/download.html, connect to your PostgreSQL database, drag the tables that need rows onto a diagram, and set the parent tables first in the list.