#Seed Data Generation

The seed generator produces type-aware test data for all tables in a pgdesign schema. It respects FK dependencies, CHECK constraints, UNIQUE constraints, semantic types, and column nullability. Output is either INSERT statements or PostgreSQL COPY format.

Source: internal/seed/seed.go, internal/seed/regen.go

#Overview

The entry point is Generate(schema, rowsPerTable, rng, cfg). It walks tables in topological order (Schema.TableOrder()), generates row data for each table, and emits SQL. The *rand.Rand parameter enables deterministic output when seeded with a fixed value.

The generation pipeline per table:

1. Analyze CHECK constraints to extract hints (range, length, regex)
2. Determine which FK columns participate in cycles (need deferred UPDATE)
3. Filter out columns that should not appear in output (generated columns, ALWAYS identity, auto_id semantic type)
4. For COPY format, additionally exclude serial/bigserial/smallserial columns (COPY cannot use DEFAULT)
5. Build unique constraint tracking (including PK as a unique constraint)
6. Generate rows with retry loop for uniqueness (up to 100 attempts per row)
7. Emit in the configured format
8. After all tables, emit deferred UPDATE statements for FK cycle resolution

#Regex String Generation

Located in internal/seed/regen.go, the regex string generator uses Go's regexp/syntax package to parse CHECK constraint patterns into an AST and walk it to produce strings that match the pattern. This approach generates valid data directly from the regex structure rather than using rejection sampling, which would be slow for complex patterns with narrow valid ranges. The generator handles character classes, alternation, repetition quantifiers, captures, and anchors.

#Algorithm

1. Parse the regex pattern with regexp/syntax.Parse() using Perl syntax
2. Simplify the AST via re.Simplify()
3. Walk the AST recursively via regenWalk, handling each syntax.Op type:

- OpLiteral: emit the literal runes directly - OpCharClass: pairs of [lo, hi] ranges; pick a range uniformly, then pick a rune uniformly within that range - OpAnyCharNotNL / OpAnyChar: random printable ASCII character (code points 33-126) - OpCapture: recurse into the sub-expression - OpConcat: generate each sub-expression in sequence - OpAlternate: pick one branch uniformly at random - OpStar (0+): generate 0 to regenDefaultRepeat (5) repetitions - OpPlus (1+): generate 1 to regenDefaultRepeat (5) repetitions - OpQuest (0 or 1): 50% chance of generating the sub-expression - OpRepeat: generate between Min and Max repetitions; if Max is unbounded (-1), cap at Min + regenDefaultRepeat - Anchors (OpBeginLine, OpEndLine, OpBeginText, OpEndText), OpWordBoundary, OpNoWordBoundary, OpEmptyMatch, OpNoMatch: no output

1. Return the generated string

#Use Cases

• CHECK constraints with regex patterns (e.g., col ~ '^[A-Z]{3}-\d{4}$')
• Ensures generated data passes constraint validation without rejection sampling

#Distributions

#Zipf Distribution (FK References)

• Parameters: s=1.5, v=1, upper bound = referenced table's row count minus 1
• Implementation: Go's rand.NewZipf with the parent table's generated value list
• Applied to all FK columns via the ref semantic type and during cycle resolution UPDATEs

In real databases, FK distributions are highly skewed -- some parent rows are referenced far more often than others (popular categories, active users). A Zipf distribution with s=1.5 models this: the first few parent rows absorb the majority of references, producing realistic skew rather than uniform spread.

#Log-Normal Distribution (Money)

• Used when SemanticTypeName == "money"
• Formula: exp(NormFloat64() * 1.0 + 4.0), then abs(), then capped at 999999
• Produces values centered around exp(4) ~= 55 with a long right tail
• Many small amounts, few large amounts -- representative of financial data (prices, transactions, balances)

#Uniform Distribution (Default)

• Used for all other column types
• Integers: Intn(10000), dates: random day within past year, timestamps: random date + random HH:MM:SS
• Range types generate a random lower bound and a random span

#NULL Injection

• 10% probability for nullable columns (checked via col.NotNull == false)
• Applied before type-specific generation -- if the coin flip says NULL, the value is NULL regardless of type
• NOT NULL columns never receive NULL values

#Constraint-Aware Generation

#CHECK Constraint Parsing

The analyzeChecks function parses each table's CHECK constraints using the sqlexpr SQL expression parser and extracts generation hints for three recognized constraint patterns. These hints guide the value generator to produce data that satisfies constraints on the first attempt rather than relying on retry loops. The three patterns cover the most common CHECK constraint forms found in production schemas: numeric range bounds, string length limits, and regex format validation.

