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 <salisb...@globe.gov>wrote: > > > On 3/29/12 4:26 PM, Chris Angelico wrote: > >> On Fri, Mar 30, 2012 at 9:16 AM, David Salisbury<salisb...@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> >
