Skip site navigation (1) Skip section navigation (2)

Re: Getting null values in an update statement

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: JORGE MALDONADO <jorgemal1960(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Getting null values in an update statement
Date: 2011-04-26 18:05:14
Message-ID: 10279.1303841114@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> writes:
>  I want to perform an UPDATE to a field in a table. Such a field is of type
> numeric, does not allow null values and has a default value of 0 (zero). The
> situation I am facing is that the records that do not meet the update
> criteria are set to null and I do not know why. I get an error message
> saying that a violation has been made because the field cannot be set to
> null. Then, I change the field definition so it accepts null values, run the
> update statement and I confirm that such a field is set to null for the
> records that do not meed the criteria. How can I avoid this sitution?
> Does this behavior has to do with the way I am using the update statement?
> Below is the UPDATE statement for your reference.

> UPDATE temp_lista_titulos SET tmt_porc =
>   (SELECT SUM(tmt_clave) AS suma FROM
>     (SELECT t1.tmt_clave, t1.tmt_album AS album
>     FROM temp_lista_titulos as t1, temp_lista_titulos as t2
>     WHERE t1.tmt_album = t2.tmt_album AND t1.tmt_clave != t2.tmt_clave) temp
> WHERE tmt_album = album
> GROUP BY tmt_album)

Right offhand I'd guess that the sub-select is finding no rows that
match its WHERE clause.  SUM() over no rows yields NULL, which is what
the SQL spec demands, although IMO anybody with even a nodding
acquaintance with math would expect zero.

If that's what your problem is, try COALESCE(SUM(...), 0)

			regards, tom lane

In response to

pgsql-novice by date

Next:From: e-letterDate: 2011-04-26 21:37:56
Subject: how to remove empty rows
Previous:From: JORGE MALDONADODate: 2011-04-26 17:40:36
Subject: Getting null values in an update statement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group