David Goodenough wrote:
> I realise this is not strictly a Postgreslql question, but if the best way to
> solve it involves using PG extensions, such as the PG procedural languages
> I am only going to do this on PG and so I am happy to use them.
>
> I have an address table, with all the normal fields and a customer name
> field and an address type. There is a constraint that means that the
> combination of customer and type have to be unique. Normally the
> only record per customer will be of type 'default', but if for instance
> the customer wants a different billing address I would add in a second
> type='billing' address record.
>
> I then want to join this table to another table, say an invoice table,
> and I want to use the billing address if present, otherwise the default
> address. I do not want to create either two addresses or to put both
> addresses on the invoice.
Not sure whether a schema change is possible for you, but you might want
to have two tables -
addresses (customer_id*, addr_id*, ...)
addr_usage (customer_id*, addr_type*, addr_id)
Add a custom trigger that ensures for every customer_id there is a valid
row in addr_usage for each addr_type (sales, billing, shipping etc).
That way you can have any mix of addresses you like, and it's explicit
which address is for which purpose.
--
Richard Huxton
Archonet Ltd