Enforcing uniqueness on [real estate/postal] addresses - Mailing list 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