pg_dump and quoted identifiers

2016-12-13 17:38 GMT+01:00 Thomas Kellerer <spam_eater@gmx.net>:

Inspired by this question:

http://dba.stackexchange.com/q/158044/1822

I tried that for myself, and it seems that pg_dump indeed can not parse
quoted identifiers:

psql (9.6.1)
Type "help" for help.

postgres=# create table "Statuses" (id integer);
CREATE TABLE
postgres=# \q

-bash-4.1$ pg_dump -d postgres -t "Statuses"
pg_dump: no matching tables were found

-bash-4.1$ pg_dump -d postgres -t '"Statuses"'
pg_dump: no matching tables were found

-bash-4.1$ pg_dump -d postgres -t 'public."Statuses"'
pg_dump: no matching tables were found

Running 9.6.1 on CentOS 6 but under Windows this is the same.

Any ideas?

pg_dump -t '"Statuses"' postgres

Regards

Pavel

Thomas Kellerer <spam_eater@gmx.net> writes:

postgres=# create table "Statuses" (id integer);
CREATE TABLE

-bash-4.1$ pg_dump -d postgres -t "Statuses"
pg_dump: no matching tables were found

This is expected since those quotes are eaten by the shell.

-bash-4.1$ pg_dump -d postgres -t '"Statuses"'
pg_dump: no matching tables were found

-bash-4.1$ pg_dump -d postgres -t 'public."Statuses"'
pg_dump: no matching tables were found

These cases work for me. Maybe your shell is doing something weird
with the quotes?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

pg_dump -t '"Statuses"' postgres

​To elaborate - your shell was removing the double-quotes. You need make
it so after shell processing the double-quotes remain. Wrapping the
double-quote string in single-quotes should do it.

David J.
​

David G. Johnston schrieb am 13.12.2016 um 18:05:

On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>>wrote:

pg_dump -t '"Statuses"' postgres

​To elaborate - your shell was removing the double-quotes. You need
make it so after shell processing the double-quotes remain. Wrapping
the double-quote string in single-quotes should do it.

That was one of the options I tried, but that neither worked on Linux (using bash) nor on Windows

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane schrieb am 13.12.2016 um 18:02:

-bash-4.1$ pg_dump -d postgres -t '"Statuses"'
pg_dump: no matching tables were found

-bash-4.1$ pg_dump -d postgres -t 'public."Statuses"'
pg_dump: no matching tables were found

These cases work for me. Maybe your shell is doing something weird
with the quotes?

Hmm, that's the default bash from CentOS 6 (don't know the exact version)

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Tue, Dec 13, 2016 at 11:24 AM, Thomas Kellerer <spam_eater@gmx.net>
wrote:

David G. Johnston schrieb am 13.12.2016 um 18:05:

On Tue, Dec 13, 2016 at 9:43 AM, Pavel Stehule <pavel.stehule@gmail.com
<mailto:pavel.stehule@gmail.com>>wrote:

pg_dump -t '"Statuses"' postgres

​To elaborate - your shell was removing the double-quotes. You need
make it so after shell processing the double-quotes remain. Wrapping
the double-quote string in single-quotes should do it.

That was one of the options I tried, but that neither worked on Linux
(using bash) nor on Windows

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

​pg_dump -t '"Statuses"' (that's " marks around the word Statuses and '
marks around that. On Fedora:

[tsh009@it-johnmckown-linux junk]$ pg_dump -t '"Status"'
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.5
-- Dumped by pg_dump version 9.5.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: Status; Type: TABLE; Schema: public; Owner: tsh009
--

CREATE TABLE "Status" (
x integer
);

ALTER TABLE "Status" OWNER TO tsh009;

--
-- Data for Name: Status; Type: TABLE DATA; Schema: public; Owner: tsh009
--

COPY "Status" (x) FROM stdin;
1
\.

--
-- PostgreSQL database dump complete
--

--
Heisenberg may have been here.

http://xkcd.com/1770/

Maranatha! <><
John McKown

Thomas Kellerer <spam_eater@gmx.net> writes:

Tom Lane schrieb am 13.12.2016 um 18:02:

These cases work for me. Maybe your shell is doing something weird
with the quotes?

Hmm, that's the default bash from CentOS 6 (don't know the exact version)

I'm using bash from current RHEL6, should be the same.

I'm suspicious that you're not actually typing plain-ASCII single and
double quotes, but some fancy curly quote character.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane schrieb am 13.12.2016 um 19:35:

These cases work for me. Maybe your shell is doing something weird
with the quotes?

Hmm, that's the default bash from CentOS 6 (don't know the exact version)

I'm using bash from current RHEL6, should be the same.

I'm suspicious that you're not actually typing plain-ASCII single and
double quotes, but some fancy curly quote character.

Definitely not. I typed this manually on the command line using Putty

Bash version is "GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu)"

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/13/2016 11:18 PM, Thomas Kellerer wrote:

Tom Lane schrieb am 13.12.2016 um 19:35:

These cases work for me. Maybe your shell is doing something weird
with the quotes?

Hmm, that's the default bash from CentOS 6 (don't know the exact version)

I'm using bash from current RHEL6, should be the same.

I'm suspicious that you're not actually typing plain-ASCII single and
double quotes, but some fancy curly quote character.

Definitely not. I typed this manually on the command line using Putty

So you are reaching the Bash shell via Putty on a Windows machine, correct?

