Handling NULL Values in Bulk INSERT

NULL Handling Strategies

JSON null values need special treatment when generating SQL. The bulk INSERT tool offers three strategies, each suited to different scenarios.

Strategy 1: NULL Keyword (Default)

Inserts the SQL NULL literal for null JSON values:

INSERT INTO "users" ("id", "name", "department")
VALUES
  (1, 'Alice', 'Engineering'),
  (2, 'Bob', NULL),
  (3, 'Charlie', NULL);

Use when: The column allows NULLs and you want to explicitly store NULL.

Strategy 2: DEFAULT Keyword

Uses the SQL DEFAULT keyword, which applies the column's default value:

INSERT INTO "users" ("id", "name", "department")
VALUES
  (1, 'Alice', 'Engineering'),
  (2, 'Bob', DEFAULT),
  (3, 'Charlie', DEFAULT);

Use when: Your table has column defaults (e.g., department VARCHAR(100) DEFAULT 'Unassigned') and you want null JSON values to trigger the database default.

Strategy 3: Skip Column

Omits the column entirely for rows with null values. Note that this only works with individual INSERT mode since bulk INSERT requires consistent column lists across all rows.

Use when: You need per-row column lists and cannot use bulk mode.

Comparison Table

Strategy	NULL JSON value becomes	Bulk compatible	Uses DB default
NULL keyword	NULL	Yes	No
DEFAULT keyword	DEFAULT	Yes	Yes
Skip column	Column omitted	No (individual only)	Yes

Mixed Nullability

When your JSON data has inconsistent keys (some objects lack certain properties), missing keys are treated the same as explicit nulls. The tool detects nullability from the data and marks columns as nullable in the inferred type information.