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
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? |