Organism - unique constraint does not work for nullable columns

[dsenalik]

In Postgresql 13 (though with any version probably) you can add more than one duplicate organism to the organism table
The Tripal issue about this: tripal/tripal#1592
Briefly, you can do this

sitedb=> INSERT INTO chado.organism (genus, species) values ('Tripalus', 'databasicus');
INSERT 0 1
sitedb=> INSERT INTO chado.organism (genus, species) values ('Tripalus', 'databasicus');
INSERT 0 1
sitedb=> select * from chado.organism;
 organism_id |      abbreviation       |  genus   |   species   |     common_name     | infraspecific_name | type_id | comment 
-------------+-------------------------+----------+-------------+---------------------+--------------------+---------+---------
           1 |                         | Tripalus | databasicus |                     |                    |         | 
           2 |                         | Tripalus | databasicus |                     |                    |         | 

The constraint that Chado 1.31 currently defines is
constraint organism_c1 unique (genus,species,type_id,infraspecific_name)

From the Postgresql manual
In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns.

Both type_id and infraspecific_name can be null.

MAYBE this could be fixed by changing to
constraint organism_c1 unique (genus,species,COALESCE(type_id,0),COALESCE(infraspecific_name,''))

This change does impose the desired constraint with my testing, but I am stumbling around in the dark here...

[laceysanderson]

In PostgreSQL 15 there is now a built-in way to handle nulls in the constraint:

"Allow unique constraints and indexes to treat NULL values as not distinct (Peter Eisentraut)

Previously NULL values were always indexed as distinct values, but this can now be changed by creating constraints and indexes using UNIQUE NULLS NOT DISTINCT."
-- Ryan Lambert