pgsql table/subquery input bug

[jamierob]

When selecting data from a PostGIS table there is an issue with an error being thrown saying there is an unterminated quoted string around the field value. removing the quotes around the value then throws an error saying the column doesn't exist. add the single quotes back around the value and then the data is loaded successfully.

reproduction: A. add a postGIS table with a subquery like this:

( SELECT *
  FROM um_line
  WHERE handle = 'mtrail'
) AS data

error thrown:

Postgis Plugin: PSQL error:
ERROR:  unterminated quoted string at or near "'
) AS data WHERE "geom" IS NOT NULL LIMIT 1;"
LINE 3:   WHERE handle = ‘mtrail'
                                ^
Full sql was: 'SELECT ST_SRID("geom") AS srid FROM ( SELECT *
  FROM um_line
  WHERE handle = ‘mtrail'
) AS data WHERE "geom" IS NOT NULL LIMIT 1;'

B. remove quotes around value, ie - 'mtrail' like this:

( SELECT *
  FROM um_line
  WHERE handle = mtrail
) AS data

error thrown:

Postgis Plugin: PSQL error:
ERROR:  column "mtrail" does not exist
LINE 3:   WHERE handle = mtrail
                         ^
Full sql was: 'SELECT ST_SRID("geom") AS srid FROM ( SELECT *
  FROM um_line
  WHERE handle = mtrail
) AS data WHERE "geom" IS NOT NULL LIMIT 1;'

C. add quotes back around field value, ie - 'mtrail' exactly like the original attempt:

( SELECT *
  FROM um_line
  WHERE handle = 'mtrail'
) AS data

and the data loads as expected.

[springmeyer]

looks to me like your first query must have an invalid quote: it used ` instead of '

[jamierob]

I figured out what was really happening within tilemill to cause this issue. If you copy a subquery from one working layer and paste it into the subquery section of of another layer, and then edit the field value, the leading quote is changed from the type that works to the type that throws an error. here's a quick video: http://youtu.be/P5tVGVtESiQ

[artbotterell]

OK, I seem to be having the same sort of problem in 0.10.1 The subquery I attempt is:

(SELECT * FROM roads WHERE type = "primary") AS data

The error I get is:

Postgis Plugin: PSQL error: ERROR: column "primary" does not exist LINE 1: ...") AS srid FROM (SELECT * FROM roads WHERE type = "primary")... ^ Full sql was: 'SELECT ST_SRID("geom") AS srid FROM (SELECT * FROM roads WHERE type = "primary") AS data WHERE "geom" IS NOT NULL LIMIT 1;'

[springmeyer]

@jamierob - thanks for figuring that out and reporting back.

@artbotterell - your issue is more simple: your table just does not have the primary field.

[stevage]

Is it caused by OS X "smart quotes" by any chance?

Also, does using Shift-Cmd-V for paste avoid the problem?

(FWIW, I use Chrome on OS X, with smart quotes on and have never seen any problem like this, but I'm sure I've seen something similar with someone else, possibly not with TileMill though.)

[jamierob]

@stevage : That's exactly what it is. When I do the procedure above with the smart quotes > single quotes option set to the last one in the list, i'm able to modify the sql queries of new layers without having to replace the quotes.