Enforcing uniqueness on [real estate/postal] addresses

From: Peter Devoy <peter(at)3xe(dot)co(dot)uk>
To: "Psql_General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: Enforcing uniqueness on [real estate/postal] addresses
Date: 2020-05-11 16:55:49
Message-ID: CABoFc_im50V4DgcOddDiPyDWEFkVR==P26q8fBLNdgcpx--rSw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Kind regards

Peter

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Philip Semanchuk 2020-05-11 18:14:29 Re: Enforcing uniqueness on [real estate/postal] addresses
Previous Message Laurenz Albe 2020-05-11 16:28:16 Re: Hash partitioning, what function is used to compute the hash?