From: Arni <Arni.Kromic@Bios-ICT.hr>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Problem with schemas, possibly oids?
Date: Fri, 9 Aug 2019 12:04:46 +0200
Message-ID: <7b555c9d-8d50-eced-53e8-b64bd35b883a@Bios-ICT.hr> (raw)
In-Reply-To: <CAKoxK+7v-nAxVBxbMD8J7ws1ehOAHgz1vaBTrOeKZNDESF-b1w@mail.gmail.com>
References: <3cabefc3-0713-7f5d-27c5-c0dab55cd8c6@Bios-ICT.hr>
<19010.1565274302@sss.pgh.pa.us>
<CAKoxK+7XXr4rSNV8NRzPpj+pHMdfwTtzjMRRBBK+HWkKArTPxA@mail.gmail.com>
<49e6cc59-5e8f-78c0-389d-f4c48e49f4ab@Bios-ICT.hr>
<CAKoxK+7v-nAxVBxbMD8J7ws1ehOAHgz1vaBTrOeKZNDESF-b1w@mail.gmail.com>
Pozdrav,
Arni
On 09/08/2019 11.53, Luca Ferrari wrote:
> On Fri, Aug 9, 2019 at 11:39 AM Arni <Arni.Kromic@bios-ict.hr> wrote:
>> On 08/08/2019 16.38, Luca Ferrari wrote:
>>> the first query is performed with quote_ident and that's the initial
>>> failure.
>> Oh now I get it, that was the reason why it wouldn't clone an existing
>> schema! My schema names are always quoted and that's why it fails. I
>> removed that quote_indent and it sort of works now (even though it still
>> fails trying to do something on a nonexistant column!) I wonder how
>> nobody noticed this before, while so many people discussed the script.
>> As I'm not so good with procedural SQL, I wouldn't have caught it too.
>> Many thanks!
> I didn't get this: your schema name requires double quotes because it
> has uppercase letters, and you fixed it removing quote_ident?
> Do you have the time and patient to reproduce every single query done
> by the script and see what exactly does not work? Because it seems to
> me that the schema is always quote_ident-ed.
Always quote_ident-ed... well, yes and no :)
The confusion seems to spring from the way quote_ident works! Let me
illustrate with two examples:
postgres=# select quote_ident('HR0000');
quote_ident
-------------
"HR0000"
(1 row)
postgres=# select quote_ident('hr0000');
quote_ident
-------------
hr0000
(1 row)
Apparently, postgres thinks the identifier should be quoted if it
contains capitals, and not if there are none (or any other characters it
finds offensive!) That inevitably leads to different behaviors for
different types of identifier names, which may introduce subtle bugs as
is the case here.
> Luca
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Arni.Kromic@Bios-ICT.hr, pgsql-admin@lists.postgresql.org
Subject: Re: Problem with schemas, possibly oids?
In-Reply-To: <7b555c9d-8d50-eced-53e8-b64bd35b883a@Bios-ICT.hr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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