Horton

Thread

  1. default value returned from sql stmt

    David Salisbury <salisbury@globe.gov> — 2012-03-29T22:16:23Z

    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)

Do I have to resort to PLPGSQL for this?

thanks for any info,

-ds

oh.. running 9.1

  2. Re: default value returned from sql stmt

    Chris Angelico <rosuav@gmail.com> — 2012-03-29T22:26:37Z

    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

  3. Re: default value returned from sql stmt

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-03-29T22:28:09Z

    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

>
> Do I have to resort to PLPGSQL for this?
>
> thanks for any info,
>
> -ds
>
> oh.. running 9.1
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

  4. Re: default value returned from sql stmt

    David Salisbury <salisbury@globe.gov> — 2012-03-29T22:56:36Z

    
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

  5. Re: default value returned from sql stmt

    Ken Tanzer <ken.tanzer@gmail.com> — 2012-03-30T00:57:37Z

    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 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>

  6. Re: default value returned from sql stmt

    Richard Huxton <dev@archonet.com> — 2012-03-30T07:42:45Z

    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

  7. Re: default value returned from sql stmt

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-03-30T07:46:52Z

    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

>
> --
>  Richard Huxton
>  Archonet Ltd

  8. Re: default value returned from sql stmt

    Richard Huxton <dev@archonet.com> — 2012-03-30T08:22:09Z

    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

  9. Re: default value returned from sql stmt

    Pavel Stehule <pavel.stehule@gmail.com> — 2012-03-30T08:34:21Z

    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


>
> --
>  Richard Huxton
>  Archonet Ltd

  10. Re: default value returned from sql stmt

    Alban Hertroys <haramrae@gmail.com> — 2012-03-30T08:45:40Z

    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.