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 now 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 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.