Обсуждение: Default value if query returns 0 rows?

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

--
Lars Kellogg-Stedman <lars@oddbit.com>

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

On Fri, 17 Sep 2004, Bruno Wolff III wrote:

> >
> >   if exists (select 1 from map where name = 'foo') then
> >     select id from map where name = 'foo'
> >   else
> >     select -1
> >   end if
> >
>
> If there can be at most one match you can use a subselect and coalesce.
>

Bruno,

Thanks for the suggestion.  I've come up with the following that appears to
work:

  SELECT
    COALESCE((SELECT id FROM map WHERE name = $1), -1)
    FROM map_level
    LIMIT 1

-- Lars

--
Lars Kellogg-Stedman <lars@oddbit.com>

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

[snip]
> Another way is a subselect:
>
>     select coalesce((select id from map where name = 'foo'), -1);
Then why not:

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

This should work even if there are more rows with foo.

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

Hi,

I think

select id from
(select id from map where name like 'foo'
  union
  select -1 as id order by id desc) a LIMIT 1

should do it in the case id >= 0 for existing names.

    -Christian



Lars Kellogg-Stedman schrieb:
> 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
>

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.

> Thanks for the suggestion.  I've come up with the following that appears to
> work:
>
>   SELECT
>     COALESCE((SELECT id FROM map WHERE name = $1), -1)
>     FROM map_level
>     LIMIT 1

And in fact I see that this should simply be:

  SELECT COALESCE((SELECT id FROM map WHERE name = $1), -1)

No need for me to be making things all complicated.

Thanks again!

-- Lars

--
Lars Kellogg-Stedman <lars@deas.harvard.edu>
IT Operations Manager
Division of Engineering and Applied Sciences
Harvard University

Tom Lane wrote:


> 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;
>
>
> 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.

Can't you just:

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

Or am I missing something?