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

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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com