Avoid Division By Zero In PostgreSQL


Answer :

You can use NULLIF function e.g.

something/NULLIF(column_name,0)

If the value of column_name is 0 - result of entire expression will be NULL


Since count() never returns NULL (unlike other aggregate functions), you only have to catch the 0 case (which is the only problematic case anyway):

CASE count(column_name)    WHEN 0 THEN 1    ELSE count(column_name) END

Quoting the manual about aggregate functions:

It should be noted that except for count, these functions return a null value when no rows are selected.


I realize this is an old question, but another solution would be to make use of the greatest function:

greatest( count(column_name), 1 )  -- NULL and 0 are valid argument values

Note: My preference would be to either return a NULL, as in Erwin and Yuriy's answer, or to solve this logically by detecting the value is 0 before the division operation, and returning 0. Otherwise, the data may be misrepresented by using 1.


Comments

Post a Comment

Popular posts from this blog

Chemistry - Bond Angles In NH3 And NCl3

Answer : Solution 1: As Tan Yong Boon stated, it is Bent’s Rule with which we can explain the lower bond angle of \ce N F 3 \ce{NF3} \ce NF 3 when compared to \ce N H 3 \ce{NH3} \ce N H 3 . The rule as stated by Henry Bent: Atomic s character concentrates in orbitals directed towards electropositive substituents. Fluorine is more electronegative that hydrogen, and the \ce N − F \ce{N−F} \ce N − F bond would have greater p character than the \ce N − H \ce{N−H} \ce N − H bond. And more s character leads to large bond angles. Thus, the bond angle is greater in \ce N H 3 \ce{NH3} \ce N H 3 than in \ce N F 3 \ce{NF3} \ce NF 3 . Now, consider \ce N C l 3 \ce{NCl3} \ce NCl 3 . Clearly, \ce C l \ce{Cl} \ce Cl atom islarger in size than the central atom, nitrogen. Hence the higher bond angle here is due to the steric crowding caused by \ce C l \ce{Cl} \ce Cl atoms.(More pronounced than the electrongativity of \ce C l \ce{Cl} \ce Cl atom). They repel eachother and hence b...
Read more

Are Regular VACUUM ANALYZE Still Recommended Under 9.1?

Answer : VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs. These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there is a n_dead_tup column that tells, per table, how much rows need to be vacuumed. (see Monitoring statistics in the doc for functions and views related to statistics gathering). A possible strategy in your case would be to suppress the scheduled VACUUM, keeping an eye on this view and checking on which tables the n_dead_tup is going up significantly. Then apply the aggressive VACUUM to these tables only. This will be a win if there are large tables whose rows never get deleted nor updated and the aggressive VACUUM is really necessary only on smaller tables. But keep running the ANALYZE for the optimiz...
Read more

Change The Font Size Of Visual Studio Solution Explorer

Answer : Go to Tools->Options->Environment->Fonts and Colors In "Show Settings for" chose "Environment Font" In "Font" replace Automatic to for example, Arial and change size. Change the font size of that setting, all Font size in the Explorers (Solution Explorer, Server Explorer and Toolbox) and menus will change. You can set that in Tools, Options.
Read more