Re: Default value if query returns 0 rows?

Lars Kellogg-Stedman <lars@oddbit.com> writes:
> 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

Is the name unique?  If so you could do

    select * from
      (select id from map where name = 'foo'
       union all
       select -1) ss
    limit 1;

This is an abuse of SQL of course --- mainly, it relies on the
assumption that UNION ALL is implemented in the "obvious" way.
But it certainly will work in current and foreseeable versions
of Postgres.  A bigger problem is that I don't see how to extend
the approach if there might be more than one 'foo' row, and you
want them all and only want the -1 when there are none.

Another way is a subselect:

    select coalesce((select id from map where name = 'foo'), -1);

but this one will actively blow up if there are multiple 'foo' rows,
so it doesn't solve that problem either.

            regards, tom lane