ogr2ogr -sql select a layer name with space

Question

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\""

• PolyGeo: I think you should cut/paste your solution/answe from the area reserved for questions into the area reserved for answers.

Answer 1

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\""