Re: SQL query - Mailing list pgsql-general

From David Goodenough
Subject Re: SQL query
Date
Msg-id 200502111639.06071.david.goodenough@btconnect.com
Whole thread Raw
In response to Re: SQL query  (Matt K <matt@kynx.org>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Yury Don
Date:
Subject: Re: how to capture query?
Next
From: David Goodenough
Date:
Subject: Re: SQL query