Re: Default value if query returns 0 rows?

Lars Kellogg-Stedman <lars(at)oddbit(dot)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