pg_dump: patterns and tables with uppercase letters
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Hello to everybody,</div>
<div>if you have table names with uppercase letters you will not be able to use the patterns in pg_dump.<br/>
In particular the processSQLNamePattern function, inside src/fe_utils/strings_utils.c, is converting to lowercase when the text is not double quoted, but when it is double quoted, all the |*+?()[]{}.^$\ characters are quoted.<br/>
This means, i.e., that if your tables are called "tDocuments" and "tDocumentsFiles" if you call</div>
<div> pg_dump --dbname=healthorganizer --username=hor --table=tDocument*</div>
<div>processSQLNamePattern will output</div>
<div> c.relname ~ '^(tdocument*)$'</div>
<div>and if you use the double quote</div>
<div> pg_dump --dbname=healthorganizer --username=hor --table='"tDocument*"'</div>
<div>processSQLNamePattern will output</div>
<div> c.relname ~ '^(tDocument\*)$'</div>
<div>and both the instructions will not find those tables.</div>
<div>I suggest to add a parameter to the processSQLNamePattern function to choose between a case-sensitive or case-insensitive compare</div>
<div>c.relname ~ '^(tdocument*)$'<br/>
c.relname ~* '^(tdocument*)$'</div>
<div>and to use it in the expand_table_name_patterns method of pg_dump.c.</div>
<div>Bye<br/>
Andrea<br/>
matfanjol@user.sf.net</div>
<div> </div>
</div></div></body></html>
On Wed, Dec 21, 2016 at 8:35 PM, Andrea Urbani <matfanjol@mail.com> wrote:
Hello to everybody,
if you have table names with uppercase letters you will not be able to use
the patterns in pg_dump.
In particular the processSQLNamePattern function, inside
src/fe_utils/strings_utils.c, is converting to lowercase when the text is
not double quoted, but when it is double quoted, all the |*+?()[]{}.^$\
characters are quoted.
You are missing the point here... The pattern analysis matches what is
done in psql. So you could do want you want by moving the regex
pattern out of the double quotes for example:
$ pg_dump --table '"tDocuments"*' | grep "CREATE TABLE" | grep tDocuments
CREATE TABLE "tDocuments" (
CREATE TABLE "tDocumentsFiles" (
The fine docs explain the full behavior here, the first paragraph
being especially explicit about the use of double quotes:
https://www.postgresql.org/docs/devel/static/app-psql.html#app-psql-patterns
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Hello Michael,</div>
<div>it works: thank you and sorry.</div>
<div>Bye</div>
<div>Andrea</div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Sent:</b> Thursday, December 22, 2016 at 5:32 AM<br/>
<b>From:</b> "Michael Paquier" <michael.paquier@gmail.com><br/>
<b>To:</b> "Andrea Urbani" <matfanjol@mail.com><br/>
<b>Cc:</b> "PostgreSQL mailing lists" <pgsql-bugs@postgresql.org><br/>
<b>Subject:</b> Re: [BUGS] pg_dump: patterns and tables with uppercase letters</div>
<div name="quoted-content">On Wed, Dec 21, 2016 at 8:35 PM, Andrea Urbani <matfanjol@mail.com> wrote:<br/>
> Hello to everybody,<br/>
> if you have table names with uppercase letters you will not be able to use<br/>
> the patterns in pg_dump.<br/>
> In particular the processSQLNamePattern function, inside<br/>
> src/fe_utils/strings_utils.c, is converting to lowercase when the text is<br/>
> not double quoted, but when it is double quoted, all the |*+?()[]{}.^$\<br/>
> characters are quoted.<br/>
<br/>
You are missing the point here... The pattern analysis matches what is<br/>
done in psql. So you could do want you want by moving the regex<br/>
pattern out of the double quotes for example:<br/>
$ pg_dump --table '"tDocuments"*' | grep "CREATE TABLE" | grep tDocuments<br/>
CREATE TABLE "tDocuments" (<br/>
CREATE TABLE "tDocumentsFiles" (<br/>
<br/>
The fine docs explain the full behavior here, the first paragraph<br/>
being especially explicit about the use of double quotes:<br/>
<a href="https://www.postgresql.org/docs/devel/static/app-psql.html#app-psql-patterns" target="_blank">https://www.postgresql.org/docs/devel/static/app-psql.html#app-psql-patterns</a><br/>
--<br/>
Michael</div>
</div>
</div>
</div></div></body></html>