Postgres query doesn't accept double quote around schema name in query statement

On Apr 27, 2023, at 12:40, Michael Xu <online.mxu@gmail.com> wrote:
In our env, it throws 42P01:relation "ads.MyTableName" does not exist.

The function of double quotes in SQL is to allow you do include characters that would otherwise not be legal in an identifier (as well as making the identifier case-sensitive). So, you are telling the database that there is a table named "ads.MyTableName", with the "." included as part of the identifier.

Michael Xu <online.mxu@gmail.com> writes:

By default, pgsql accepts double quotes around schema's name in a query,
e.g. select * from "ads"."MyTableName". In our env, it throws
42P01:relation "ads.MyTableName" does not exist.

Works for me:

regression=# create schema ads;
CREATE SCHEMA
regression=# create table "ads"."MyTableName" (f1 int);
CREATE TABLE
regression=# select * from "ads"."MyTableName";
f1
----
(0 rows)

I'm wondering a bit about invisible white space in your schema
name; but without a self-contained test case we can do no more
than guess.

regards, tom lane

On 4/27/23 12:40, Michael Xu wrote:

Hi,

By default, pgsql accepts double quotes around schema's name in a query,
e.g. select * from "ads"."MyTableName". In our env, it throws
42P01:relation "ads.MyTableName" does not exist. It is okay if no double
quote around schema name, select * from ads."MyTableName".

Is there settings in pssql server or db level to change it back to its
default to allow double quotes around schema.

To repeat what I asked on SO:

https://stackoverflow.com/questions/76123696/postgres-double-quote-around-schema-name-cause-42p01relation-schemana-tablen

Works here.

1) Are you sure whatever client you are using is not doing
"ads.MyTableName"?

2) Not sure if this is a copy and paste thing but the quotes look like
typographical quotes, not straight quotes.

We're using PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

Thanks!

Michael

--
Adrian Klaver
adrian.klaver@aklaver.com

On Apr 27, 2023, at 12:40 PM, Michael Xu <online.mxu@gmail.com> wrote:


Hi,

By default, pgsql accepts double quotes around schema's name in a query, e.g. select * from "ads"."MyTableName". In our env, it throws 42P01:relation "ads.MyTableName" does not exist. It is okay if no double quote around schema name, select * from ads."MyTableName".

Is there settings in pssql server or db level to change it back to its default to allow double quotes around schema.

We're using PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

Thanks!

Michael

Any chance the actual name of the schema is not all lowercase ads?

On 4/27/23 17:58, Michael Xu wrote:

Please reply to list also.

Ccing list.

1) Are you sure whatever client you are using is not doing
"ads.MyTableName"?

Confirm, it is not ads.MyTableName.

1) What client(and it's version) are you using?

2) What I asked was whether the schema and table name where being quoted
together as "ads.MyTableName"?
Check the Postgres log to see what is actually hitting the database.

3) What is the database encoding?

We also ran the query in pgadmin against similar tables but under
default public schema, got the same error.
(*MyTableName* is a table name)

select * from "public"."MyTableName", the same error.
select * from public."MyTableName", works.

2) Not sure if this is a copy and paste thing but the quotes look like
typographical quotes, not straight quotes.

sorry for the confusion, it is copied/pasted from OneNote which may have
brought invalid quote, but the quotes used in pgadmin are correct.

On Thu, Apr 27, 2023 at 12:46 PM Adrian Klaver
<adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:

On 4/27/23 12:40, Michael Xu wrote:

Hi,

By default, pgsql accepts double quotes around schema's name in a

query,

e.g. select * from "ads"."MyTableName". In our env, it throws
42P01:relation "ads.MyTableName" does not exist. It is okay if no

double

quote around schema name, select * from ads."MyTableName".

Is there settings in pssql server or db level to change it back

to its

default to allow double quotes around schema.

To repeat what I asked on SO:

https://stackoverflow.com/questions/76123696/postgres-double-quote-around-schema-name-cause-42p01relation-schemana-tablen <https://stackoverflow.com/questions/76123696/postgres-double-quote-around-schema-name-cause-42p01relation-schemana-tablen&gt;

Works here.

1) Are you sure whatever client you are using is not doing
"ads.MyTableName"?

2) Not sure if this is a copy and paste thing but the quotes look like
typographical quotes, not straight quotes.

We're using PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc
(GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit

Thanks!

Michael

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com