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.
I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to
me.
I thought of using an inner select for the join, and using limit 1 to
get just the one, and forcing the order by to give me the billing
address by preference, but I am then dependant on the sort order
of the particular type values I am selecting from.
Is there a better way? I am sure this kind of problem must have
been solved before.
Thanks in advance for any help you can give
David