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