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

On 5/11/20 9:55 AM, Peter Devoy wrote:
> Hi list
> 
> I need to store addresses for properties (as in real estate) so in my
> naivety I created a unique constraint like this:
> 
> ALTER TABLE properties
>      ADD CONSTRAINT is_unique_address
>      UNIQUE (
>          description, --e.g. Land north of Foo Cottage
>          address_identifier_general,
>          street,
>          postcode
>      );
> 
> 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.
> 
> Please can anyone recommend a way of approaching this? Perhaps empty strings
> are pragmatic in this situation?

How about?:

create table properties (description varchar, address_identifier_general 
varchar, street varchar, postcode varchar);

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


insert into properties (description, street, postcode) values ('test', 
'anywhere', '1234');
INSERT 0 1

insert into properties (description, street, postcode) values ('test', 
'anywhere', '1234');
ERROR:  duplicate key value violates unique constraint "is_unique_address"
DETAIL:  Key (COALESCE(description, ''::character varying), 
COALESCE(address_identifier_general, ''::character varying), 
COALESCE(street, ''::character varying), COALESCE(postcode, 
''::character varying))=(test, , anywhere, 1234) already exists.

> 
> Kind regards
> 
> 
> Peter
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com