Thread: Trigger function failure
I have a trigger function problem that threatens to cause my early demise. Please someone help an absolute novice.
I have two tables, invoice and invline, with the usual functions.
Table invoice has a column for the total of debit lines, and a column for the total of credit (payment) lines. The function is triggered by any change in inv.gross
The ERROR occurs if there are no invline found by either summing part, where we set dr_total or cr_total.
If I change "sum" to "count" it works as expected, returning 0.00
--THE FUNCTION
UPDATE invoice SET
invoice.cr_total = (
SELECT
sum(invline.gross)
FROM invline
WHERE invline.type > 4 AND invline.invoice_id = 200003
)
,
invoice.dr_total = (
SELECT
sum(invline.gross)
FROM invline
WHERE invline.type <5 AND invline.invoice_id = 200003
)
WHERE invoice.id = 200003;
--TEST
select invoice.dr_total, invoice.cr_total from invoice where invoice.id=200003
---------------------------------------------------------------------------------------------
ERROR: null value in column "po_cr_total" violates not-null constraint
Anyone?
Mike
Hi Michael,
From http://www.postgresql.org/docs/current/static/functions-aggregate.html:It should be noted that except for
count
, these functions return a null value when no rows are selected. In particular, sum
of no rows returns null, not zero as one might expect, and array_agg
returns null rather than an empty array when there are no input rows.This explains why your query works with COUNT(): that function *is* guaranteed to return 0 when run over an empty set.
COALESCE((SELECT …), 0)
(SELECT …)
[]s, Fábio.
On Mon, Mar 7, 2016 at 3:43 PM, Michael Rowan <michael.rowan3@gmail.com> wrote:
I have a trigger function problem that threatens to cause my early demise. Please someone help an absolute novice.I have two tables, invoice and invline, with the usual functions.Table invoice has a column for the total of debit lines, and a column for the total of credit (payment) lines. The function is triggered by any change in inv.grossThe ERROR occurs if there are no invline found by either summing part, where we set dr_total or cr_total.If I change "sum" to "count" it works as expected, returning 0.00--THE FUNCTIONUPDATE invoice SETinvoice.cr_total = (SELECTsum(invline.gross)FROM invlineWHERE invline.type > 4 AND invline.invoice_id = 200003),invoice.dr_total = (SELECTsum(invline.gross)FROM invlineWHERE invline.type <5 AND invline.invoice_id = 200003)WHERE invoice.id = 200003;--TESTselect invoice.dr_total, invoice.cr_total from invoice where invoice.id=200003---------------------------------------------------------------------------------------------ERROR: null value in column "po_cr_total" violates not-null constraint
Anyone?
Mike
--
Fábio Dias Moreira
On 7 March 2016 at 07:43, Michael Rowan <michael.rowan3@gmail.com> wrote:
There is a difference between the behaviour of sum and count. Count returns the number of found records(0 in this case) while sum returns the sum of the given expression from the found records. And anything plus null is always null in Postgres.I have a trigger function problem that threatens to cause my early demise. Please someone help an absolute novice.I have two tables, invoice and invline, with the usual functions.Table invoice has a column for the total of debit lines, and a column for the total of credit (payment) lines. The function is triggered by any change in inv.grossThe ERROR occurs if there are no invline found by either summing part, where we set dr_total or cr_total.If I change "sum" to "count" it works as expected, returning 0.00--THE FUNCTIONUPDATE invoice SETinvoice.cr_total = (SELECTsum(invline.gross)FROM invlineWHERE invline.type > 4 AND invline.invoice_id = 200003),invoice.dr_total = (SELECTsum(invline.gross)FROM invlineWHERE invline.type <5 AND invline.invoice_id = 200003)WHERE invoice.id = 200003;--TESTselect invoice.dr_total, invoice.cr_total from invoice where invoice.id=200003---------------------------------------------------------------------------------------------ERROR: null value in column "po_cr_total" violates not-null constraint
Anyone?
Mike
However you can use the coalesce function which returns its first non-null argument to ensure a non null result.
SELECT
coalece(sum(invline.gross), 0)
FROM invline
WHERE invline.type > 4 AND invline.invoice_id = 200003
or
invoice.cr_total = coalesce((SELECT
sum(invline.gross)
FROM invline
WHERE invline.type > 4 AND invline.invoice_id = 200003),0)
Regards,
Sándor