Re: Default value if query returns 0 rows? - Mailing list pgsql-general

On Fri, 17 Sep 2004 11:03:48 -0400 (EDT), lars@oddbit.com (Lars
Kellogg-Stedman) wrote:

>Hello,
>
>I have a simple two-column table mapping names to ids.  I'd like to write a
>select statement that will return a default value if a given name isn't
>found in the table.  That is, I want something equivalent to the following
>pseudocode:
>
>  if exists (select 1 from map where name = 'foo') then
>    select id from map where name = 'foo'
>  else
>    select -1
>  end if
>
>I think I can see how to do this by writing a pl/pgsql function, but I'm
>curious if it's possible to do this completely as part of a select
>statement.  I've toyed with CASE expressions, but the fact that a missing
>value returns 0 rows continues to foil me.
>
>Thanks,
>
>-- Lars


try something like:

select case
    when count(*) > 0 then (select id from map where name = 'foo')
    when count(*) = 0 then -1
      end as id
 from map where name = 'foo'


cheers,
Gary.