Re: [Gnumed-devel] Backend dem.urb needs cleaner constraint

[Jim Busser]

On 2011-10-07, at 10:59 PM, Jim Busser wrote:

> the constraint where uniqueness allows variations on any of the following:
> 
>        id_street, aux_street, number, subunit, addendum

except I just realized (after importing thousands of records redundantly, 
fortunately into v16 'test') that provided that if any value among

        aux_street, subunit, addendum

in an insertion is null, you can keep importing duplicate after duplicate until 
after the cows have come home.

Possibly we should not (in postgres) be including nullable columns in a UNIQUE 
key except if accept limitless numbers of them?

In order for the potential workaround

        CASE foo IS NULL then <empty string ''>  else foo

to be reliable, is would have to be implemented in the client and anyone who is 
doing batch inserts would likewise need to be inserting null strings. It feels 
to me like a bad practice, but I am no expert. Is there a setting that would 
process (and require unique on) the nulls?

-- Jim