What causes "NULL value not allowed for NOT NULL column: " in prestodb/presto?

Clean the data before writing: wrap the offending expression with COALESCE(col, default) or filter NULL rows (WHERE col IS NOT NULL).

prestodb/presto · error · PrestoException

CONSTRAINT_VIOLATION

CONSTRAINT_VIOLATION

Error message

NULL value not allowed for NOT NULL column:

What it means

CONSTRAINT_VIOLATION thrown by TableWriterOperator.verifyBlockHasNoNulls when a Block destined for a NOT NULL output column actually contains a NULL value. Presto's table writers enforce NOT NULL constraints declared on target tables (e.g. in a connector such as Hive/Iceberg) before writing. This surfaces at execution time when upstream operators produce NULLs the schema forbids.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/TableWriterOperator.java:353

        timer.end(statisticsTiming);

        ListenableFuture<?> blockedOnAggregation = statisticAggregationOperator.isBlocked();
        CompletableFuture<?> future = pageSink.appendPage(new Page(blocks));
        updateMemoryUsage();
        ListenableFuture<?> blockedOnWrite = toListenableFuture(future);
        blocked = allAsList(blockedOnAggregation, blockedOnWrite);
        rowCount += page.getPositionCount();
        updateWrittenBytes();
    }

    private void verifyBlockHasNoNulls(Block block, String columnName)
    {
        if (!block.mayHaveNull()) {
            return;
        }
        for (int position = 0; position < block.getPositionCount(); position++) {
            if (block.isNull(position)) {
                throw new PrestoException(CONSTRAINT_VIOLATION, "NULL value not allowed for NOT NULL column: " + columnName);
            }
        }
    }

    @Override
    public Page getOutput()
    {
        if (!blocked.isDone()) {
            return null;
        }

        if (!statisticAggregationOperator.isFinished()) {
            OperationTimer timer = new OperationTimer(statisticsCpuTimerEnabled);
            Page aggregationOutput = statisticAggregationOperator.getOutput();
            timer.end(statisticsTiming);

            if (aggregationOutput == null) {
                return null;

View on GitHub (pinned to 55bb57d202)

Solutions

Clean the data before writing: wrap the offending expression with COALESCE(col, default) or filter NULL rows (WHERE col IS NOT NULL).
Check which column fails — the message includes columnName — and relax the target table's NOT NULL constraint if NULLs are legitimate.
Fix upstream joins/aggregations that introduce unintended NULLs.
Use TRY() on expressions that can fail to NULL unexpectedly, or add explicit validation in the query.

Example fix

// before
INSERT INTO tgt (id, name) SELECT id, name FROM src; -- name is NOT NULL but has NULLs
// after
INSERT INTO tgt (id, name) SELECT id, COALESCE(name, 'unknown') FROM src WHERE id IS NOT NULL;

Defensive patterns

Strategy: validation

Validate before calling

// SQL pre-check before inserting into NOT NULL columns:
SELECT COUNT(*) FROM src
WHERE id IS NULL OR name IS NULL; -- must be 0 for NOT NULL targets
// or coerce at write time:
-- INSERT INTO tgt SELECT id, COALESCE(name, 'unknown') FROM src

Try / catch

try {
    statement.execute(insertSql);
} catch (SQLException e) {
    if (e.getMessage() != null && e.getMessage().contains("NULL value not allowed for NOT NULL column")) {
        String col = e.getMessage().substring(e.getMessage().lastIndexOf(':') + 1).trim();
        throw new DataIntegrityViolationException("NULLs present for NOT NULL column: " + col, e);
    }
    throw e;
}

Prevention

Check target table DDL for NOT NULL constraints before writing
Wrap nullable expressions in COALESCE with sensible defaults
Filter NULL rows with WHERE col IS NOT NULL when dropping them is acceptable
Re-check constraints after schema changes (column made NOT NULL)

When it happens

Trigger: During addInput on the writer's input channel for a NOT NULL column, block.mayHaveNull() is true and scanning positions finds block.isNull(position) true for any position.

Common situations: Inserting or CTAS-ing data with NULLs into a table whose column is declared NOT NULL; LEFT/OUTER JOIN producing NULLs feeding the writer; COALESCE/CAST logic missing on optional columns; schema evolution making a previously nullable column NOT NULL.

Related errors

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/b7bb160f90a4c1c0. Report an issue: GitHub.