Table/column names quoted and written with upper case while creating table will not be recognized

[jorgeas80]

Hello,

I ran into this problem, making tests of GDAL CartoDB driver (which basically makes calls to SQL API):

This sequence of calls work as expected: table created and cartodbfyied:

Create table:

https://jorgearevalo.cartodb.com/api/v2/sql?q=CREATE TABLE "country_borders" ( cartodb_id SERIAL,the_geom GEOMETRY(POINT, 4326), the_geom_webmercator GEOMETRY(POINT, 3857),"Fips Cntry" VARCHAR,"Cntry Name" VARCHAR,"gx_id" VARCHAR,PRIMARY KEY (cartodb_id) );DROP SEQUENCE IF EXISTS "country_borders_cartodb_id_seq" CASCADE;CREATE SEQUENCE "country_borders_cartodb_id_seq" START 1;ALTER TABLE "country_borders" ALTER COLUMN cartodb_id SET DEFAULT nextval('"country_borders_cartodb_id_seq"')&api_key=API_KEY

Cartodbfy table:

https://jorgearevalo.cartodb.com/api/v2/sql?q=SELECT cdb_cartodbfytable('jorgearevalo', 'country_borders')&api_key=API_KEY

But in this other sequence, the second call fails:

Create table:

https://jorgearevalo.cartodb.com/api/v2/sql?q=CREATE TABLE "TM_WORLD_BORDERS_SIMPL" ( cartodb_id SERIAL,the_geom GEOMETRY(MULTIPOLYGON, 4326), the_geom_webmercator GEOMETRY(MULTIPOLYGON, 3857),"FIPS" VARCHAR,"ISO2" VARCHAR,"ISO3" VARCHAR,"UN" INTEGER,"NAME" VARCHAR,"AREA" INTEGER,"POP2005" INTEGER,"REGION" INTEGER,"SUBREGION" INTEGER,"LON" FLOAT8,"LAT" FLOAT8,"gx_id" VARCHAR,PRIMARY KEY (cartodb_id) );DROP SEQUENCE IF EXISTS "TM_WORLD_BORDERS_SIMPL_cartodb_id_seq" CASCADE;CREATE SEQUENCE "TM_WORLD_BORDERS_SIMPL_cartodb_id_seq" START 1;ALTER TABLE "TM_WORLD_BORDERS_SIMPL" ALTER COLUMN cartodb_id SET DEFAULT nextval('"TM_WORLD_BORDERS_SIMPL_cartodb_id_seq"')&api_key=API_KEY

Cartodbfy table (ERROR):

https://jorgearevalo.cartodb.com/api/v2/sql?q=SELECT cdb_cartodbfytable('jorgearevalo', 'TM_WORLD_BORDERS_SIMPL')&api_key=API_KEY

This is the error:

{"error":["relation \"tm_world_borders_simpl\" does not exist"]}

I suspected this was a lexical problem. So, I tried with

SELECT cdb_cartodbfytable('jorgearevalo', '"TM_WORLD_BORDERS_SIMPL"')

And it worked.

The problem here is Postgres folds unquoted identifiers (like table names) to lower case, but respects the quoted ones. So, if you specify a table's name using upper case AND quotes, you'll need to use quotes and upper case to refer to it.

So, maybe we should filter all the tables and columns names, folding them to lower case, to avoid this issue (you know... not a bug, it's a feature)