PostgreSQL test data generator

Two ways in, and they are not equivalent

Set the SQL export's dialect to postgres and identifiers are double-quoted, which is what Postgres expects. You get an optional CREATE TABLE followed by inserts, batched 250 rows to a statement when batching is on:

CREATE TABLE "customers" (
  "id" INT,
  "full_name" VARCHAR(100),
  "email" VARCHAR(120),
  "city" VARCHAR(60),
  "signup_date" DATE
);

INSERT INTO "customers" ("id", "full_name", "email", "city", "signup_date") VALUES
  (1, 'Elena Rossi', 'elena.rossi7@example.org', 'Lyon', '2024-03-18'),
  (2, 'Marco Schneider', 'marco_schneider@example.com', 'Hamburg', '2024-07-02');

That is convenient up to a few thousand rows. Past that, use CSV and \copy instead — bulk loading bypasses per-statement overhead and is commonly an order of magnitude faster:

psql mydb -c "TRUNCATE customers RESTART IDENTITY CASCADE"
psql mydb -c "\copy customers FROM 'customers.csv' CSV HEADER"

Note \copy (client-side, reads a file on your machine) rather than COPY (server-side, needs the file on the database host and superuser rights). The backslash form is almost always the one you want locally and in CI.

Types the generator infers

Each field carries a SQL type used by CREATE TABLE — INT, VARCHAR(n), DATE, TIME, DECIMAL(10,6), CHAR(36) for UUIDs. These are conservative and portable rather than idiomatic Postgres. If you are creating the table yourself, prefer the native types: uuid instead of CHAR(36), timestamptz instead of a string, numeric for money, and text instead of VARCHAR(n) — Postgres gains nothing from the length limit. Generate against your real DDL; treat the generated one as a starting point.

NULL versus empty string

Set Blank % on a field and the SQL export emits NULL, while the CSV export emits an empty field. Those two are the same thing to \copy by default — an unquoted empty field becomes NULL — but an explicitly quoted empty string does not. If the distinction matters in your schema, control it at load time:

\copy customers FROM 'customers.csv' WITH (FORMAT csv, HEADER, NULL '')

This is worth being deliberate about. A column that is NOT NULL in production but full of empty strings in development hides bugs until the day it does not.

Sequences after a load

The mistake that catches everyone: loading explicit primary keys does not advance the underlying sequence, so the first insert your application makes collides with row 1. Either truncate with RESTART IDENTITY before loading, or fix the sequence afterwards:

SELECT setval(
  pg_get_serial_sequence('customers', 'id'),
  (SELECT COALESCE(MAX(id), 1) FROM customers)
);

Foreign keys and load order

Generate parent tables first and feed their key column into a Custom List field on the child schema, so every child row references a parent that exists — repeating some IDs gives you the realistic skew a uniform draw never will. Then load parents before children, or defer the checks and let one transaction sort it out:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
\copy customers FROM 'customers.csv' CSV HEADER
\copy orders    FROM 'orders.csv'    CSV HEADER
COMMIT;

Loading 100,000 rows quickly

• Drop non-essential indexes first, recreate after. Every index is maintained per inserted row; this is usually the single biggest win.
• One transaction. Autocommit forces a flush per statement.
• Consider UNLOGGED for throwaway development tables — no WAL, much faster, and you genuinely do not care if it survives a crash.
• Raise maintenance_work_mem before recreating indexes, then ANALYZE so the planner has statistics.

Keep it separate from production

Use an obviously named development database, never production credentials, and remember that generated values are for testing rather than identity or payment verification — IBANs fail their checksum and card numbers are published test values by design. The methodology page has the full picture.

Common questions

Related

• MySQL test data — the same job with LOAD DATA.
• MongoDB test data — JSON and NDJSON via mongoimport.
• Database test data guide — schema design and constraints.
• SQL export reference — dialects, batching and options.