2

I am trying to make sense of how to format data values for a popup.

I have values that are reading $890000, and I want them to read $890,000. How would I format my SQL statement?

More generally, is there documentation on how SQL syntax works in Carto? (I see this symbol | in between statements, and I am trying to understand what that means)

closed as too broad by BBG_GIS, whyzar, aldo_tapia, Mapperz Dec 15 '17 at 18:41

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

If you want to change all the values in the current column, you can open the SQL window and use this command:

update tablename
set dollarvaluecolumn = cast(dollarvaluetable as money)

money is a data type used by postgresql, so is usable in CARTO

However, if you want to create a new column and strip the data of the $ sign and convert to integers, you can run this on a new integer column:

update tablename
set newdollarvaluecolumn = cast(substring(dollarvaluecolumn from 2) as integer)
  • The field I am trying to use was field calculated. It is giving me an error when I try to use the first query you wrote. Is there a way to access this field anyway? I wish to use the first query: update tablename set dollarvaluecolumn = cast(dollarvaluetable as money) – Isabelle Lipton Dec 14 '17 at 18:58
  • @IsabelleLipton what's the error? – DPSSpatial Dec 14 '17 at 20:56
  • Syntax error: column "string_sales_vol" does not exist This field was created from an original field, in order to add a $ at the beginning. When I try the query with the original field, I get Syntax error: cannot cast type double precision to money – Isabelle Lipton Dec 14 '17 at 22:38
  • 1
    I'd say go back to the original field and create a new field that you can update to the original field cast to MONEY type - don't manually add the $ if you can avoid it... does that make sense? – DPSSpatial Dec 14 '17 at 22:52
1

You have three options:

  1. From the SQL panel of the DATA tab on your BUILDER dashboard modify the SELECT statement to create a new virtual field. This is OK if your dataset is not really big and very common approach to create custom values for pop ups. In your case you probably want first need to remove the dollar sign, then format it conveniently, something like

    SELECT *,
           to_char(
             replace(
               "my_money_field"::text,
               '$',
               ''
             )::bigint,
             '$999,999,999'
           )
      FROM my_table
    
  2. You can update your current field directly as @DPSSpatial suggested

  3. You can create a new column and populate it with the derived value

I would suggest never do 2, so you create a new column if your dataset is big or you know you will use that value for other purposes, or you directly apply a SELECT statement in your dashboard.

CARTO uses PostgreSQL 9.5 so you can follow the official documentation for more details on the SQL language syntax.

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