Обсуждение: pg_table_size errors "invalid name syntax" for table names containing spaces
Hi
Sorry I am not used to mailing lists so please excuse me if I am not doing this correctly.
Also I am operating on PostgreSQL 13.2, so not the newest version, but I have no way to upgrade right now or to test on the most recent version and it should be really easy to test for anyone who has the recent version running.
Without further ado, this fails for me and I think it shouldn't:
CREATE TABLE "wei rd" (id SERIAL);
SELECT pg_table_size('wei rd');
SELECT pg_table_size('wei rd');
So just creating a table containing spaces, the pg_table_size, also pg_total_relation_size and maybe other administrative functions fail with error message "invalid name syntax".
Thanks
Michel from TogetherDB
Re: pg_table_size errors "invalid name syntax" for table names containing spaces
От
"David G. Johnston"
Дата:
On Thursday, June 10, 2021, Michel Helms <michel@togetherdb.com> wrote:
CREATE TABLE "wei rd" (id SERIAL);
SELECT pg_table_size('wei rd');So just creating a table containing spaces, the pg_table_size, also pg_total_relation_size and maybe other administrative functions fail with error message "invalid name syntax".
You still have to double-quote the name even if its being passed around in a string literal.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, June 10, 2021, Michel Helms <michel@togetherdb.com> wrote:
>> CREATE TABLE "wei rd" (id SERIAL);
>> SELECT pg_table_size('wei rd');
> You still have to double-quote the name even if its being passed around in
> a string literal.
Yeah. The reason for this is that you're also allowed to write qualified
table names:
SELECT pg_table_size('myschema.mytable');
That would seem to introduce an ambiguity: is the dot a schema separator,
or just an ordinary character (in a table name that was presumably written
with double quotes originally)? We resolve this by saying that the
parsing rules for regclass_in are the same as they are in SQL text,
so you have to double-quote anything that is not a plain identifier
or needs to be protected against case-folding.
Hence, you should write
SELECT pg_table_size('"wei rd"');
regards, tom lane
Re: pg_table_size errors "invalid name syntax" for table names containing spaces
От
Michel Helms
Дата:
Ahh okay, thank you both for the clarification!
On Thu, Jun 10, 2021 at 8:43 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, June 10, 2021, Michel Helms <michel@togetherdb.com> wrote:
>> CREATE TABLE "wei rd" (id SERIAL);
>> SELECT pg_table_size('wei rd');
> You still have to double-quote the name even if its being passed around in
> a string literal.
Yeah. The reason for this is that you're also allowed to write qualified
table names:
SELECT pg_table_size('myschema.mytable');
That would seem to introduce an ambiguity: is the dot a schema separator,
or just an ordinary character (in a table name that was presumably written
with double quotes originally)? We resolve this by saying that the
parsing rules for regclass_in are the same as they are in SQL text,
so you have to double-quote anything that is not a plain identifier
or needs to be protected against case-folding.
Hence, you should write
SELECT pg_table_size('"wei rd"');
regards, tom lane