[postgis-users] OGR2OGR Uppercase/Lowercase

David Blasby dblasby at refractions.net
Mon Oct 7 10:30:13 PDT 2002


San,

The reason I haven't added the always-quoting-column-names is because it can
cause confusion.

For example,

CREATE TABLE MyTable (PlaceCode int, PlaceName text);
SELECT AddGeometryColumn(...'MyGeometry'...);

You've now got a table that has changed the 'PlaceCode' column to 'placecode'
and 'PlaceName' to 'placename' and 'MyTable' to 'mytable'.  On the other hand,
you've
added a geometry column with the name 'MyGeometry'.  If you were to access the
table with something like:

SELECT placename,mygeometry FROM mytable;

You'll get an error because there is no column named mygeometry (its
'MyGeometry').  Even a query like:

SELECT PlaceName,MyGeometry FROM MyTable;

Will not work!

On the other hand, I do see your point - its probably a good idea to allow the
postgis.sql functions "AddGeometryColumn",  "DropGeometryColumn", and a few
others to always assume that the column and table names are assumed to be
littorals.  Hopefully I'll be able to mitigate user confusion by sending out
NOTICES that the column name isn't what they're expecting.

The mapserver connector is another problem - I think its a bad idea to always
quote column (and table) names!

For example, the above example "SELECT PlaceName,MyGeometry FROM MyTable;" will
now fail because the column "PlaceName" is really 'placename' and the table
name "MyTable" is really 'mytable'.  There will be a lot of people
(justifiably) upset that their SQL isn't working in mapserver when its working
perfectly fine in psql.

Besides, you can always do queries like:
DATA 'the_geom from (SELECT "MyGeometry" as the_geom, placename,oid FROM
mytable) as foo using unique oid using SRID=1234'

I dont know much about the shp2pgsql utility, so I will not comment on that
(perhaps jeff will).

What do you think?

dave
ps. I really do think you're asking for trouble if your database has to quote
column or table names.







More information about the postgis-users mailing list