Regclass and quoted table names
Hi all,
It looks like the behavior of regclass is not consistent when table
names are quoted. The name is returned without the quotes if the name is
lower case with eventual trailing numbers, otherwise it is returned with
quotes.
See some examples here:
tpch=# CREATE VIEW test AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "test1" AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "'test2'" AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "testcolumnVIEW" AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "testcolumnview" AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW testcolumnVIEW2 AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "1test" AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "1test2abc" AS SELECT * FROM customer;
CREATE VIEW
tpch=# CREATE VIEW "1test2" AS SELECT * FROM customer;
CREATE VIEW
tpch=# select c.oid , c.oid::regclass from pg_class c where c.relname
like '%test%';
oid | oid
-------+------------------
16410 | test
16413 | test1
16416 | "'test2'"
16419 | "testcolumnVIEW"
16422 | testcolumnview
16425 | testcolumnview2
16428 | "1test2abc"
16431 | "1test2"
16434 | "1test"
(9 rows)
Is this a bug?
manu
--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com
Emmanuel Cecchet <manu@asterdata.com> writes:
It looks like the behavior of regclass is not consistent when table
names are quoted. The name is returned without the quotes if the name is
lower case with eventual trailing numbers, otherwise it is returned with
quotes.
It's intentional that it quotes only when needed.
regards, tom lane
Tom Lane wrote:
Emmanuel Cecchet <manu@asterdata.com> writes:
It looks like the behavior of regclass is not consistent when table
names are quoted. The name is returned without the quotes if the name is
lower case with eventual trailing numbers, otherwise it is returned with
quotes.It's intentional that it quotes only when needed.
This is problematic in situations where the output of the cast is involved in some later join which returns incorrect results because of the extra double quotes surrounding the table name.
Is there a way to override the default behavior to have a consistent quoted or non-quoted result?
Thanks,
Emmanuel
--
Emmanuel Cecchet
Aster Data Systems
Web: http://www.asterdata.com
On Wed, Mar 4, 2009 at 12:34 PM, Emmanuel Cecchet <manu@asterdata.com> wrote:
This is problematic in situations where the output of the cast is involved
in some later join which returns incorrect results because of the extra
double quotes surrounding the table name. Is there a way to override the
default behavior to have a consistent quoted or non-quoted result?
regclass's output format is intended for human readers, not for
joining against text columns. Why would you need to be joining between
regclass and text anyways? Normally you would be joining regclass to
either regclass columns or oid columns where it does an integer
comparison.
Actually normally you would just be joining oid columns since most
catalog columns are declared to be oids rather than regfoo columns :(
--
greg
Greg Stark <stark@enterprisedb.com> writes:
On Wed, Mar 4, 2009 at 12:34 PM, Emmanuel Cecchet <manu@asterdata.com> wrote:
This is problematic in situations where the output of the cast is involved
in some later join which returns incorrect results because of the extra
double quotes surrounding the table name. Is there a way to override the
default behavior to have a consistent quoted or non-quoted result?
regclass's output format is intended for human readers, not for
joining against text columns. Why would you need to be joining between
regclass and text anyways?
The quoting behavior is the *least* of your problems if you're trying to
do that. Are you aware of how it works vis-a-vis search_path? Have you
thought about what will happen when you rename a table?
Use plain oids or regclass values, not a text column, if you are trying
to store table identities.
regards, tom lane
Use plain oids or regclass values, not a text column, if you are trying
to store table identities.
wouldn`t oids change on dump/reload?
Greetings
Marcin
marcin mank wrote:
Use plain oids or regclass values, not a text column, if you are trying
to store table identities.wouldn`t oids change on dump/reload?
I don't know. I'd also be interested to know if there is a difference if
we use pg_restore with a binary format or sql dump, or if that does not
influence at all the way oids are created.
manu