pg_dump -t '"Table"' for cmd.exe

On Mon, Nov 12, 2012 at 8:45 PM, Mike Toews <mwtoews@gmail.com> wrote:

I'm not sure if this is worth documenting, but pg_dumping mixed-case tables
with the '-t table' option appears to not be accurately documented for
cmd.exe. Here are my four attempts, with only the last as success:

I believe this complaint is basically the same as this recent one:
http://archives.postgresql.org/pgsql-bugs/2012-10/msg00044.php

and this behavior confused me recently as well. I agree the behavior
should be documented, though it should be documented for all our
utilities which accept a --table argument and pass it on as a
supposedly already-escaped identifier.

Incidentally, I'm really not fond of the existing behavior because it:
a.) is counterintuitive
b.) requires the user to escape table names themselves, and how were
they even supposed to have a chance of programmatically doing this
properly before we exposed PQescapeIdentifier()
c.) opens possible SQL injection holes, e.g.
reindexdb --table='foo; ALTER ROLE limited WITH superuser'

Josh

On Tue, Nov 13, 2012 at 02:45:05PM +1300, Mike Toews wrote:

I'm not sure if this is worth documenting, but pg_dumping mixed-case tables
with the '-t table' option appears to not be accurately documented for cmd.exe.
Here are my four attempts, with only the last as success:

Intuitive (supplying "My Table" like "My Database"), but not correct:
C:\>"C:\Program Files\PostgreSQL\9.1\bin\pg_dump.exe" -U postgres -t "My Table"
"My Database"
pg_dump: No matching tables were found

As documented in last example at http://www.postgresql.org/docs/9.1/static/
app-pgdump.html
C:\>"C:\Program Files\PostgreSQL\9.1\bin\pg_dump.exe" -U postgres -t '"My
Table"' "My Database"
pg_dump: No matching tables were found

Escaping quotes, but using single quotes:
C:\>"C:\Program Files\PostgreSQL\9.1\bin\pg_dump.exe" -U postgres -t '\"My
Table\"' "My Database"
pg_dump: too many command-line arguments (first is "My Database")

Escaping quotes, but using double quotes:
C:\>"C:\Program Files\PostgreSQL\9.1\bin\pg_dump.exe" -U postgres -t "\"My
Table\"" "My Database"

works!

Good point. Docs updated with attached patch, and patched to 9.3 beta
as well.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +