Snowflake SQL error: NULL result in a non-nullable column

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".

Timestamp precision in Snowflake

Timestamps in Snowflake have precisions 0 to 9 with a default of 9, which is a nanosecond, but the Snowflake documentation is not clear on precisions 0 to 8.

Storage difference

I did an empircal test by creating tables. Each table had one million rows and one column with random timestamps. The values have an original precison of one nanosecond, and I used random values because otherwise Snowflake would compress down any number of rows with the same values to a few KB.

create or replace table zzz_timestamp9 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(9) as time1
from TABLE(GENERATOR(ROWCOUNT => 1e6))
;
create or replace table zzz_timestamp0 as
select dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(0) as time1
from TABLE(GENERATOR(ROWCOUNT => 1e6))
;

The storage difference for 1 million rows was 3.5MB vs 7.0MB.

Precision difference

Again, I generated random rows and then copied the value into columns with varied precisions.

select
    dateadd(nanosecond, uniform(1,3e17, random()), current_timestamp())::timestamp(9) as "precision 9",
    "precision 9"::timestamp(3) as "precision 3",
    "precision 9"::timestamp(2) as "precision 2",
    "precision 9"::timestamp(1) as "precision 1",
    "precision 9"::timestamp(0) as "precision 0",
    datediff(ms, "precision 3", "precision 9") as "Precisions 9 vs 3 in milliseconds", /* alwayz zero */
    datediff(second, "precision 0", "precision 9") as "Precisions 9 vs 0 in seconds" /* alwayz zero */
from TABLE(GENERATOR(ROWCOUNT => 10))

Precision 0 is 1 second, precision 1 is 100 ms, precision 2 is 10 ms, precision 3 is 1 ms, etc.

Reusing calculated columns in Netezza and SAS queries

Netezza and SAS allow a query to reference a calculated column by name in the SELECT, WHERE, and ORDER BY clauses. Based on the DRY principle, this reduces code and makes code easier to read and maintain.

Some people call calculated columns derived or computed columns.

In Microsoft SQL Server, SQLite, and other RDBMSs you cannot exactly do this: a workaround is to reference a subquery or view. In Microsoft SQL Server, you can also define a computed column on a table.

Below is an example tested with Netezza 7.2. Notice height_m is used in the SELECT clause, and bmi is used in the WHERE and ORDER BY clauses.