pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feeddefault value for select?
4+ messages / 4 participants
[nested] [flat]
* default value for select?
@ 2005-05-09 16:57 Mark Fenbers <Mark.Fenbers@noaa.gov>
0 siblings, 3 replies; 4+ messages in thread
From: Mark Fenbers @ 2005-05-09 16:57 UTC (permalink / raw)
To: pgsql-sql
This is a multi-part message in MIME format.
--------------090904020309000109020002
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#333399">
I want to update a column in myTable. The value this column is set to
depends on a nested select statement which sometimes returns 0 rows
instead of 1. This is a problem since the column I'm trying to update
is set to refuse nulls. Here's a sample:<br>
<br>
update myTable set myColumn = (Select altColumn from altTable where
altColumn != 'XXX' limit 1) where myColumn = 'XXX';<br>
<br>
MyColumn cannot accept nulls, but sometimes "Select altColumn ..."
returns 0 rows, and thus, the query fails. <br>
<br>
Is there a way to set a default value to be inserted into myColumn if
and when "select altColumn ..." returns zero rows?<br>
<br>
Mark<br>
</body>
</html>
--------------090904020309000109020002
Content-Type: text/x-vcard; charset=utf-8;
name="Mark.Fenbers.vcf"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Mark.Fenbers.vcf"
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:DoC/NOAA/NWS/OHRFC
adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. HAS Meteorologist
tel;work:937-383-0430 x246
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard
--------------090904020309000109020002--
Attachments:
Mark.Fenbers.vcf (text/x-vcard, 281B) download
begin:vcard
fn:Mark Fenbers
n:Fenbers;Mark
org:DoC/NOAA/NWS/OHRFC
adr:;;1901 South SR 134;Wilmington;OH;45177-9708;USA
email;internet:Mark.Fenbers@noaa.gov
title:Sr. HAS Meteorologist
tel;work:937-383-0430 x246
x-mozilla-html:TRUE
url:http://weather.gov/ohrfc
version:2.1
end:vcard
* Re: default value for select?
@ 2005-05-09 17:18 Philip Hallstrom <postgresql@philip.pjkh.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Philip Hallstrom @ 2005-05-09 17:18 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
> I want to update a column in myTable. The value this column is set to depends on a
> nested select statement which sometimes returns 0 rows instead of 1. This is a
> problem since the column I'm trying to update is set to refuse nulls. Here's a
> sample:
>
> update myTable set myColumn = (Select altColumn from altTable where altColumn !=
> 'XXX' limit 1) where myColumn = 'XXX';
>
> MyColumn cannot accept nulls, but sometimes "Select altColumn ..." returns 0 rows,
> and thus, the query fails.
>
> Is there a way to set a default value to be inserted into myColumn if and when
> "select altColumn ..." returns zero rows?
COALESCE(value [, ...])
The COALESCE function returns the first of its arguments that is not null.
Null is returned only if all arguments are null. This is often useful to
substitute a default value for null values when data is retrieved for
display, for example:
SELECT COALESCE(description, short_description, '(none)') ...
Like a CASE expression, COALESCE will not evaluate arguments that are not
needed to determine the result; that is, arguments to the right of the
first non-null argument are not evaluated.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: default value for select?
@ 2005-05-09 17:26 Keith Worthington <keithw@narrowpathinc.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Keith Worthington @ 2005-05-09 17:26 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
On Mon, 09 May 2005 12:57:41 -0400, Mark Fenbers wrote
> I want to update a column in myTable. The value this column is set
> todepends on a nested select statement which sometimes returns 0
> rowsinstead of 1. This is a problem since the column I'm trying to
> updateis set to refuse nulls. Here's a sample:
>
> update myTable set myColumn = (Select altColumn from altTable
> wherealtColumn != 'XXX' limit 1) where myColumn = 'XXX';
>
> MyColumn cannot accept nulls, but sometimes "Select altColumn
> ..."returns 0 rows, and thus, the query fails.
>
> Is there a way to set a default value to be inserted into myColumn
> ifand when "select altColumn ..." returns zero rows?
>
> Mark
Mark,
I do not know if it will work but I would try the COALESCE function.
http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html
Kind Regards,
Keith
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: default value for select?
@ 2005-05-09 17:30 Tony Wasson <ajwasson@gmail.com>
parent: Mark Fenbers <Mark.Fenbers@noaa.gov>
2 siblings, 0 replies; 4+ messages in thread
From: Tony Wasson @ 2005-05-09 17:30 UTC (permalink / raw)
To: Mark Fenbers <Mark.Fenbers@noaa.gov>; +Cc: pgsql-sql
On 5/9/05, Mark Fenbers <Mark.Fenbers@noaa.gov> wrote:
> I want to update a column in myTable. The value this column is set to
> depends on a nested select statement which sometimes returns 0 rows instead
> of 1. This is a problem since the column I'm trying to update is set to
> refuse nulls. Here's a sample:
>
> update myTable set myColumn = (Select altColumn from altTable where
> altColumn != 'XXX' limit 1) where myColumn = 'XXX';
>
> MyColumn cannot accept nulls, but sometimes "Select altColumn ..." returns
> 0 rows, and thus, the query fails.
>
> Is there a way to set a default value to be inserted into myColumn if and
> when "select altColumn ..." returns zero rows?
>
> Mark
Mark,
You can work around this by using a CASE statement. In this case, test
for a NULL from your subquery. This is not elegant at all, but it
should do what you are wanting.
update myTable set myColumn = (CASE
WHEN (Select altColumn from altTable where altColumn != 'XXX'
limit 1) IS NULL
THEN 'some default value'
ELSE (Select altColumn from altTable where altColumn != 'XXX' limit 1)
END)
where myColumn = 'XXX';
Hope this helps...
Tony
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-05-09 17:30 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-05-09 16:57 default value for select? Mark Fenbers <Mark.Fenbers@noaa.gov>
2005-05-09 17:18 ` Philip Hallstrom <postgresql@philip.pjkh.com>
2005-05-09 17:26 ` Keith Worthington <keithw@narrowpathinc.com>
2005-05-09 17:30 ` Tony Wasson <ajwasson@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox