On Friday 11 February 2005 11:31, Matt K wrote: > David Goodenough wrote: > >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. > > Use NULL to indicate that the customer type is default. Then you can > query with: > > select * from addresses where customer = ? > and coalesce(type, 'billing') = 'billing' > > If type is NULL, the comparison will be 'billing' = 'billing' - always > true. If there's a bunch of non-null type addresses, you'll get the > 'billing' one. > > http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html#A >EN12003 > > Matt > Well coalesce is not something I had come across, learn something every day. But I can not use this as the type (with the customer) are the primary key and therefore not null. I could do something like:- coalesce( nullif( 'default', type), 'billing') but I think that might be over egging it a bit. I will hope this one reserve and remember coalesce for the future. Thanks, David ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
