1

I have a problem. If artikel.preis is 2430 and umsatz.opreis is 2722. 

COALESCE(ROUND(artikel.preis/NULLIF(umsatz.opreis,0),0),9999) as kontroll

In MySQL i get 1 as result but in PostgresSQL i get 0.

Why is it like that? 2430/2722 = 0.8927 and should be rounded to 1.

How can i get this to work in Postgres and what is other in postgres then in MySQL?

fthiella
  • 48,073
  • 15
  • 90
  • 106
user2210516
  • 613
  • 3
  • 15
  • 32

1 Answers1

3

In PostgreSQL, the / operator does the division based on the types of its operands, if both artikel.preis and umsatz.opreis are integers the division will be an integer division:

select 2430/2722

will return 0, while

select 2430::float/2722::float

will return the expected result. Try with this:

COALESCE(ROUND(artikel.preis::float/NULLIF(umsatz.opreis::float,0),0),9999) as kontroll

however, be careful about umsatz.opreis being null or being zero, it will result in a divide by zero condition. If you have problems with round this query will convert the result back to integer:

COALESCE((artikel.preis::float/umsatz.opreis::float)::int,9999) as kontroll
fthiella
  • 48,073
  • 15
  • 90
  • 106
  • Thanks for the help but now it stand round funktion doesnt exist. if i take that away i get 0.8927.. but i need to round the answer as a INT – user2210516 Jan 05 '16 at 17:17
  • @user2210516 which version of postgresql do you have? please see my updated answer – fthiella Jan 05 '16 at 17:20