Escape double quotes issue in pgsql2shp

[kalxas]

On behalf of Sergej Dugokontov:

Error executing main scan query: ERROR: zero-length delimited identifier at or near """" LINE 1: DECLARE cur CURSOR FOR SELECT ""quoteColumn",ST_AsEWKB(ST_Se…

If I change sql to:

SELECT
c1 AS "geom",
c2 AS """""quoteColumn"
FROM t1

I got different error:

Error executing main scan query: ERROR:  column ""quoteColumn" does not
exist
LINE 1: DECLARE cur CURSOR FOR SELECT """quoteColumn",ST_AsEWKB(ST_S...
                                      ^
HINT:  Perhaps you meant to reference the column
"__pgsql2shp22020_tmp_table.""quoteColumn".

I use pgsql2shp RELEASE: 2.2.1 (r14555) on Ubuntu 16.04 LTS and don't have any issues if column alias in sql doesn't contain double quotes.

[kalxas]

Reposting:

I have an issue when I try to create shapefile using pgsql2shp if column alias contains double quotes. For example, my SQL query is:

SELECT
c1 AS "geom",
c2 AS """quoteColumn"
FROM t1

I store this sql in a file, and if I run from console

$ pgsql2shp -r -g geom -f results -h localhost -u userName -P password
databaseName "`cat query.sql`" 1>/dev/null

I get following error:

Error executing main scan query: ERROR:  zero-length delimited identifier
at or near """"
LINE 1: DECLARE cur CURSOR FOR SELECT ""quoteColumn",ST_AsEWKB(ST_Se...

If I change sql to:

SELECT
c1 AS "geom",
c2 AS """""quoteColumn"
FROM t1

I got different error:

Error executing main scan query: ERROR:  column ""quoteColumn" does not
exist
LINE 1: DECLARE cur CURSOR FOR SELECT """quoteColumn",ST_AsEWKB(ST_S...
                                      ^
HINT:  Perhaps you meant to reference the column
"__pgsql2shp22020_tmp_table.""quoteColumn".

I use pgsql2shp RELEASE: 2.2.1 (r14555) on Ubuntu 16.04 LTS and don't have any issues if column alias in sql doesn't contain double quotes.

[robe]

I don't think that DBFs support columns with quotes in them to being with. Why is it you are wanting to do this? Seems strange to want to alias a column to have quotes.

I did this for example and it worked using

RELEASE: 2.3.1dev (r15238)

pgsql2shp -r -g geom -f test pgr "SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn""

However the output column gets output as QUOTECOLUMN

If you use the -k option to preserve case, the quotes still get stripped I think

pgsql2shp -k -r -g geom -f test pgr "SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn""

So my dbf column ends up being quoteColumn.

Anyrate, I think your quoting might still be off by a bit and seems like a waste of effort.

[robe]

okay I tried again with this, which might be what you meant.

I started off with a table that has quotes in column:

So my dbf column ends up being quoteColumn.

Anyrate, I think your quoting might still be off by a bit and seems like a waste of effort.

I also tried this first creating a table:


{{{
SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn" INTO testq;
}}}


Then outputing the table




{{{  --run from psql
SELECT ST_SetSRID(ST_Point(1,2)::geometry,4326)  AS "geom", 1 AS """quoteColumn" INTO testq;

Then I tried to export the table

pgsql2shp -r -g geom -f testq pgr testq

and it failed with your error. So seems there is something wrong with our quote escaping logic.

[robe]

In 15441:

Escape double quotes issue in pgsql2shp
Include test for boolean and quotes
References #3701 for PostGIS 2.4 (trunk)

[robe]

In 15442:

Backport fixes for pgsql2shp for Boolean field length and quote escaping
Includes tests for both
Closes #3682
Closes #3701

[robe]

In 15443:

Backport test sql script missed in last commit
References #3682 for 2.3.3
References #3701 for 2.3.3