Re: [postgis-users] Removed double quotes from column and table names
Thanks all for the suggestions.SOn Tue, 20 Mar 2012 05:10:28 +1100, Lee Hachadoorian lee.hachadooria...@gmail.com wrote:This option is not available via SPIT, but ogr2ogr does allow it as a layer creation option:"LAUNDER: This may be "YES" to force new fields created on this layer to have their field names "laundered" into a form more compatible with PostgreSQL. This converts to lower case and converts some special characters like "-" and "#" to "_". If "NO" exact names are preserved. The default value is "YES". If enabled the table (layer) name will also be laundered." (http://www.gdal.org/ogr/drv_pg.html) Also shp2pgsql defaults to forcing lower case names (the correct choice, IMHO), but has an option to specify preserving case.Best,--LeeOn Mon, Mar 19, 2012 at 6:58 AM, Simon Greener si...@spatialdbadvisor.com wrote: Birgit, I realise that I can do this manually. I was looking for a method for doing this at the point of loading the shapefile into PostGIS using QGIS. I will have a look at ogr2ogr to see if that helps. Thanks regards Simon On Mon, 19 Mar 2012 21:00:27 +1100, Birgit Laggner birgit.lagg...@vti.bund.de wrote: Hi Simon, I would think RENAME would do what you want: ALTER TABLE schema."TableName" RENAME TO tablename; ALTER TABLE schema.tablename RENAME COLUMN "ColumnName" TO columnname; Hope that helps, Birgit. Am 19.03.2012 06:03, schrieb Simon Greener: Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of "2011 Oracle Spatial Excellence Award for Education and Research." SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18" E) Latitude: -43.01530 (43° 00' 55" S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of "2011 Oracle Spatial Excellence Award for Education and Research."SpatialDB Advice and Design, Solutions Architecture and Programming,Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified ProfessionalOracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist.39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia.Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397Mobile: +61 418 396391Skype: sggreenerLongitude: 147.20515 (147° 12' 18" E)Latitude: -43.01530 (43° 00' 55" S)GeoHash: r22em9r98wgNAC:W80CK 7SWP3___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
On 21 March 2012 11:52, Simon Greener si...@spatialdbadvisor.com wrote: Thanks all for the suggestions. S If you are using postgres 9.0 or later, try something like this to rename tables: DO $$DECLARE r record; BEGIN FOR r IN (SELECT relname, regexp_replace(lower(relname), E'[ \-]+', '_', 'g') AS new_relname FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND relkind='r' AND relname quote_ident(relname)) LOOP EXECUTE 'ALTER TABLE ' || quote_ident(r.relname) || ' RENAME TO ' || quote_ident(r.new_relname) || ';'; END LOOP; END$$ A similar query can be used to rename columns. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
Very useful, Mike, very useful. Thanks S On Wed, 21 Mar 2012 10:09:34 +1100, Mike Toews mwto...@gmail.com wrote: On 21 March 2012 11:52, Simon Greener si...@spatialdbadvisor.com wrote: Thanks all for the suggestions. S If you are using postgres 9.0 or later, try something like this to rename tables: DO $$DECLARE r record; BEGIN FOR r IN (SELECT relname, regexp_replace(lower(relname), E'[ \-]+', '_', 'g') AS new_relname FROM pg_class c JOIN pg_namespace n ON n.oid=c.relnamespace WHERE n.nspname='public' AND relkind='r' AND relname quote_ident(relname)) LOOP EXECUTE 'ALTER TABLE ' || quote_ident(r.relname) || ' RENAME TO ' || quote_ident(r.new_relname) || ';'; END LOOP; END$$ A similar query can be used to rename columns. -Mike ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
Hi Simon, I would think RENAME would do what you want: ALTER TABLE schema.TableName RENAME TO tablename; ALTER TABLE schema.tablename RENAME COLUMN ColumnName TO columnname; Hope that helps, Birgit. Am 19.03.2012 06:03, schrieb Simon Greener: Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
Birgit, I realise that I can do this manually. I was looking for a method for doing this at the point of loading the shapefile into PostGIS using QGIS. I will have a look at ogr2ogr to see if that helps. Thanks regards Simon On Mon, 19 Mar 2012 21:00:27 +1100, Birgit Laggner birgit.lagg...@vti.bund.de wrote: Hi Simon, I would think RENAME would do what you want: ALTER TABLE schema.TableName RENAME TO tablename; ALTER TABLE schema.tablename RENAME COLUMN ColumnName TO columnname; Hope that helps, Birgit. Am 19.03.2012 06:03, schrieb Simon Greener: Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
Check out the pg_* system tables table, you should be able to bulk update the appropriate table to have lower case column names only... P On Mon, Mar 19, 2012 at 3:58 AM, Simon Greener si...@spatialdbadvisor.com wrote: Birgit, I realise that I can do this manually. I was looking for a method for doing this at the point of loading the shapefile into PostGIS using QGIS. I will have a look at ogr2ogr to see if that helps. Thanks regards Simon On Mon, 19 Mar 2012 21:00:27 +1100, Birgit Laggner birgit.lagg...@vti.bund.de wrote: Hi Simon, I would think RENAME would do what you want: ALTER TABLE schema.TableName RENAME TO tablename; ALTER TABLE schema.tablename RENAME COLUMN ColumnName TO columnname; Hope that helps, Birgit. Am 19.03.2012 06:03, schrieb Simon Greener: Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
Re: [postgis-users] Removed double quotes from column and table names
This option is not available via SPIT, but ogr2ogr does allow it as a layer creation option: LAUNDER: This may be YES to force new fields created on this layer to have their field names laundered into a form more compatible with PostgreSQL. This converts to lower case and converts some special characters like - and # to _. If NO exact names are preserved. The default value is YES. If enabled the table (layer) name will also be laundered. (http://www.gdal.org/ogr/drv_pg.html) Also shp2pgsql defaults to forcing lower case names (the correct choice, IMHO), but has an option to specify preserving case. Best, --Lee On Mon, Mar 19, 2012 at 6:58 AM, Simon Greener si...@spatialdbadvisor.comwrote: Birgit, I realise that I can do this manually. I was looking for a method for doing this at the point of loading the shapefile into PostGIS using QGIS. I will have a look at ogr2ogr to see if that helps. Thanks regards Simon On Mon, 19 Mar 2012 21:00:27 +1100, Birgit Laggner birgit.lagg...@vti.bund.de wrote: Hi Simon, I would think RENAME would do what you want: ALTER TABLE schema.TableName RENAME TO tablename; ALTER TABLE schema.tablename RENAME COLUMN ColumnName TO columnname; Hope that helps, Birgit. Am 19.03.2012 06:03, schrieb Simon Greener: Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S __**_ postgis-users mailing list postgis-users@postgis.**refractions.netpostgis-users@postgis.refractions.net http://postgis.refractions.**net/mailman/listinfo/postgis-**usershttp://postgis.refractions.net/mailman/listinfo/postgis-users -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 __**_ postgis-users mailing list postgis-users@postgis.**refractions.netpostgis-users@postgis.refractions.net http://postgis.refractions.**net/mailman/listinfo/postgis-**usershttp://postgis.refractions.net/mailman/listinfo/postgis-users ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
[postgis-users] Removed double quotes from column and table names
Is there any way to remove the double quotes created around a table or its column names? My names do NOT include spaces but do include mixed case. I wish to remove the quotes and lower case all names. How can this be done? Note: the tables were loaded by QGIS into PostgreSQL. In the DBF file the names are mixed case. I can see nothing in the PostGIS/QGIS plugin SPIT that allows me to tell it to lower case all names. S -- Holder of 2011 Oracle Spatial Excellence Award for Education and Research. SpatialDB Advice and Design, Solutions Architecture and Programming, Oracle Database 10g Administrator Certified Associate; Oracle Database 10g SQL Certified Professional Oracle Spatial, SQL Server, PostGIS, MySQL, ArcSDE, Manifold GIS, FME, Radius Topology and Studio Specialist. 39 Cliff View Drive, Allens Rivulet, 7150, Tasmania, Australia. Website: www.spatialdbadvisor.com Email: si...@spatialdbadvisor.com Voice: +61 362 396397 Mobile: +61 418 396391 Skype: sggreener Longitude: 147.20515 (147° 12' 18 E) Latitude: -43.01530 (43° 00' 55 S) GeoHash: r22em9r98wg NAC:W80CK 7SWP3 ___ postgis-users mailing list postgis-users@postgis.refractions.net http://postgis.refractions.net/mailman/listinfo/postgis-users
