creating tables though the SQL API

intro

CartoDB has a SQL API that allows you to execute arbitrary SQL queries so you can select, insert or remove data from a table. You can also create/drop a table but there is a problem: the table is not shown in the CartoDB editor.

The reason is that CartoDB editor needs a special schema for the table, for example it needs to have a cartodb_id column among other ones.

In order to simplify that we have created a method to register tables in the editor.

creating a table visible in the editor

lets create a table using SQL API:

create table test (whatever int);

at this point you will not able to see table test in the editor

select cdb_cartodbfytable('test');

Now if you go to your dashboard should appear (it takes some seconds to be available, so refresh after a bit if it's not there)

For people using multiuser account you need to include your username in cdb_cartodbfytable call:

select cdb_cartodbfytable('myuser', 'test');

renaming a table

use the standard SQL query:

alter table test rename to test_renamed;

this will change the name you see in CartoDB editor. It takes some seconds to realize the table rename so for seconds you could see the old name.

remove a table

use the standard SQL query:

drop table test;

this drops the table from your CartoDB editor dashboard. It takes some seconds to realize the table is not there so for seconds you could see the old name.

take into account that if you remove a table, all the layers using it will be removed. So even if you create a new table with the same name, internally it will be a different table