ROUND Function - SQL Server to PostgreSQL Migration

In SQL Server, the ROUND function rounds the number to the nearest value with the specified precision, or truncates the number to the specified precision.

In PostgreSQL you can use the ROUND and TRUNC functions.

SQL Server:

  -- Round to 1 decimal digit (rounded to lower value as 4 <= 5)
  SELECT ROUND(123.34, 1);
  /* 123.30 */  
 
  -- Round to 1 decimal digit (rounded to upper value as 6 > 5)
  SELECT ROUND(123.36, 1);
  /* 123.40 */  
 
  -- When the 3rd parameter is non-zero then the value is just truncated
  SELECT ROUND(123.36, 1, 1);
  /* 123.30 */

PostgreSQL:

  -- Round to 1 decimal digit (rounded to lower value as 4 <= 5)
  SELECT ROUND(123.34, 1);
  /* 123.30 */  
 
  -- Round to 1 decimal digit (rounded to upper value as 6 > 5)
  SELECT ROUND(123.36, 1);
  /* 123.40 */  
 
  -- TRUNC function has to be used for truncation
  SELECT TRUNC(123.36, 1);
  /* 123.30 */

PostgreSQL does not support ROUND with 3 parameters, so you need to use TRUNC for truncation.

If the precision is negative, the ROUND function rounds or truncates the integer part:

SQL Server:

  -- Round to tens (rounded to lower value as 3 <= 5)
  SELECT ROUND(123, -1);
  /* 120 */  
 
  -- Round to hundreds (rounded to upper value as 56 > 50)
  SELECT ROUND(156, -2);
  /* 200 */  
 
  -- When the 3rd parameter is non-zero then the value is just truncated
  SELECT ROUND(156, -2, 1);
  /* 100 */

PostgreSQL:

  -- Round to tens (rounded to lower value as 3 <= 5)
  SELECT ROUND(123, -1);
  /* 120 */  
 
  -- Round to hundreds (rounded to upper value as 56 > 50)
  SELECT ROUND(156, -2);
  /* 200 */  
 
  -- TRUNC function has to be used for truncation
  SELECT TRUNC(156, -2);
  /* 100 */

For more information, see SQL Server to PostgreSQL Migration.