39

I am trying to perform a postgres dump of a specific table using -t. However, the table has a capital letter in it and I get a "No matching tables were found." I tried using quotations and double quotations around the table name but they did not work. How can I get pg to recognize the capitals? Thanks!

pg_dump -h hostname dbname -t tableName > pgdump.sql
sheldonk
  • 2,604
  • 3
  • 22
  • 32

7 Answers7

75

Here is the complete command to dump your table in plain mode:

pg_dump --host localhost --port 5432 --username "postgres" --role "postgres"  --format plain  --file "complete_path_file" --table "schema_name.\"table_name\"" "database_name"

OR you can just do:

pg_dump -t '"tablename"' database_name > data_base.sql

Look to the last page here: Documentation

evandrix
  • 6,041
  • 4
  • 27
  • 38
Houari
  • 5,326
  • 3
  • 31
  • 54
  • 1
    In windows it still doesn't work. `> pg_dump --table="\"Accounts\"" -U postgres -d helium2 > dump.s ql` Gives the following error: `pg_dump: [archiver (db)] query failed: ERROR: invalid regular expression: parentheses () not balanced pg_dump: [archiver (db)] query was: SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind in ('r', 'S', 'v', 'm', 'f') AND c.relname ~ E'^(\\accounts\\)$' AND pg_catalog.pg_table_is_visible(c.oid)` – htellez Mar 27 '15 at 03:20
  • Hi, I was working along this blog post, and it covers also a mix of specific schema / specific table name / mixed case settings: http://sptl.eu/2015/01/03/backup-a-mixed-case-table-from-a-specific-schema-via-pg_dump/ – sal Nov 03 '15 at 15:13
  • Adding the Schema is VERY VITAL – saviour123 Apr 28 '20 at 18:01
15

The above solutions do not work for me under Windows 7 x64. PostgreSQL 9.4.5. But this does, at last (sigh):

-t "cms.\"FooContents\""

either...

pg_dump.exe -p 8888 --username=user -t "cms.\"FooContents\"" basdb

...or...

pg_dump.exe -p 8888 --username=user -table="cms.\"FooContents\"" basdb
Frank N
  • 9,625
  • 4
  • 80
  • 110
  • 1
    This is also the only solution that worked for me on a Raspbian, thanks. – dislick Sep 11 '18 at 06:33
  • 2
    This also worked in Windows 10 x64 and PostgreSQL 12. Thank you. – r3gularJ0hn May 11 '20 at 13:50
  • I'm pretty sure you don't need to quote the full table spec unless it contains whitespace. That's a command processor thing, not pg_dump. `pg_dump.exe -p 8888 --username=user --table=cms.\"FooContents\" basdb` – Tom Aug 17 '20 at 15:45
6

Inside a cmd window, I had to put three (!) double quotes around the table name if it countains upper case letters. Example pg_dump -t """Colors""" database > database.colors.psql

Dirk Zabel
  • 61
  • 1
  • 1
1

Powershell

the good (shortest)

& 'C:\Program Files\PostgreSQL\12\bin\pg_dump.exe' -d db_name -t '\"CasedTableName\"'

the bad (requires --%)

& 'C:\Program Files\PostgreSQL\12\bin\pg_dump.exe' --% -d db_name -t "\"CasedTableName\""

the ugly (requires `")

& 'C:\Program Files\PostgreSQL\12\bin\pg_dump.exe' -d db_name -t "\`"CasedTableName\`""

The main point of confusion for me was the absolute necessity of having \" in there. I assumed that maybe there was a weird bug in the way powershell or psql was parsing the arguments, but it turns out it's explained in the docs:

Some native commands expect arguments that contain quote characters. Normally, PowerShell's command line parsing removes the quote character you provided. The parsed arguments are then joined into a single string with each parameter separated by a space. This string is then assigned to the Arguments property of a ProcessStartInfo object. Quotes within the string must be escaped using extra quotes or backslash (\) characters.

And of course ProcessStartInfo.Arguments Remarks tells us:

To include quotation marks in the final parsed argument, triple-escape each mark.

Nathan Chappell
  • 2,099
  • 18
  • 21
0

This worked for me: 

pg_dump -f file.sql -U user -t 'schema.\"Table\"' database
Vikash Madhow
  • 1,287
  • 11
  • 15
0

As part of a node script I had to surround with single and double quotes, e.g.

` ... --table 'public."IndexedData"'`

The accepted solution worked in a bash console, but not as part of a node script, only the single quote approach.

aroundtheworld
  • 731
  • 1
  • 5
  • 15
0

Thanks to @Dirk Zabel suggestion, the following worked for me:

Windows 10 CMD

pg_dump -d "MyDatabase" -h localhost -p 5432 -U postgres --schema=public -t """TableName""" > TableName.sql

Bash

pg_dump -d "MyDatabase" -h localhost -p 5432 -U postgres --schema=public -t "\"TableName\"" > TableName.sql
William Le
  • 825
  • 1
  • 9
  • 16