Range constraints (col >= 18 AND col <= 65): The extractRange function recognizes an AND node with comparison operators (>=, <=, >, <) on both sides referencing the same column. It handles both col >= X and X <= col orientations. Strict inequalities (>, <) are adjusted by 1 to produce inclusive bounds. Generated values are uniformly distributed within [min, max].

Length constraints (length(code) <= 5): The extractLength function recognizes a comparison where the left side is a function call to length, char_length, or character_length. The base string is generated normally via generateBaseString, then truncated to the maximum length.

Regex constraints (code ~ '^[A-Z]{3}-[0-9]{4}$'): The extractRegex function recognizes ~ or ~* operators where the right side is a string literal. The pattern is passed to regenFromPattern to generate a matching string directly.

#Priority

CHECK hints take priority over default type-based generation. When a column matches a hint, the hint's strategy is used instead of the type's default. If regenFromPattern fails (invalid regex), the generator falls back to default string generation.

#UNIQUE Enforcement

• A uniqueTracker maintains a set of seen value combinations per constraint
• Both explicit UNIQUE constraints and the primary key are tracked
• For composite constraints, the tuple of column values is joined with a null byte separator
• Up to 100 retries per row to find a non-duplicate combination
• If all attempts fail, the last generated values are used anyway (no hard error)

#Column Filtering

Certain column types are excluded from generated INSERT or COPY output because PostgreSQL computes their values automatically. Generated columns use server-side expressions, identity columns are assigned by sequences, and serial types use implicit sequences. Excluding these columns prevents conflicts with PostgreSQL's automatic value assignment and ensures the generated SQL is valid for both INSERT and COPY formats.

• Generated columns (col.Generated != ""): PostgreSQL computes these automatically
• ALWAYS identity columns (col.Identity == "ALWAYS"): PostgreSQL assigns values automatically
• auto_id semantic type: treated as server-generated; emits DEFAULT in INSERT format
• Serial types (serial, bigserial, smallserial): emit DEFAULT in INSERT format; excluded entirely in COPY format (COPY cannot use DEFAULT)

#FK Cycle Handling

#The Problem

Circular FK references create a chicken-and-egg problem where you cannot insert into either table without the other existing first. For example, if departments references employees via head_id and employees references departments via dept_id, neither table can be populated first because the FK constraint would fail. The seed generator solves this using a two-pass approach that temporarily inserts NULL values for cycle-breaking FK columns, then backfills them with UPDATE statements after all tables have initial data.

#Two-Pass Solution

The generator uses Schema.CycleGroups, which is pre-computed by the model builder during schema resolution, to identify which tables participate in FK cycles and which specific FK columns create the circular dependency. The cycle groups are computed once and reused by both the DDL generator (for deferred constraint creation) and the seed generator (for two-pass NULL-then-UPDATE insertion). This ensures consistent cycle handling across all code paths that need to deal with circular references.

1. Pass 1 -- INSERT with NULLs: For tables in a cycle, FK columns pointing to cycle peers are set to NULL in the initial INSERT. All other columns are generated normally. PK values are tracked for later use.
2. Pass 2 -- UPDATE with values: After all tables have been seeded, deferred UPDATE statements backfill the NULL FK columns with Zipf-distributed references to the now-existing parent rows. Each row gets its own UPDATE statement keyed on the PK.

#S001 Diagnostic

If a cycle-breaking FK column is declared NOT NULL, the generator emits a S001 warning (severity: Warning). The two-pass strategy requires nullable cycle columns. The developer must either make one FK in the cycle nullable or restructure the schema to break the cycle.

#Output Formats

#INSERT Format

• Standard INSERT INTO schema.table (col1, col2, ...) VALUES with value tuples
• Batch INSERT: up to 1000 rows per statement (hardcoded batchSize = 1000). Tables with more rows produce multiple INSERT statements.
• Each value tuple is indented with two spaces
• SQL literal escaping: single quotes doubled ('')
• Arrays use ARRAY[val1, val2, ...] syntax
• Function calls (e.g., NOW() - interval '...') used for timestamp semantic types
• Type casts (e.g., ::jsonb, ::tsvector) appended where needed

#COPY Format

PostgreSQL COPY ... FROM stdin format for high-performance bulk loading, typically 5 to 10 times faster than INSERT for large datasets. COPY bypasses the SQL parser and writes directly to the table's heap, making it the preferred format when seeding databases with thousands of rows or more. The format uses tab-separated values with backslash escaping, represents NULL as \N, and terminates with a \. line. Serial columns are excluded from COPY output because COPY cannot use DEFAULT values.

