Uploading in Carto changes column order

Question

Has anyone noticed that sometimes when uploading to Carto via the app (my tests are just from CSV) the column order is different from source, and sometimes it isn't??

I have not noticed the same problem uploading directly via the SQL API, but have not tested extensively.

Question: is there any way I control whether column order is kept intact when uploading?

Context: I'd like to standardise an update process where a new table is uploaded then the existing table is truncated and values populated from the newly uploaded table. A la:

TRUNCATE table;
INSERT INTO table
SELECT * FROM newtable;

This does not work if the columns are in a different order - I get a type error because the index is pointing to the wrong column!

Answer 1

Instead of * type your column names explicitly. That way you have full control:

TRUNCATE table;
INSERT INTO table
SELECT id, geom, col3, col4, col5 FROM newtable;

Make sure it corresponds with your table format.

Answer 2

CARTO is using Postgres and that database engine does not guarantee any ordering on the columns when using *. In your SELECT queries you should make explicit which columns and in which order you want them.