Troubleshooting Snowflake SQL Error : NULL result in a non-nullable column
When working with Snowflake, you might encounter the error message:
SQL Error [100072] [22000]: NULL result in a non-nullable column
This error can be misleading at first glance. Let's walk through a common scenario that triggers this error and how to resolve it.
Scenario
Consider the following SQL script:
-- Create the test table
CREATE TABLE set_test (
id INT NOT NULL,
is_deleted CHAR(1) NOT NULL,
foo FLOAT NULL
);
-- Insert a few records
INSERT INTO set_test (id, is_deleted, foo) VALUES
(1, 'F', 10.5),
(2, 'F', 20.0),
(3, 'F', 30.75);
-- Update one record to set is_deleted to 'T' and foo to NULL
UPDATE set_test
SET is_deleted = 'T' and foo = NULL
WHERE id = 2;
When you run the UPDATE statement, Snowflake returns the error message:
SQL Error [100072] [22000]: NULL result in a non-nullable column
Understanding the Error
The error message indicates that there is an attempt to insert a NULL value into a column that does not allow NULL values. However, in this case, the error is not due to the NULL value itself but rather a syntax issue in the UPDATE statement.
Resolving the Error
The issue lies in the UPDATE statement:
UPDATE set_test
SET is_deleted = 'T' and foo = NULL
WHERE id = 2;
In SQL, the SET clause should use commas to separate multiple column assignments, not the AND keyword. The correct UPDATE statement should be:
UPDATE set_test
SET is_deleted = 'T', foo = NULL
WHERE id = 2;
By replacing and with a comma, the UPDATE statement will execute without errors:
UPDATE set_test
SET is_deleted = 'T', foo = NULL
WHERE id = 2;
Conclusion
When you encounter the "NULL result in a non-nullable column" error in Snowflake, double-check the syntax of your UPDATE statement. Ensure that you are using commas to separate column assignments in the SET clause. This simple fix can save you time and prevent confusion when troubleshooting SQL errors.
If this does not help, see also Snowflake KB Inserting or loading data in table fails with error "NULL result in a non-nullable column".
