One SQL to Generate 2000 Test Rows: Stop Hand-Writing VALUES

Overall Approach: Turn Queried Rows into Inserted Rows

The core pattern is

INSERT INTO table (col1, col2, ...) SELECT ... FROM data_source;

Instead of writing thousands of VALUES rows manually, the SELECT determines how many rows are inserted. Each column value is derived from the data source; some columns can be set to NULL to leave them empty. The engine counts the rows automatically.

generate_series: The Row Engine

FROM generate_series(1, 2000) g

is a set-returning function that produces consecutive integers from 1 to 2000, yielding exactly 2000 rows. The alias g becomes the basis for every derived column — g, 100000+g, LPAD(g::text, ...) are all transformations of g. Changing the upper bound (e.g., to 5000) instantly changes the row count. generate_series also supports step increments (e.g., generate_series(1, 99, 2)) and date sequences (e.g.,

generate_series('2026-01-01'::date, '2026-12-31'::date, '1 month')

).

::text and ||: Cast Then Concatenate

generate_series

outputs integers. To embed them in strings, cast to text first: g::text (PostgreSQL shorthand for CAST(g AS text)). Then use the || concatenation operator: 'Test' || g yields 'Test1', 'Test2', …; 'Prefix' || LPAD(g::text, 4, '0') yields 'Prefix0001', 'Prefix0002', … Concatenation chains are allowed: 'a' || 'b' || 'c'.

LPAD: Zero-Pad to Fixed Width

Without zero-padding, string sorting puts '10' before '2'. LPAD(g::text, 4, '0') pads the string to length 4 with '0' on the left: 1 → '0001', 10 → '0010', 2000 → '2000'. Parameters: LPAD(string, target_length, pad_char). The counterpart RPAD pads on the right.

TIMESTAMP Literals: Explicit Type Declaration

Write TIMESTAMP '2026-06-15 16:00:00' instead of relying on implicit casts. This makes cross-database execution more stable and debugging easier.

ON CONFLICT: Make the Script Re-runnable

Test data scripts often fail on duplicate primary keys. ON CONFLICT (source_type, source_id) DO NOTHING; skips rows where the unique constraint on ( source_type, source_id) already exists. Prerequisite: a unique constraint or unique index on those columns. Without it, PostgreSQL raises

there is no unique or exclusion constraint matching the ON CONFLICT specification

. Create the index first: CREATE UNIQUE INDEX ON table (source_type, source_id);. The benefit: re-running the script only inserts missing rows. For upsert behavior, replace DO NOTHING with DO UPDATE SET col = EXCLUDED.col, ....

Complete Example: Column-by-Column Walkthrough

The full statement inserts into mid_year_impl_report_detail with 23 columns. Fixed values are supplied directly (e.g., '成都', '3', TIMESTAMP '2026-06-15 16:00:00'). Key columns derive from g: class_name:

'测试送外' || g

train_no

:

'成都2026T' || LPAD(g::text, 4, '0')

source_id

: 100000 + g (produces 100001–102000, naturally unique)

The ON CONFLICT clause ensures idempotency. One execution inserts all 2000 rows without any manual typing.

Common Variations: Add Randomness

To avoid uniform-looking data, inject randomness in the SELECT list:

Random integer 0–99: floor(random() * 100)::int Random timestamp within last 30 days: now() - (random() * 30)::int * interval '1 day' Random pick from array:

(ARRAY['技术', '管理', '通用'])[1 + floor(random() * 3)::int]

Each row then gets distinct values.

Portability: PostgreSQL / KingBaseES vs MySQL

KingBaseES V8R6 is PostgreSQL-compatible, so the above syntax works directly. For MySQL, equivalents differ:

generate_series : Not supported; use stored procedures or recursive CTEs.

:: cast : Not supported; use CAST().

Conflict handling : Use ON DUPLICATE KEY UPDATE or INSERT IGNORE.

|| concatenation : Not supported; use CONCAT().

Closing Thought

Writing a robust, parameterized INSERT...SELECT script takes a few extra minutes but pays off every time you need to regenerate test data. Change one number to produce 5000 rows instead of 2000. Invest ten minutes now to save ten minutes every future run.