[gdal-dev] joining shapefile to PG table

[William Kyngesburye]

On Jul 7, 2011, at 4:22 PM, Even Rouault wrote:

> 
>> Only simplified the names.  The shapefile name starts with numbers (natural
>> earth shapefiles).  There's the problem.  I removed the leading numbers,
>> and simplified the shapefile names to a few letters (they're also long
>> names), no syntax error (segfault).  Renamed with a short name with a
>> single leading number, again the syntax error.
>> 
>> So, it's a SQL limitation on names?
> 
> Yes, SQL identifiers cannot start by a number without being quoted (the SQL 
> engine of previous versions was perhaps a bit more tolerant, but the newer 
> behaviour is closer to the one of the SQL engines you find in DBs such as 
> postgres, mysql, sqlite, etc...)
> 
> I've managed to make the request work (*) by changing it like the following :
> 
> ogrinfo 123.dbf  -sql "select * from '123' onetwothree left join 
> 'PG:dbname=autotest port=5432'.poly on onetwothree.prfedea = poly.prfedea"
> 
> 1) quote the layername after the FROM : '123'
> 2) just after, add an alias name for the layername : onetwothree
> 3) use the alias name in the ON because it won't like 123 or '123'

Ah, sweet, I don't have to rename all my shapefiles :)  The quoting and aliasing works.  Thanks.

-----
William Kyngesburye <kyngchaos*at*kyngchaos*dot*com>
http://www.kyngchaos.com/

"History is an illusion caused by the passage of time, and time is an illusion caused by the passage of history."

- Hitchhiker's Guide to the Galaxy