Re: SQL query

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