From: | Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> |
---|---|
To: | David Salisbury <salisbury(at)globe(dot)gov> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: default value returned from sql stmt |
Date: | 2012-03-30 00:57:37 |
Message-ID: | CAD3a31Xjvgc2T3EcoQoT=iPNJtZ0LxpAUdUFV26UHqOK-vp9ZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)globe(dot)gov>wrote:
>
>
> On 3/29/12 4:26 PM, Chris Angelico wrote:
>
>> On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury<salisbury(at)globe(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Bret Stern | 2012-03-30 01:22:38 | Re: Move Tables From One Database to Another |
Previous Message | Chris Angelico | 2012-03-30 00:37:54 | octet_length operator: what encoding? |