Opened 11 years ago

Last modified 8 years ago

#1440 new defect

v.in.ogr's where clause cannot handle uppercase column names for Postgres

Reported by: arencambre Owned by: grass-dev@…
Priority: major Milestone: 6.4.6
Component: Vector Version: 6.4.1
Keywords: Cc: aren@…
CPU: x86-64 Platform: MSWindows 7

Description

In Postgres, if a column name has upper case characters, it must be surrounded by quote marks in SQL queries. If the column name is not surrounded by quote marks, it is interpreted as lower case.

For example, if Postgres sees SELECT * FROM x WHERE AAaa = 'hi', it looks for column aaaa, which is not the same as AAaa. If you want to match AAaa, you have to use SELECT * FROM x WHERE "AAaa" = 'hi'.

There appears to be no variant of escaping that works correctly with this with v.in.ogr's where parameter. For example, I have a column named Cnty_ID. I've tried all these variants of the where clause with no luck:

  • "where="Cnty_ID" = '57'" -- translates to Cnty_Id = '57'.
  • "where=\"Cnty_ID\" = '57'" -- translates to \Cnty_ID\ = '57'.
  • where=""Cnty_ID" = '57'" -- translates to Cnty_Id = '57'.
  • where="""Cnty_ID"" = '57'" -- translates to Cnty_Id = '57'.

The only documentation I could find on escaping in commands is at http://grass.osgeo.org/gdp/html_grass64/wxGUI.html.

This is happening on 6.4.1. (That option not available in the Version dropdown.)

Change History (2)

comment:1 by arencambre, 11 years ago

Version: unspecified6.4.1

comment:2 by martinl, 8 years ago

Milestone: 6.4.6
Note: See TracTickets for help on using tickets.