Thread: [BUGS] pg_dump: patterns and tables with uppercase letters
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
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*)$'
c.relname ~* '^(tdocument*)$'
and to use it in the expand_table_name_patterns method of pg_dump.c.
Bye
Andrea
matfanjol@user.sf.net
Andrea
matfanjol@user.sf.net
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
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
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
> 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