Understanding the NOT NULL Constraint Violation at UPDATE ON CONFLICT Query

When working with databases, it’s essential to understand how constraints work and how they impact queries. In this article, we’ll delve into a specific issue with the NOT NULL constraint and its behavior in the context of an UPDATE ON CONFLICT query.

The Problem

We have a table named player with the following definition:

create table if not exists player (
    id varchar primary key,
    col1 boolean not null default false,
    col2 json not null default '{}',
    col3 varchar not null,
    col4 varchar not null,
    col5 json not null default '{}',
    col6 boolean not null default false
);

We’re trying to insert a new row into the player table using the following query:

insert into player(id, col1, col2)
values (val_id, val1, val2)
on conflict(id)
do update set col1=excluded.col1, col2=excluded.col2;

However, we encounter an error because col3 is defined as NOT NULL without a default value. The query fails with the following message:

ERROR:  ... null value in column "col3" of relation "player" violates not-null constraint

This issue might seem puzzling at first, but understanding why it happens can help you write more robust and efficient queries.

The Role of NOT NULL Constraints

NOT NULL constraints are a crucial part of database design. They ensure that certain columns in a table cannot contain null values.

Here’s an excerpt from the PostgreSQL documentation explaining how NOT NULL constraints work:

“When a table has multiple CHECK constraints, they will be tested for each row in alphabetical order by name, after checking NOT NULL constraints.

“A not-null constraint is always written as a column constraint. A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient.”

As we can see, the documentation explicitly states that NOT NULL constraints are checked immediately when a row is inserted or modified, before other constraints like CHECK constraints.

The Issue with INSERT and UPDATE ON CONFLICT Queries

When using an INSERT ... ON CONFLICT query, PostgreSQL checks for existing rows first. If no matching row exists, the insert operation proceeds as usual. However, if there is a match, the conflict resolution clause is executed, which in our case involves updating certain columns.

The issue arises because PostgreSQL checks NOT NULL constraints before other constraints, including CHECK constraints, when inserting or modifying rows. Since we have defined col3 as NOT NULL without a default value, PostgreSQL immediately enforces this constraint during the insert operation.

Resolving the Issue

To resolve this issue, we can apply one of three solutions:

1. Define a Non-Null Column Default for col3

We can add a default value to col3 using the following syntax:

create table if not exists player (
    id varchar primary key,
    col1 boolean not null default false,
    col2 json not null default '{}',
    col3 varchar not null default 'default_value',  // add a default value for col3
    col4 varchar not null,
    col5 json not null default '{}',
    col6 boolean not null default false
);

By doing so, we ensure that col3 always contains a non-null value during the insert operation.

2. Remove the NOT NULL Constraint

Alternatively, we can remove the NOT NULL constraint from col3 by replacing it with an empty row definition:

create table if not exists player (
    id varchar primary key,
    col1 boolean not null default false,
    col2 json not null default '{}',
    col3 varchar,  // remove NOT NULL constraint
    col4 varchar not null,
    col5 json not null default '{}',
    col6 boolean not null default false
);

However, keep in mind that removing a NOT NULL constraint might compromise the integrity of your data.

3. Provide a Non-Null Value for col3

Finally, we can modify the insert query to provide a non-null value for col3. For example:

insert into player(id, col1, col2)
values (val_id, val1, val2)
on conflict(id)
do update set col1=excluded.col1, col2=excluded.col2,
    col3 = excluded.col3;  // provide a non-null value for col3

By doing so, we ensure that col3 is populated with a valid value during the insert operation.

Additional Considerations

In addition to these solutions, it’s essential to understand how other constraints and triggers work in PostgreSQL. For example:

• CHECK Constraints: These constraints can be used to enforce more complex rules on data. However, they are checked after NOT NULL constraints.
• BEFORE INSERT and BEFORE UPDATE Triggers: These triggers can be used to perform additional operations before or after rows are inserted or updated.

Conclusion

In this article, we explored the issue of a NOT NULL constraint violation during an UPDATE ON CONFLICT query in PostgreSQL. By understanding how NOT NULL constraints work and applying one of three solutions, you can resolve similar issues in your own database queries.

Last modified on 2025-01-26