{"id":17102,"date":"2022-02-03T12:34:36","date_gmt":"2022-02-03T11:34:36","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/"},"modified":"2022-02-03T12:34:36","modified_gmt":"2022-02-03T11:34:36","slug":"postgresql-15-new-option-for-null-handling-in-unique-constraints","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/","title":{"rendered":"PostgreSQL 15: New option for NULL handling in unique constraints"},"content":{"rendered":"<p>Another nice feature was just committed for PostgreSQL 15: Add UNIQUE null treatment option (discussion <a href=\"https:\/\/www.postgresql.org\/message-id\/flat\/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com\" target=\"_blank\" rel=\"noopener\">here<\/a>, commit details <a href=\"https:\/\/git.postgresql.org\/pg\/commitdiff\/94aa7cc5f707712f592885995a28e018c7c80488\" target=\"_blank\" rel=\"noopener\">here<\/a>). What is it about? The answer is quite easy if we do a little example which shows the effect of this new feature.<\/p>\n<p>Consider the following table and unique index:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create table t ( a int );\nCREATE TABLE\npostgres=# create unique index i on t ( a );\nCREATE INDEX\n<\/pre>\n<p>This obviously prevents us from adding the same value more than once:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into t values (1);\nINSERT 0 1\npostgres=# insert into t values (1);\nERROR:  duplicate key value violates unique constraint \"i\"\nDETAIL:  Key (a)=(1) already exists.\n<\/pre>\n<p>But what about NULLs? NULL means undefined, so what should the unique index do when you try to insert more than one row with the value of NULL? Are two NULL values the same or not? If you ask PostgreSQL if NULL equals NULL, you get undefined (or NULL):<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# select null = null;\n ?column? \n----------\n \n(1 row)\n<\/pre>\n<p>Comparing something which is not defined against something which is not defined does not make much sense. The unique index we&#8217;ve created above behaves exactly like this:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# insert into t values(null);\nINSERT 0 1\npostgres=# insert into t values(null);\nINSERT 0 1\npostgres=# insert into t values(null);\nINSERT 0 1\npostgres=# insert into t values(null);\nINSERT 0 1\npostgres=# insert into t values(null);\nINSERT 0 1\n<\/pre>\n<p>PostgreSQL 15 will give you choice here:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1; highlight: [3]\">\npostgres=# create table tt ( a int );\nCREATE TABLE\npostgres=# create unique index ii on tt (a) nulls not distinct;\nCREATE INDEX\npostgres=# insert into tt values(null);\nINSERT 0 1\npostgres=# insert into tt values(null);\nERROR:  duplicate key value violates unique constraint \"ii\"\nDETAIL:  Key (a)=(null) already exists.\n<\/pre>\n<p>The default, of course, is the behavior as we have it now, but this can also be requested explicitly:<\/p>\n<pre class=\"brush: sql; gutter: true; first-line: 1\">\npostgres=# create unique index iii on tt (a) nulls distinct;\nCREATE INDEX\n<\/pre>\n<p>Nice.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Another nice feature was just committed for PostgreSQL 15: Add UNIQUE null treatment option (discussion here, commit details here). What is it about? The answer is quite easy if we do a little example which shows the effect of this new feature. Consider the following table and unique index: postgres=# create table t ( a&hellip;&nbsp;<\/p>\n","protected":false},"author":28,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133],"tags":[],"class_list":["post-17102","post","type-post","status-publish","format-standard","hentry","category-database"],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v28.8 (Yoast SEO v28.8) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>PostgreSQL 15: New option for NULL handling in unique constraints - dbi services | 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-15-new-option-for-null-handling-in-unique-constraints\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 15: New option for NULL handling in unique constraints\" \/>\n<meta property=\"og:description\" content=\"Another nice feature was just committed for PostgreSQL 15: Add UNIQUE null treatment option (discussion here, commit details here). What is it about? The answer is quite easy if we do a little example which shows the effect of this new feature. Consider the following table and unique index: postgres=# create table t ( a&hellip;&nbsp;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-02-03T11:34:36+00:00\" \/>\n<meta name=\"author\" content=\"Open source Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Open source Team\" \/>\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-15-new-option-for-null-handling-in-unique-constraints\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/\"},\"author\":{\"name\":\"Open source Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\"},\"headline\":\"PostgreSQL 15: New option for NULL handling in unique constraints\",\"datePublished\":\"2022-02-03T11:34:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/\"},\"wordCount\":172,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/\",\"name\":\"PostgreSQL 15: New option for NULL handling in unique constraints - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2022-02-03T11:34:36+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/postgresql-15-new-option-for-null-handling-in-unique-constraints\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 15: New option for NULL handling in unique constraints\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"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\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/0b4548362cac3eeff8b3eaf329e3530a\",\"name\":\"Open source Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/05\\\/open-source-author.png\",\"caption\":\"Open source Team\"},\"description\":\"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/open-source-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL 15: New option for NULL handling in unique constraints - dbi services | 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-15-new-option-for-null-handling-in-unique-constraints\/","og_locale":"en_US","og_type":"article","og_title":"PostgreSQL 15: New option for NULL handling in unique constraints","og_description":"Another nice feature was just committed for PostgreSQL 15: Add UNIQUE null treatment option (discussion here, commit details here). What is it about? The answer is quite easy if we do a little example which shows the effect of this new feature. Consider the following table and unique index: postgres=# create table t ( a&hellip;&nbsp;","og_url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2022-02-03T11:34:36+00:00","author":"Open source Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Open source Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/"},"author":{"name":"Open source Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a"},"headline":"PostgreSQL 15: New option for NULL handling in unique constraints","datePublished":"2022-02-03T11:34:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/"},"wordCount":172,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/","url":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/","name":"PostgreSQL 15: New option for NULL handling in unique constraints - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2022-02-03T11:34:36+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/postgresql-15-new-option-for-null-handling-in-unique-constraints\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 15: New option for NULL handling in unique constraints"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","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":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/0b4548362cac3eeff8b3eaf329e3530a","name":"Open source Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/05\/open-source-author.png","caption":"Open source Team"},"description":"Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.","url":"https:\/\/www.dbi-services.com\/blog\/author\/open-source-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17102","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\/28"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=17102"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/17102\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=17102"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=17102"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=17102"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}