default value returned from sql stmt

On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury <salisbury@globe.gov> wrote:

development=# select  coalesce(anum,100) from t1 where anum = 4;

What you have there is rather different from COALESCE, as you're
looking for a case where the row completely doesn't exist. But you can
fudge it with an outer join.

Untested code:

WITH rowid AS (select 4 as anum) SELECT coalesce(anum,100) FROM rowid
LEFT JOIN t1 ON rowid.anum=t1.anum

However, you may simply want a WHERE [NOT] EXISTS predicate. There may
be other ways of achieving your goal, too.

ChrisA

Hello

2012/3/30 David Salisbury <salisbury@globe.gov>:

In trying to get an sql stmt to return a default value, I read in the docs..

"The COALESCE function returns the first of its arguments that is not null.
Null is returned only if all arguments are null. It is often used to
substitute a default value for null values when data is retrieved for
display, for example:
SELECT COALESCE(description, short_description, '(none)') ..."

But I seem to be missing something:

development=# create table t1 ( anum integer );
CREATE TABLE

development=# insert into t1 values ( 2 ), (3);
INSERT 0 2

development=# select * from t1;

 anum
------
   2
   3

development=# select  coalesce(anum,100) from t1 where anum = 4;
 coalesce
----------
(0 rows)

select anum from t1 where anum = 4
union all select 100 limit 1;

Regards

Pavel

On 3/29/12 4:26 PM, Chris Angelico wrote:

On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury<salisbury@globe.gov> wrote:

development=# select coalesce(anum,100) from t1 where anum = 4;

What you have there is rather different from COALESCE, as you're
looking for a case where the row completely doesn't exist. But you can
fudge it with an outer join.

Untested code:

WITH rowid AS (select 4 as anum) SELECT coalesce(anum,100) FROM rowid
LEFT JOIN t1 ON rowid.anum=t1.anum

However, you may simply want a WHERE [NOT] EXISTS predicate. There may
be other ways of achieving your goal, too.

Thanks guys! In fact I did see the difference between no row and a null
value within a row. But it seemed there must be a way that I was missing.

It does look though that plpg is the way to go, otherwise it just seems
to obfuscate the code, or have other possible consequences.

-ds

It depends on what exactly it is you're trying to do, and where your
default is supposed to be used. Are you wanting a single number returned?
in that case something like this

SELECT COALESCE((SELECT anum FROM t1 WHERE anum=4 [ LIMIT 1 ]),100)

that would get you back a 4 or 100 in this case. If your anums are not
unique, you'd want the "LIMIT 1" included.

Ken

On Thu, Mar 29, 2012 at 3:56 PM, David Salisbury <salisbury@globe.gov>wrote:

On 29/03/12 23:28, Pavel Stehule wrote:

select anum from t1 where anum = 4
union all select 100 limit 1;

I'm not sure the ordering here is guaranteed by the standard though, is
it? You could end up with the 4 being discarded.

--
Richard Huxton
Archonet Ltd

2012/3/30 Richard Huxton <dev@archonet.com>:

On 29/03/12 23:28, Pavel Stehule wrote:

select anum from t1 where anum = 4
union all select 100 limit 1;

I'm not sure the ordering here is guaranteed by the standard though, is it?
You could end up with the 4 being discarded.

A order is random for only "UNION", "UNION ALL" should to respect
order. But I didn't check it in standard.

Pavel

On 30/03/12 08:46, Pavel Stehule wrote:

2012/3/30 Richard Huxton<dev@archonet.com>:

On 29/03/12 23:28, Pavel Stehule wrote:

select anum from t1 where anum = 4
union all select 100 limit 1;

I'm not sure the ordering here is guaranteed by the standard though, is it?
You could end up with the 4 being discarded.

A order is random for only "UNION", "UNION ALL" should to respect
order. But I didn't check it in standard.

Let's put it this way - a quick bit of googling can't find anything that
says the order *is* guaranteed, and (almost?) no other operations do so
by default.

--
Richard Huxton
Archonet Ltd

2012/3/30 Richard Huxton <dev@archonet.com>:

On 30/03/12 08:46, Pavel Stehule wrote:

2012/3/30 Richard Huxton<dev@archonet.com>:

On 29/03/12 23:28, Pavel Stehule wrote:

select anum from t1 where anum = 4
union all select 100 limit 1;

I'm not sure the ordering here is guaranteed by the standard though, is
it?
You could end up with the 4 being discarded.

A order is random for only "UNION", "UNION ALL" should to respect
order.  But I didn't check it in standard.

Let's put it this way - a quick bit of googling can't find anything that
says the order *is* guaranteed, and (almost?) no other operations do so by
default.

yes, it should to work in pg, but it should not work else where.

secure solution is

SELECT x FROM (SELECT * FROM (SELECT 1, x FROM tab WHERE x = 10 LIMIT
1) s1 UNION ALL SELECT 2, -1000 ORDER BY 1 LIMIT 1) s2;

Regards

Pavel Stehule

On 30 Mar 2012, at 10:22, Richard Huxton wrote:

On 30/03/12 08:46, Pavel Stehule wrote:

2012/3/30 Richard Huxton<dev@archonet.com>:

On 29/03/12 23:28, Pavel Stehule wrote:

select anum from t1 where anum = 4
union all select 100 limit 1;

I'm not sure the ordering here is guaranteed by the standard though, is it?
You could end up with the 4 being discarded.

A order is random for only "UNION", "UNION ALL" should to respect
order. But I didn't check it in standard.

Let's put it this way - a quick bit of googling can't find anything that says the order *is* guaranteed, and (almost?) no other operations do so by default.

Obviously, UNION needs to sort the results to filter out any duplicate rows, so it would change the order of the results of above query and return the 100-valued row for anum values > 100.

UNION ALL will not do so by default, so it would probably behave as Pavel describes. Until you add an ORDER BY to your query.

A more robust implementation would be:

select anum, 0 from t1 where anum = 4
union all
select 100, 1 limit 1
order by 2;

If you don't want the extra column in your query results, you can wrap the query in another select.

Alban Hertroys

--
The scale of a problem often equals the size of an ego.