pgsql2shp error when query is introduced

Question

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?

Answer 1

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.