Thread: [BUGS] pg_dump: patterns and tables with uppercase letters

[BUGS] pg_dump: patterns and tables with uppercase letters

From
"Andrea Urbani"
Date:
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.
This means, i.e., that if your tables are called  "tDocuments" and "tDocumentsFiles" if you call
    pg_dump --dbname=healthorganizer --username=hor --table=tDocument*
processSQLNamePattern will output
    c.relname ~ '^(tdocument*)$'
and if you use the double quote
    pg_dump --dbname=healthorganizer --username=hor --table='"tDocument*"'
processSQLNamePattern will output
    c.relname ~ '^(tDocument\*)$'
and both the instructions will not find those tables.
I suggest to add a parameter to the processSQLNamePattern function to choose between a case-sensitive or case-insensitive compare
c.relname ~ '^(tdocument*)$'
c.relname ~* '^(tdocument*)$'
and to use it in the expand_table_name_patterns method of pg_dump.c.
Bye
Andrea
matfanjol@user.sf.net
 

Re: [BUGS] pg_dump: patterns and tables with uppercase letters

From
Michael Paquier
Date:
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

Re: [BUGS] pg_dump: patterns and tables with uppercase letters

From
"Andrea Urbani"
Date:
Hello Michael,
it works: thank you and sorry.
Bye
Andrea
 
Sent: Thursday, December 22, 2016 at 5:32 AM
From: "Michael Paquier" <michael.paquier@gmail.com>
To: "Andrea Urbani" <matfanjol@mail.com>
Cc: "PostgreSQL mailing lists" <pgsql-bugs@postgresql.org>
Subject: Re: [BUGS] pg_dump: patterns and tables with uppercase letters
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