Thread: 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



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

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>
> 
>     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