Re: Default value if query returns 0 rows?

On Fri, Sep 17, 2004 at 11:03:48 -0400,
Lars Kellogg-Stedman <lars(at)oddbit(dot)com> 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.

If there can be at most one match you can use a subselect and coalesce.