So have you tried the answer from the SO question?:

"\"Statuses\""

Bash version is "GNU bash, version 4.1.2(1)-release (x86_64-redhat-linux-gnu)"

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Adrian Klaver schrieb am 14.12.2016 um 15:32:

I'm suspicious that you're not actually typing plain-ASCII single and
double quotes, but some fancy curly quote character.

Definitely not. I typed this manually on the command line using Putty

So you are reaching the Bash shell via Putty on a Windows machine, correct?

Correct.

So have you tried the answer from the SO question?:

"\"Statuses\""

Still doesn't work:

-bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
pg_dump: no matching tables were found

Must apparently be something strange with the bash on that server.

It's not really important, as we never use quoted identifiers where I work anyway (and the above problem is precisely one of the reasons).

I was just curious what the correct solution would be, but apparently there is no single answer and it depends on the environment.

Thomas

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 12/14/2016 11:37 PM, Thomas Kellerer wrote:

Adrian Klaver schrieb am 14.12.2016 um 15:32:

I'm suspicious that you're not actually typing plain-ASCII single and
double quotes, but some fancy curly quote character.

Definitely not. I typed this manually on the command line using Putty

So you are reaching the Bash shell via Putty on a Windows machine, correct?

Correct.

So have you tried the answer from the SO question?:

"\"Statuses\""

Still doesn't work:

-bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
pg_dump: no matching tables were found

Must apparently be something strange with the bash on that server.

Have you tried using using something other then Putty to connect to the
machine and/or from a non-Windows machine?

It's not really important, as we never use quoted identifiers where I work anyway (and the above problem is precisely one of the reasons).

I was just curious what the correct solution would be, but apparently there is no single answer and it depends on the environment.

Thomas

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thomas Kellerer <spam_eater@gmx.net> writes:

Adrian Klaver schrieb am 14.12.2016 um 15:32:

So have you tried the answer from the SO question?:

Still doesn't work:
-bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
pg_dump: no matching tables were found

Hmm. It might shed some light if you put "echo" in front of that
to see what gets printed:

$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Dec 15, 2016 at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
...

Hmm. It might shed some light if you put "echo" in front of that
to see what gets printed:

$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

Also, when having strange issues, I've found the combo

echo <original command> | od -tx1 -tc

very useful, this help rule out potential fancy quotes pointed previously

Francisco Olarte.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Tom Lane schrieb am 15.12.2016 um 16:20:

Still doesn't work:
-bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
pg_dump: no matching tables were found

Hmm. It might shed some light if you put "echo" in front of that
to see what gets printed:

$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

Same here:

-bash-4.1$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

Also, when having strange issues, I've found the combo

echo <original command> | od -tx1 -tc

very useful, this help rule out potential fancy quotes pointed previously

-bash-4.1$ echo pg_dump -d postgres -t "\"Statuses\"" | od -tx1 -tc
0000000 70 67 5f 64 75 6d 70 20 2d 64 20 70 6f 73 74 67
p g _ d u m p - d p o s t g
0000020 72 65 73 20 2d 74 20 22 53 74 61 74 75 73 65 73
r e s - t " S t a t u s e s
0000040 22 0a
" \n
0000042

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thursday, December 15, 2016 5:15:44 PM EST Thomas Kellerer wrote:

Tom Lane schrieb am 15.12.2016 um 16:20:

Still doesn't work:
-bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
pg_dump: no matching tables were found

Hmm. It might shed some light if you put "echo" in front of that
to see what gets printed:

$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

[snip]

Crazy guess: Is pg_dump shadowed by a (maybe distro provided) script that eats
your quotes?

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thomas Kellerer <spam_eater@gmx.net> writes:

Tom Lane schrieb am 15.12.2016 um 16:20:

Hmm. It might shed some light if you put "echo" in front of that
to see what gets printed:
$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

Same here:
-bash-4.1$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

So no light there ... next step would be to set log_statement = all
and see what pg_dump's last few queries before failing are.
I see this with correct quoting:

2016-12-15 12:06:15.211 EST [693] LOG: statement: 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', 'P')
AND c.relname ~ '^(Statuses)$'
AND pg_catalog.pg_table_is_visible(c.oid)

and this if I omit quotes:

2016-12-15 12:07:19.735 EST [741] LOG: statement: 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', 'P')
AND c.relname ~ '^(statuses)$'
AND pg_catalog.pg_table_is_visible(c.oid)

(This test is on HEAD; I suppose the 'P' case is pretty new...)

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

________________________________________
Von: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org]&quot; im Auftrag von &quot;Thomas Kellerer [spam_eater@gmx.net]
Gesendet: Donnerstag, 15. Dezember 2016 17:15
An: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] pg_dump and quoted identifiers

Tom Lane schrieb am 15.12.2016 um 16:20:

Still doesn't work:
-bash-4.1$ pg_dump -d postgres -t "\"Statuses\""
pg_dump: no matching tables were found

Hmm. It might shed some light if you put "echo" in front of that
to see what gets printed:

$ echo pg_dump -d postgres -t "\"Statuses\""
pg_dump -d postgres -t "Statuses"

Hello,

Have you checked if the problem is limited to pg_dump ?
Maybe you're facing a trivial mistake, like a space in the table name at creation time or a wrong database...
psql -d postgres -c "select * from \"Statuses\" "

regards,
Marc Mamin

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general