Default value if query returns 0 rows?

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

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:

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

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

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

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?