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(dot)klaver(at)aklaver(dot)com