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

[Adrian Klaver]

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 
mailto:adrian.kla...@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
 


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.kla...@aklaver.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Rob Sargent]

> On Apr 27, 2023, at 12:40 PM, 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. 
> 
> 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?

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

[Adrian Klaver]

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.kla...@aklaver.com

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

[Christophe Pettus]

> On Apr 27, 2023, at 12:40, Michael Xu  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.

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

[Tom Lane]

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