pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Postgres query doesn't accept double quote around schema name in query statement
6+ messages / 5 participants
[nested] [flat]

* Postgres query doesn't accept double quote around schema name in query statement
@ 2023-04-27 19:40 Michael Xu <online.mxu@gmail.com>
  2023-04-27 19:44 ` Re: Postgres query doesn't accept double quote around schema name in query statement Christophe Pettus <xof@thebuild.com>
  2023-04-27 19:45 ` Re: Postgres query doesn't accept double quote around schema name in query statement Tom Lane <tgl@sss.pgh.pa.us>
  2023-04-27 19:46 ` Re: Postgres query doesn't accept double quote around schema name in query statement Adrian Klaver <adrian.klaver@aklaver.com>
  2023-04-27 21:02 ` Re: Postgres query doesn't accept double quote around schema name in query statement Rob Sargent <robjsargent@gmail.com>
  0 siblings, 4 replies; 6+ messages in thread

From: Michael Xu @ 2023-04-27 19:40 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org

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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Postgres query doesn't accept double quote around schema name in query statement
  2023-04-27 19:40 Postgres query doesn't accept double quote around schema name in query statement Michael Xu <online.mxu@gmail.com>
@ 2023-04-27 19:44 ` Christophe Pettus <xof@thebuild.com>
  3 siblings, 0 replies; 6+ messages in thread

From: Christophe Pettus @ 2023-04-27 19:44 UTC (permalink / raw)
  To: Michael Xu <online.mxu@gmail.com>; +Cc: pgsql-general@lists.postgresql.org



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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Postgres query doesn't accept double quote around schema name in query statement
  2023-04-27 19:40 Postgres query doesn't accept double quote around schema name in query statement Michael Xu <online.mxu@gmail.com>
@ 2023-04-27 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
  3 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2023-04-27 19:45 UTC (permalink / raw)
  To: Michael Xu <online.mxu@gmail.com>; +Cc: pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Postgres query doesn't accept double quote around schema name in query statement
  2023-04-27 19:40 Postgres query doesn't accept double quote around schema name in query statement Michael Xu <online.mxu@gmail.com>
@ 2023-04-27 19:46 ` Adrian Klaver <adrian.klaver@aklaver.com>
  2023-04-28 04:27   ` Re: Postgres query doesn't accept double quote around schema name in query statement Adrian Klaver <adrian.klaver@aklaver.com>
  3 siblings, 1 reply; 6+ messages in thread

From: Adrian Klaver @ 2023-04-27 19:46 UTC (permalink / raw)
  To: Michael Xu <online.mxu@gmail.com>; pgsql-general@lists.postgresql.org

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-42p01rel...

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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Postgres query doesn't accept double quote around schema name in query statement
  2023-04-27 19:40 Postgres query doesn't accept double quote around schema name in query statement Michael Xu <online.mxu@gmail.com>
  2023-04-27 19:46 ` Re: Postgres query doesn't accept double quote around schema name in query statement Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-04-28 04:27   ` Adrian Klaver <adrian.klaver@aklaver.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Adrian Klaver @ 2023-04-28 04:27 UTC (permalink / raw)
  To: Michael Xu <online.mxu@gmail.com>; +Cc: pgsql-general

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-42p01rel... <https://stackoverflow.com/questions/76123696/postgres-double-quote-around-schema-name-cause-42p01rel...;
> 
>     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






^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Postgres query doesn't accept double quote around schema name in query statement
  2023-04-27 19:40 Postgres query doesn't accept double quote around schema name in query statement Michael Xu <online.mxu@gmail.com>
@ 2023-04-27 21:02 ` Rob Sargent <robjsargent@gmail.com>
  3 siblings, 0 replies; 6+ messages in thread

From: Rob Sargent @ 2023-04-27 21:02 UTC (permalink / raw)
  To: Michael Xu <online.mxu@gmail.com>; +Cc: pgsql-general@lists.postgresql.org



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

^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2023-04-28 04:27 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-27 19:40 Postgres query doesn't accept double quote around schema name in query statement Michael Xu <online.mxu@gmail.com>
2023-04-27 19:44 ` Christophe Pettus <xof@thebuild.com>
2023-04-27 19:45 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-27 19:46 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-28 04:27   ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-27 21:02 ` Rob Sargent <robjsargent@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox