From: | Oliver Kohll - Mailing Lists <oliver(dot)lists(at)gtwm(dot)co(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Division by zero |
Date: | 2009-06-03 15:48:15 |
Message-ID: | 367A1AAB-F401-4DE5-A21E-4BA68FCF7DE4@gtwm.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hello,
We have a system that allows users to create views containing
calculations but divisions by zero are commonly a problem.
An simple example calculation in SQL would be
SELECT cost / pack_size AS unit_cost from products;
Either variable could be null or zero.
I don't think there's a way of returning null or infinity for
divisions by zero, rather than causing an error but I'd just like to
check - and put in a vote for that functionality!
If not, I will have to get the system to check for any numeric fields
in user input calculations and rewrite them similar to
CASE WHEN cost IS NULL THEN null
WHEN pack_size IS NULL THEN null
WHEN cost = 0 THEN null
WHEN pack_size = 0 THEN null
ELSE cost / pack_size
AS unit_cost
I don't want to write new functions, I'd rather keep it in plain SQL.
Best regards
Oliver Kohll
oliver(at)gtwm(dot)co(dot)uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product
From | Date | Subject | |
---|---|---|---|
Next Message | Carlos Oliva | 2009-06-03 16:11:55 | Re: Upgrading Database: need to dump and restore? |
Previous Message | Tom Lane | 2009-06-03 15:47:46 | Re: SPI_ERROR_UNCONNECTED in python callback function |