Default value if query returns 0 rows?

[Lars Kellogg-Stedman]

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 <la**@oddbit.com>

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Bruno Wolff III]

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Gary Doades]

On Fri, 17 Sep 2004 11:03:48 -0400 (EDT), la**@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.

[Lars Kellogg-Stedman]

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 <la**@oddbit.com>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Lars Kellogg-Stedman <la**@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Csaba Nagy]

[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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Edmund Bacon]

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?

[Christian Mangold]

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Lars Kellogg-Stedman]

> 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 <la**@deas.harvard.edu>
IT Operations Manager
Division of Engineering and Applied Sciences
Harvard University


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match