[postgis-users] problem with apostrophe

[Michael Fuhr]

On Mon, Jan 15, 2007 at 03:29:47PM +0100, Andreas Neumann wrote:
> When I run "psql -f data.sql -d mydb" I get the following error message:
> 
> =========
> 
> psql:hydrography.sql:6302: WARNING:  nonstandard use of \' in a string 
> literal

This isn't an error; it's a warning.  The data should have loaded --
did it?

PostgreSQL 8.1 and later have an escape_string_warning configuration
variable that controls these warnings.  In 8.1 the default configuration
is "off"; in 8.2 the default is "on".

> How do I correctly format such a string containing apostrophes? Is there 
> an option for shp2pgsql to correctly handle the apostrophs in text 
> strings when converting from ESRI shapefiles? Or do I need to choose a 
> certain encoding (-W)?

You could use -D, which emits COPY statements instead of INSERTs,
or you could set escape_string_warning to off while loading data.

For compatibility with 8.2 and future versions of PostgreSQL,
shp2pgsql should probably convert ' to '' instead of \'.  Maybe it
could also have an option to use the escape string syntax (E'...')
available in 8.1 and later.

-- 
Michael Fuhr