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: https://github.com/tripal/tripal/issues/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...