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@xxxxxxxxxxx
