CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN ClickHouse error¶

The CANNOT_INSERT_NULL_IN_ORDINARY_COLUMN error in ClickHouse (and Tinybird) happens when you attempt to insert NULL values into columns that are defined as non-nullable (ordinary columns). ClickHouse is strict about NULL handling and requires explicit specification of which columns can accept NULL values.

What causes this error¶

You'll typically see it when:

• Inserting data with missing values into non-nullable columns
• Using INSERT statements without specifying all required columns
• Importing data from external sources with NULL values
• Using functions that return NULL for non-nullable columns
• Bulk insert operations with incomplete data

Example errors¶

INSERT INTO users (id, name, email) VALUES
(1, 'John Doe', NULL)

INSERT INTO events (user_id, event_type) VALUES
(123, 'click')
-- timestamp column is missing and non-nullable

INSERT INTO metrics (id, value, calculated_value) VALUES
(1, 100, NULLIF(value, 0))

INSERT INTO users (id, name, email, created_at) VALUES
(1, 'John', 'john@example.com', '2024-01-01'),
(2, 'Jane', NULL, '2024-01-02')  -- email is non-nullable

How to fix it¶

Use Nullable data types¶

Define columns that can accept NULL values:

CREATE TABLE users (
    id UInt64,
    name String,
    email Nullable(String),  -- Allows NULL values
    created_at DateTime
) ENGINE = MergeTree()
ORDER BY id

Provide default values¶

Use default values for non-nullable columns:

CREATE TABLE users (
    id UInt64,
    name String,
    email String DEFAULT '',
    created_at DateTime DEFAULT now()
) ENGINE = MergeTree()
ORDER BY id

Handle NULL values explicitly¶

Use COALESCE or IFNULL to provide fallback values:

INSERT INTO users (id, name, email, created_at) VALUES
(1, 'John Doe', COALESCE(NULL, ''), '2024-01-01')

Specify all required columns¶

Include all non-nullable columns in your INSERT statements:

INSERT INTO events (user_id, event_type, timestamp) VALUES
(123, 'click', '2024-01-01 10:00:00')

Common patterns and solutions¶

Table creation with proper NULL handling¶

CREATE TABLE events (
    id UInt64,
    user_id UInt64,
    event_type String,
    timestamp DateTime,
    metadata Nullable(String),  -- Optional field
    value Float64 DEFAULT 0.0   -- Default value
) ENGINE = MergeTree()
ORDER BY (timestamp, user_id)
PARTITION BY toYYYYMM(timestamp)

Safe INSERT operations¶

INSERT INTO events (user_id, event_type, timestamp, value) VALUES
(123, 'page_view', '2024-01-01 10:00:00', 1.0),
(124, 'click', '2024-01-01 10:01:00', 1.0)

Handling optional data¶

INSERT INTO users (id, name, email, phone) VALUES
(1, 'John Doe', 'john@example.com', NULL),  -- phone is Nullable
(2, 'Jane Smith', 'jane@example.com', '+1234567890')

Bulk insert with validation¶

INSERT INTO events (user_id, event_type, timestamp, value)
SELECT
    user_id,
    event_type,
    timestamp,
    COALESCE(value, 0.0) as safe_value
FROM source_events
WHERE user_id IS NOT NULL
  AND event_type IS NOT NULL
  AND timestamp IS NOT NULL

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Creating Data Sources with non-nullable columns
• Importing data from external sources
• Using Pipes to transform and insert data
• Creating Materialized Views with strict schemas

To debug in Tinybird:

1. Check your Data Source schema for non-nullable columns
2. Use the Data Source preview to identify NULL values
3. Modify your Pipe transformations to handle NULL values
4. Consider using default values or NULL handling functions

Best practices¶

Schema design¶

• Use Nullable() for optional fields
• Provide meaningful default values for required fields
• Consider business logic when deciding NULL policies
• Document which fields can be NULL

Data validation¶

• Validate data before insertion
• Use CHECK constraints where possible
• Implement data quality checks in your pipelines
• Handle NULL values at the application level

INSERT operations¶

• Always specify column names explicitly
• Use parameterized queries to avoid NULL issues
• Implement proper error handling for failed inserts
• Consider using UPSERT operations for data updates

Alternative solutions¶

Using COALESCE¶

INSERT INTO users (id, name, email) VALUES
(1, 'John', COALESCE(NULL, 'no-email@example.com'))

Using IFNULL¶

INSERT INTO users (id, name, email) VALUES
(1, 'John', IFNULL(NULL, 'no-email@example.com'))

Conditional INSERT¶

INSERT INTO users (id, name, email) VALUES
(1, 'John', CASE
    WHEN NULL IS NULL THEN 'no-email@example.com'
    ELSE NULL
END)

See also¶

• Data Types
• Working with Data Sources
• Schema Design Best Practices
• Data Validation