2

I'm trying to export a table from PostGIS using pgsql2shp. The basic query to export the entire table works fine using the following format:

"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp" -h
localhost -p 5432 -u postgres -P xxxx mapdata planning.dm_planning-apps

However when I add a query (which works fine in PGAdmin III) the command fails, due to the hyphen in the table name.

Working query in PGAdmin:

SELECT * FROM planning."dm_planning-apps" WHERE "dm_planning-apps"."Modified_Reference"
ILIKE '%np%'

Included in command:

"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp"  -h
localhost -p 5432 -u postgres -P xxxx mapdata "SELECT * FROM planning."dm_planning-apps"
WHERE "dm_planning-apps"."Modified_Reference" ILIKE '%np%'"

Produces a "syntax error at or near "-" and highlights the hyphen in "...FROM planning.dm_planning-apps WHERE...". The double quotes obviously aren't doing the same job they were within PDAdmin where the query worked fine.

Can anyone suggest a way to make this query work without renaming tables?

3

There's probably an issue with escaping double quotes in your final command. To bypass that you could create a view in your Postgres database and give it a simple name. Something like:

CREATE VIEW v_export AS
  SELECT * FROM planning."dm_planning-apps" 
    WHERE "dm_planning-apps"."Modified_Reference" ILIKE '%np%';

Then from the command line you could use

"C:\Program Files\PostgreSQL\9.1\bin\pgsql2shp.exe" -f "D:\path\shapefile.shp" -h localhost -p 5432 -u postgres -P xxxx mapdata v_export

It's kind of a cheat, but it should work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.