2

I need to round numbers that are in my table in CartoDB, so that in the Map visualization infowindows I only show results to two decimal digits rather than the long decimals that are currently showing.

I tried using SELECT round(column,10,2) FROM table as suggested on this webpage How can I show numbers as percentages in CartoDB?

but the SQL query results in no records, and I get the following error:

function round(double precision, integer) does not exist

Is there another way to reformat the numbers so they display fewer decimals in the CartoDB infowindows without editing the source data?

4

I was running into the same problem on CartoDB, and was able to find a solution based on some of the other answers here.

If you are running this query...

SELECT round(c, 2) FROM t

and get the error message function round(double precision, integer) does not exist

then the problem is that column c is double precision type rather than the numeric type that the round() function requires when there are two arguments. (Somewhat confusingly, the round() function will accept a double precision type but only if there is just one argument.)

Although the solution from @user2033157 may work, you probably don't want to alter the entire table column just to run a query. So here is a solution using CAST:

SELECT round( CAST(c as numeric), 2 ) FROM t
1
  • This worked for me and I think is a more complete solution than the top voted answer Sep 3 '15 at 11:36
2

I think the syntax of your ROUND() function is incorrect. You are passing three inputs (column, 10, and 2) to the function, but it only needs two.

Try using SELECT ROUND(column,2) FROM table.

2
  • Unfortunately @Erika I tried that syntax and still get the same error that "function round(double precision, integer) does not exist". I'm currently inputting the ROUND() function into the SQL window for the table in CartoDB.
    – jackie
    Jul 22 '14 at 16:41
  • I can only assume that CartoDB does not use the SQL ROUND() function, unfortunately... which seems strange. Sorry!
    – Erica
    Jul 22 '14 at 16:45
1

As Erica pointed out, you have one too many inputs in the syntax. However, the error message function round(double precision, integer) does not exist indicates that you actually used the correct syntax but have the wrong data type in the column to be rounded. The ROUND function requires the numeric data type, whereas the default number type on CartoDB is dp. So you need first convert the data type:

ALTER TABLE name_of_table ALTER COLUMN column_to_be_rounded TYPE numeric

After having done that, SELECT ROUND(column_to_be_rounded,2) FROM table should work.

If you just want to round to the nearest integer, you can skip the data type conversion and just use ROUND(column_to_be_rounded).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.