| 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: | Whole Thread | Raw Message | 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? |