CODEWITHSUNDEEP
postgresqlshell
sheldonk
sheldonk

Reputation: 2684

Postgres dump specific table with a capital letter

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

Upvotes: 42

Views: 22927

Answers (10)

Frees
Frees

Reputation: 61

Killed several hours... For C# as argument for Process.Start() the following worked for me:

--table \"public.\\\"{tableName}\\\"\"

Full code:

private async Task GetTableInserts(string tableName)
{
    Environment.SetEnvironmentVariable("PGPASSFILE", PgpassFileFullPath);
    var p = new Process()
    {
        StartInfo = new ProcessStartInfo()
        {
            FileName = PgdumpPath,
            CreateNoWindow = true,
            Arguments = $"--file \"{TempFileFullPath}\" --host {Ip4} --port {Port} --username {User} --no-password --role postgres --format=p --encoding=UTF8  --section=data --inserts --column-inserts --enable-row-security --verbose --table \"public.\\\"{tableName}\\\"\" -d {DbName}",
        }
    };
    p.Start();
    await p.WaitForExitAsync();
}

Upvotes: 1

Som
Som

Reputation: 1627

Only below command worked for me in windows 10 (postgres 11)

.\pg_dump.exe -h host_name -U user_name -t 'schema.\"TableName\"' --data-only --column-inserts DB_Name > data.sql

PS: hack is this format -t 'schema."TableName"'

Upvotes: 1

Roman Rhrn Nesterov
Roman Rhrn Nesterov

Reputation: 3673

Wrap a table by quotes '"tableName"'

pg_dump -h hostname dbname -t '"tableName"' > pgdump.sql

Upvotes: 2

Nathan Chappell
Nathan Chappell

Reputation: 2436

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.

Upvotes: 3

William Le
William Le

Reputation: 1171

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

Upvotes: 1

aroundtheworld
aroundtheworld

Reputation: 782

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.

Upvotes: 1

Houari
Houari

Reputation: 5621

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

Upvotes: 81

Vikash Madhow
Vikash Madhow

Reputation: 1344

This worked for me: 

pg_dump -f file.sql -U user -t 'schema.\"Table\"' database

Upvotes: 1

Frank N
Frank N

Reputation: 10386

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

Upvotes: 17

Dirk Zabel
Dirk Zabel

Reputation: 71

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

Upvotes: 7

Related Questions