Thread: default value returned from sql stmt
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
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 > > 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
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:
Thanks guys! In fact I did see the difference between no row and a null
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.
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
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 > > -- > Richard Huxton > Archonet Ltd
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 > > -- > Richard Huxton > Archonet Ltd
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 resultsof 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.