• Header: COPY schema.table (col1, col2, ...) FROM stdin;
• Rows: tab-separated values, one per line
• NULL: represented as \N
• Booleans: t / f (not true / false)
• Arrays: PostgreSQL array literal format {val1,val2,val3} (converted from ARRAY[...] syntax)
• Type casts stripped (e.g., ::jsonb removed)
• Function calls (NOW(), etc.) converted to literal timestamps
• Escaping via escapeCopy: backslash-escapes for \, \t, \n, \r
• Terminator: \. on its own line
• Serial columns excluded from the column list (COPY cannot use DEFAULT)

The toCopyValue function handles the conversion from INSERT-format SQL values to COPY-format values, including stripping type casts, converting array syntax, unquoting SQL strings, and re-escaping for the COPY protocol.

#Transaction Wrapping

• Default mode: output is wrapped in BEGIN; / COMMIT;
• Apply mode (cfg.Apply == true): no transaction wrapping, since the caller manages its own transaction

#Edge-Case Mode (--mode edge-cases)

When cfg.Mode == "edge-cases", the generator forces exactly one row per table regardless of the rowsPerTable parameter and produces boundary values designed to stress-test application code and database constraints. Edge-case values include empty strings, zero values, minimum and maximum integer bounds, epoch timestamps, empty collections, and NULL for all nullable columns. Integer edge-case values rotate between zero and the type's maximum value using a per-type counter, so multiple integer columns in the same table receive different boundary values.

Values by type:

Integer edge-case values rotate between 0 and MAX via a per-PGType counter (intTypeCounter), so if a table has two integer columns, the first gets 0 and the second gets MAX_INT.

#Semantic Type Handling

Semantic types identified by col.SemanticTypeName take priority over raw PostgreSQL type-based generation, producing values that match the semantic intent of each column rather than just its storage type. For example, email columns generate realistic email addresses, money columns use log-normal distributions matching real financial data patterns, and slug columns produce URL-safe identifiers. When no semantic type is recognized, the generator falls back to the column's raw PostgreSQL type for value generation.

If the semantic type name matches an enum name, a random enum value is selected. The same enum lookup applies to col.PGType as a fallback.

#Type Coverage

The generator handles all 42 built-in PostgreSQL types, including numeric types, string types, binary data, date and time types, UUID, network address types, JSON, XML, full-text search types, OID, range and multirange types, user-defined enums, and array columns. Unknown or extension-provided types that are not in the recognized list produce a NULL cast with a comment indicating the unsupported type, ensuring the generated SQL is always syntactically valid.

• Numeric: boolean, integer, bigint, smallint, serial, bigserial, smallserial, numeric, real, float4, float8
• String: text, citext, varchar, char
• Binary: bytea (random 8-byte hex)
• Date/time: date, time, timetz, timestamp, timestamptz, interval
• UUID: uuid (v4 format with correct version/variant bits)
• Network: inet (random IPv4), cidr (random /24), macaddr (random 6-byte)
• JSON: json ('{}'::json), jsonb ('{}'::jsonb or schema-aware generation)
• XML: xml ('<val>item_{rowIdx}</val>')
• Text search: tsvector, tsquery (random word pairs from a 10-word vocabulary)
• OID: oid (random integer 1-100000)
• Range: int4range, int8range, numrange, daterange, tsrange, tstzrange (half-open [lo, hi) intervals)
• Multirange: int4multirange, int8multirange, nummultirange, datemultirange, tsmultirange, tstzmultirange (single-range multirange literals)
• User-defined enums: random pick from declared values
• Array columns: 1-5 elements of the base type, using ARRAY[...] syntax
• Unknown types: NULL::typename /* unsupported seed type */ -- generates valid SQL while making the gap visible

#Per-Table Row Counts

Configurable via SeedConfig.TableRows (map of table name to row count). Tables not in the map use the rowsPerTable default. In edge-cases mode, the row count is forced to 1 regardless of configuration.

#Clean Flag

When SeedConfig.Clean == true, generates a TRUNCATE ... CASCADE statement before any seed data. Tables are listed in reverse dependency order (reverse of Schema.TableOrder()) so that child tables appear before parent tables in the TRUNCATE list.

#Deterministic Output

The *rand.Rand parameter controls all random decisions. Passing the same seed produces identical output across runs -- useful for reproducible test data in CI. The --seed CLI flag surfaces this capability.

#JSONB Schema-Aware Generation

When a JSONB column has a json_schema attribute referencing an external JSON Schema file, the generator produces a JSON object that conforms to the schema's basic structure rather than generating an empty object or random JSON. The generated object includes a _schema key identifying the schema file and an id field with a random integer, providing a recognizable structure that satisfies the CHECK constraints derived from the JSON Schema's required properties.

• _schema: the schema file reference
• id: a random integer

This ensures the generated JSON has a recognizable structure rather than an empty object.