Hey! This content applies only to previous CARTO products

Please check if it's relevant to your use case. On October 2021 we released a new version of our platform.
You can learn more and read the latest documentation at docs.carto.com

Questions / Working with Data / SQL

How to work with NULL values in CARTO datasets

Learn how to select, populate or delete NULL values in CARTO datasets.

To replace all NULL values in a column you can use the COALESCE PostgreSQL function. This replaces null with 0, because column is number-type:

UPDATE table_name SET column = COALESCE(column, 0)

If your column type is string, make sure to enclose the second COALESCE argument in single quotes.

You can do the opposite with NULLIF, thus replacing non-null values with NULL ones:

UPDATE populated_places SET pop_max = NULLIF(pop_max, -99)

You can also use the PostgreSQL instruction NULLS FIRST or NULLS LAST to define where you want the NULLs to be!

SELECT
    cartodb_id,
    column
FROM
  table_name
ORDER BY
  column DESC NULLS LAST