How to Implement a unique constraint in PostgreSQL when NULL conflicts with everything

To implement a unique constraint in PostgreSQL when NULL conflicts with everything, you can use the following method:

  1. Create a new table or modify an existing table to add a new column that will be used for the unique constraint.
  2. Create a unique constraint on the new column using the CREATE UNIQUE CONSTRAINT statement. For example:

Use the COALESCE function to replace any NULL values in the new column with a unique value, such as -1. For example:

  1. Test the constraint by attempting to insert or update a row with a duplicate value for the new column. The constraint should prevent the duplicate value from being inserted or updated.
  2. If you want to make sure that the constraint is enforced for all NULLs, you can use the EXCLUDE constraint.

This will ensure that the constraint is enforced even when the new column has NULL values.

ALTER TABLE mytable ADD CONSTRAINT mytable_new_column_exclude
EXCLUDE USING GIST (new_column WITH =, new_column IS NULL WITH <>);

About MinervaDB Corporation 36 Articles
A boutique private-label enterprise-class MySQL, MariaDB, MyRocks, PostgreSQL and ClickHouse consulting, 24*7 consultative support and remote DBA services company with core expertise in performance, scalability and high availability. Our consultants have several years of experience in architecting and building web-scale database infrastructure operations for internet properties from diversified verticals like CDN, Mobile Advertising Networks, E-Commerce, Social Media Applications, SaaS, Gaming and Digital Payment Solutions. Our globally distributed team working on multiple timezones guarantee 24*7 Consulting, Support and Remote DBA Services delivery for MySQL, MariaDB, MyRocks, PostgreSQL and ClickHouse.