SQL OGR2OGR – Selecting Layer Name with Space

ogr2ogrsql

I'd like to ingest some shp files into PostGIS using ogr2ogr from my Mac Terminal. The name of these shp files contains spaces.

Here is my original script and it works perfectly

ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=geom PG:"host="xx" port="xx" user="xx" dbname="xx" password="xx"" -lco OVERWRITE=YES -nlt GEOMETRY nr\ test.shp -nln poly

However, the script failed after I add -sql statement to select some specific attributes from the shp file.

ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=geom PG:"host="xx" port="xx" user="xx" dbname="xx" password="xx"" -lco OVERWRITE=YES -nlt GEOMETRY nr\ test.shp -nln poly -sql "SELECT Name FROM "nr test""

## Error
Warning 1: layer names ignored in combination with -sql.
ERROR 1: SELECT from table nr failed, no such table/featureclass.

I tried several combinations of quotes but none of them worked.

-sql "SELECT Name FROM 'nr test'"
-sql "SELECT Name FROM "'nr test'""
-sql "SELECT Name FROM '"nr test"'"

Is there anyway to accommodate the space in the -sql statement?

Solution:

Using @user30184’s answer, my layers are loaded successfully with escaped double quotes

ogr2ogr -f "PostgreSQL" -lco GEOMETRY_NAME=geom PG:"host="xx" port="xx" user="xx" dbname="xx" password="xx"" -lco OVERWRITE=YES -nlt GEOMETRY nr\ test.shp -nln poly -sql "SELECT LEGEND FROM \"nr test\""

Best Answer

Layer name with space character is a so called delimited identifier in SQL. See for example https://www.ibm.com/docs/en/informix-servers/12.10?topic=identifier-delimited-identifiers.

Delimited identifiers must be enclosed between double quotes "delimited identifier". To prevent the truncation of the -sql parameter in ogr2ogr that must also appear between double quotes these internal double quotes must be escaped with back slash.

-sql "select * from \"delimited identifier\""

Related Question