How to Generate a Fake cartodb_id

Overview

When working with nested queries using WITH statements or multiple subqueries in CARTO, you may need to generate a new cartodb_id column for the final query to function properly.

Solution

Use PostgreSQL’s row_number() and over() functions to create fake cartodb_id values:

SELECT
  row_number() over() as cartodb_id
FROM
  table_name

This approach assigns sequential numbering to each row, creating the necessary identifier column for downstream query operations.