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:<\/p>\n\n\n
\npostgres=# select version();\n version \n-----------------------------------------------------------------------------------------------------------------------------\n 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\n(1 row)\npostgres=# create table t ( a int not null, b text );\nCREATE TABLE\npostgres=# \\d t\n Table "public.t"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | not null | \n b | text | | | \n<\/pre><\/div>\n\n\nTrying to insert data into that table which violates the constraint of course will fail:<\/p>\n\n\n
\npostgres=# insert into t select null,1 from generate_series(1,2);\nERROR: null value in column "a" of relation "t" violates not-null constraint\nDETAIL: Failing row contains (null, 1).\n<\/pre><\/div>\n\n\nEven if you can set the column to “NOT NULL” syntax wise, this will not disable the constraint:<\/p>\n\n\n
\npostgres=# alter table t alter column a set not null;\nALTER TABLE\npostgres=# insert into t select null,1 from generate_series(1,2);\nERROR: null value in column "a" of relation "t" violates not-null constraint\nDETAIL: Failing row contains (null, 1).\npostgres=# \\d t\n Table "public.t"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | not null | \n b | text | | | \n<\/pre><\/div>\n\n\nThe only option you have when you want to do this, is to drop the constraint:<\/p>\n\n\n
\npostgres=# alter table t alter column a drop not null;\nALTER TABLE\npostgres=# insert into t select null,1 from generate_series(1,2);\nINSERT 0 2\npostgres=# \\d t\n Table "public.t"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | | \n b | text | | | \n<\/pre><\/div>\n\n\nThe 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:<\/p>\n\n\n
\npostgres=# update t set a = 1;\nUPDATE 2\npostgres=# alter table t alter column a set not null;\nALTER TABLE\npostgres=# \\d t\n Table "public.t"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | not null | \n b | text | | | \n\npostgres=# insert into t select null,1 from generate_series(1,2);\nERROR: null value in column "a" of relation "t" violates not-null constraint\nDETAIL: Failing row contains (null, 1).\n<\/pre><\/div>\n\n\nThis will change with PostgreSQL 18. From now you have more options. The following still behaves as before:<\/p>\n\n\n
\npostgres=# select version();\n version \n--------------------------------------------------------------------\n PostgreSQL 18devel on x86_64-linux, compiled by gcc-14.2.1, 64-bit\n(1 row)\npostgres=# create table t ( a int, b text );\nCREATE TABLE\npostgres=# alter table t add constraint c1 not null a;\nALTER TABLE\npostgres=# \\d t\n Table "public.t"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | not null | \n b | text | | | \n\npostgres=# insert into t select null,1 from generate_series(1,2);\nERROR: null value in column "a" of relation "t" violates not-null constraint\nDETAIL: Failing row contains (null, 1).\n<\/pre><\/div>\n\n\nThis, of course, leads to the same behavior as with PostgreSQL 17 above. But now you can do this:<\/p>\n\n\n
\npostgres=# create table t ( a int, b text );\nCREATE TABLE\npostgres=# insert into t select null,1 from generate_series(1,2);\nINSERT 0 2\npostgres=# alter table t add constraint c1 not null a not valid;\nALTER TABLE\n<\/pre><\/div>\n\n\nThis 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:<\/p>\n\n\n
\npostgres=# select version();\n version \n-----------------------------------------------------------------------------------------------------------------------------\n 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\n(1 row)\n\npostgres=# create table t ( a int, b text );\nCREATE TABLE\npostgres=# insert into t select null,1 from generate_series(1,2);\nINSERT 0 2\npostgres=# alter table t add constraint c1 not null a not valid;\nERROR: syntax error at or near "not"\nLINE 1: alter table t add constraint c1 not null a not valid;\n ^\npostgres=# alter table t alter column a set not null;\nERROR: column "a" of relation "t" contains null values\n<\/pre><\/div>\n\n\nAs you can see the syntax is not supported and adding a “NOT NULL” constraint will scan the table and enforce the constraint.<\/p>\n\n\n\n
Back to the PostgreSQL 18 cluster. As we now have data which would violate the constraint:<\/p>\n\n\n
\npostgres=# select * from t;\n a | b \n---+---\n | 1\n | 1\n(2 rows)\n\npostgres=# \\d t\n Table "public.t"\n Column | Type | Collation | Nullable | Default \n--------+---------+-----------+----------+---------\n a | integer | | not null | \n b | text | | | \n<\/pre><\/div>\n\n\n… we can fix that manually and then validate the constraint afterwards:<\/p>\n\n\n
\npostgres=# update t set a = 1;\nUPDATE 2\npostgres=# alter table t validate constraint c1;\nALTER TABLE\npostgres=# insert into t values (null, 'a');\nERROR: null value in column "a" of relation "t" violates not-null constraint\nDETAIL: Failing row contains (null, a).\n<\/pre><\/div>\n\n\nNice, thanks to all involved, details here<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"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: Trying to insert data into that table which violates the constraint of course will fail: Even if […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,198],"tags":[77],"type_dbi":[],"class_list":["post-37943","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-database-management","tag-postgresql"],"acf":[],"yoast_head":"\n
PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID\" \/>\n<meta property=\"og:description\" content=\"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: Trying to insert data into that table which violates the constraint of course will fail: Even if […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2025-04-08T07:07:29+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-04-08T07:07:32+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID\",\"datePublished\":\"2025-04-08T07:07:29+00:00\",\"dateModified\":\"2025-04-08T07:07:32+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\"},\"wordCount\":259,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\",\"Database management\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\",\"name\":\"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2025-04-08T07:07:29+00:00\",\"dateModified\":\"2025-04-08T07:07:32+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID","og_description":"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: Trying to insert data into that table which violates the constraint of course will fail: Even if […]","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/","og_site_name":"dbi Blog","article_published_time":"2025-04-08T07:07:29+00:00","article_modified_time":"2025-04-08T07:07:32+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID","datePublished":"2025-04-08T07:07:29+00:00","dateModified":"2025-04-08T07:07:32+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/"},"wordCount":259,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring","Database management"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/","name":"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2025-04-08T07:07:29+00:00","dateModified":"2025-04-08T07:07:32+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-18-allow-not-null-constraints-to-be-added-as-not-valid\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37943","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=37943"}],"version-history":[{"count":6,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37943\/revisions"}],"predecessor-version":[{"id":37949,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/37943\/revisions\/37949"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=37943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=37943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=37943"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=37943"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}