Before we take a look at what this new feature is about, let’s have a look at how PostgreSQL 17 (and before) handles “NOT NULL” constraints when they get created. As usual we start with a simple table:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1 20250110 (Red Hat 14.2.1-7), 64-bit
(1 row)
postgres=# create table t ( a int not null, b text );
CREATE TABLE
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | text | | |
Trying to insert data into that table which violates the constraint of course will fail:
postgres=# insert into t select null,1 from generate_series(1,2);
ERROR: null value in column "a" of relation "t" violates not-null constraint
DETAIL: Failing row contains (null, 1).
Even if you can set the column to “NOT NULL” syntax wise, this will not disable the constraint:
postgres=# alter table t alter column a set not null;
ALTER TABLE
postgres=# insert into t select null,1 from generate_series(1,2);
ERROR: null value in column "a" of relation "t" violates not-null constraint
DETAIL: Failing row contains (null, 1).
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | text | | |
The only option you have when you want to do this, is to drop the constraint:
postgres=# alter table t alter column a drop not null;
ALTER TABLE
postgres=# insert into t select null,1 from generate_series(1,2);
INSERT 0 2
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | text | | |
The use case for this is data loading. Maybe you want to load data where you know that the constraint would be violated but you’re ok with fixing that manually afterwards and then re-enable the constraint like this:
postgres=# update t set a = 1;
UPDATE 2
postgres=# alter table t alter column a set not null;
ALTER TABLE
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | text | | |
postgres=# insert into t select null,1 from generate_series(1,2);
ERROR: null value in column "a" of relation "t" violates not-null constraint
DETAIL: Failing row contains (null, 1).
This will change with PostgreSQL 18. From now you have more options. The following still behaves as before:
postgres=# select version();
version
--------------------------------------------------------------------
PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit
(1 row)
postgres=# create table t ( a int, b text );
CREATE TABLE
postgres=# alter table t add constraint c1 not null a;
ALTER TABLE
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | text | | |
postgres=# insert into t select null,1 from generate_series(1,2);
ERROR: null value in column "a" of relation "t" violates not-null constraint
DETAIL: Failing row contains (null, 1).
This, of course, leads to the same behavior as with PostgreSQL 17 above. But now you can do this:
postgres=# create table t ( a int, b text );
CREATE TABLE
postgres=# insert into t select null,1 from generate_series(1,2);
INSERT 0 2
postgres=# alter table t add constraint c1 not null a not valid;
ALTER TABLE
This gives us a “NOT NULL” constraint which will not be enforced when it is created. Doing the same in PostgreSQL 17 (and before) will scan the table and enforce the constraint:
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.2 dbi services build on x86_64-pc-linux-gnu, compiled by gcc (GCC) 14.2.1 20250110 (Red Hat 14.2.1-7), 64-bit
(1 row)
postgres=# create table t ( a int, b text );
CREATE TABLE
postgres=# insert into t select null,1 from generate_series(1,2);
INSERT 0 2
postgres=# alter table t add constraint c1 not null a not valid;
ERROR: syntax error at or near "not"
LINE 1: alter table t add constraint c1 not null a not valid;
^
postgres=# alter table t alter column a set not null;
ERROR: column "a" of relation "t" contains null values
As you can see the syntax is not supported and adding a “NOT NULL” constraint will scan the table and enforce the constraint.
Back to the PostgreSQL 18 cluster. As we now have data which would violate the constraint:
postgres=# select * from t;
a | b
---+---
| 1
| 1
(2 rows)
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | not null |
b | text | | |
… we can fix that manually and then validate the constraint afterwards:
postgres=# update t set a = 1;
UPDATE 2
postgres=# alter table t validate constraint c1;
ALTER TABLE
postgres=# insert into t values (null, 'a');
ERROR: null value in column "a" of relation "t" violates not-null constraint
DETAIL: Failing row contains (null, a).
Nice, thanks to all involved, details here.
![Thumbnail [60x60]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/DWE_web-min-scaled.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2024/01/HME_web.jpg)
![Thumbnail [90x90]](https://www.dbi-services.com/blog/wp-content/uploads/2022/08/HER_web-min-scaled.jpg)