Re: Enforcing uniqueness on [real estate/postal] addresses

On 5/11/20 9:55 AM, Peter Devoy wrote:
> Of course, if any of the fields are NULL (which they often are) I end
> up with duplicates.
> 
> One solution may be to add NOT NULL constraints and use empty strings
> instead of NULL values but, until asking around today, I thought this was
> generally considered bad practice.

If you don't want to store empty strings (which I agree is a little 
yucky), you could replace NULLs with an empty string *only when checking 
for uniqueness*. To do this, first replace your unique constraint with a 
unique index, which gives you some additional features (e.g. indexing 
expressions, indexing only part of the table with a WHERE clause, 
building it concurrently, etc.). In this case we only care about 
indexing expressions.

So you can say:

     CREATE UNIQUE INDEX is_unique_address ON properties (
       COALESCE(description, ''),
       COALESCE(address_identifier_general, ''),
       COALESCE(street, ''),
       COALESCE(postcode, ''));

Another approach, which I don't think is really a serious suggestion but 
is sort of interesting to think about: you could define an operator, say 
===, that does the same thing as `IS NOT DISTINCT FROM`. Then you could 
create an exclusion constraint using that operator on all four columns. 
I've never tried that before but it seems like it would work.

Maybe that's too much effort for something like this. I just think it's 
interesting because it feels like a use case for exclusion constraints 
that goes in the "opposite direction" of how they are usually used: 
instead of being less restrictive than =, it is more restrictive.

Regards,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com