From: Michael Downey <mdowney@esri.com>
To: "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
Subject: PostgreSQL implicitly double-quoting identifier name with umlaut
Thread-Topic: PostgreSQL implicitly double-quoting identifier name with umlaut
Thread-Index: AdsGLnHVnGdm0oISQoqxFlMAEyZJDA==
Date: Fri, 13 Sep 2024 23:06:11 +0000
Message-ID: 
 <BY3PR05MB7985A889E17333E35AF6B110DB652@BY3PR05MB7985.namprd05.prod.outlook.com>
Accept-Language: en-US
Content-Language: en-US
x-ms-exchange-antispam-messagedata-chunkcount: 1
x-ms-exchange-antispam-messagedata-0: 
 =?iso-8859-1?Q?eSfIcUpvGS+m3IH9II/hsRNaLwyyGajbLL4hPgnuxuJjTYQu7NntnM+qhj?=
 =?iso-8859-1?Q?D9Ue7zBIqe5sMVXHeayneD2Av+jEfduW1W8LQ4FOdxJVti/PolAeTGayEd?=
 =?iso-8859-1?Q?bBK6YeDc2ckrVhDE5VVtT80knH2AFt/r7cP9QJ7GHFvSRb/iu4zQdjkPMP?=
 =?iso-8859-1?Q?zqvyAvwCs49dpIxpQApAkM0DGcNL7HGd2Lyj/sxFOOhO21uQ3N82Gp1ssS?=
 =?iso-8859-1?Q?UIlpLO8qo9jlYrm6zMHCuup3tKJPx10HKBsUnExsv0WwT+edVzufpbk5gX?=
 =?iso-8859-1?Q?/5D4WsPHkOJh28EFA+jTHPNx11QcLdUe+0aPIqZpN3OuTeqeWbuCKaQ3ak?=
 =?iso-8859-1?Q?ka8gQauYrDEbFPbtMMqR3GjVj//dloayi6Wa2WKm2TEvMSMNgrLzvz0A+P?=
 =?iso-8859-1?Q?ppkoDXbcZFO2ufDDUdV4T92aajqQJYIeFQMmZj8yplxgA6uGvCpMI8JkTm?=
 =?iso-8859-1?Q?H8YGg4gBHWoysk4vE+eYMYl9ERRiVjHSBcqGb1BLwibB+nrHbJ3eifJqe9?=
 =?iso-8859-1?Q?qvqfKYLrS4MVaIygZ2BLWO312/zulKofM4Ei6/oMpHSiC7NZ/h4gf/ExCp?=
 =?iso-8859-1?Q?IZpxWRuwP6oNYCchhquBhVP4kXUBoW4qlmlW1Oo80ni/KqXpy1KIWzPsuR?=
 =?iso-8859-1?Q?wVv+tg0/hz3DOk2iz4axaxK0hvVigmV90YZdKCtk7GBelQBK3gzCUZkX3n?=
 =?iso-8859-1?Q?8Vi2LcmnU7dB+zoefR9CwKDS34ITnDKr2MQsioW8qi5Hiveikkw4NSIJcG?=
 =?iso-8859-1?Q?tsZnl9trEza4xSWATzA3Se/zQv3xGgHUlxTr7bKOpdWBgFXVZtwHxRxVqO?=
 =?iso-8859-1?Q?nBh6YCvXAsSg+/SAfuzf8kcMbsZw3dCiy+iw8uTwKAMy7cIs2ziL0JTT5F?=
 =?iso-8859-1?Q?lpiMB275KauzUllVycBbuYcnVoVt0i0Eih6tkgRNikvs1BUKjky1vE0QLg?=
 =?iso-8859-1?Q?VHL2pQ0jFnuSFgtYKANDNDAs44jgzqUsEQbrp2XvuR6a3jZRhznDn6rkHH?=
 =?iso-8859-1?Q?05u1uBYTVI44JEv48MUge0/B3EcQBQnTP+Q3kOJjy62Mw84ZBApj+W7YZS?=
 =?iso-8859-1?Q?NMioDWFqjAbYy4n6+rYdcQK+r1q4B8aJmcj5de0oWdXZaHiL39vqpCSUba?=
 =?iso-8859-1?Q?DeZSTmth8yuaxzZvCSgyQgPiVTqGjYY76jpe886mKmfW6eWvLhtPZ8iupO?=
 =?iso-8859-1?Q?Ml/UCOwI9IwoaWuduVD298Ix8gVnSMtINhQhxA3ZeHyMQw8l6//xcwaJzh?=
 =?iso-8859-1?Q?ugN9XIwiCjevUdIBpSdO8ir2WS7rmhJLg+LbxlBoeeREIbqhj+0ny4EnlW?=
 =?iso-8859-1?Q?/sPF1CvSb51BcrF2WTAaKzbDwAaMLsF/bVnJzFK682yAapkuXNMz8WsXON?=
 =?iso-8859-1?Q?/OzZmwhBXPxGyiu+1T59aj4+7uQs2g3yE2NGL/XmcYC2Cnmi1S5BC5kl96?=
 =?iso-8859-1?Q?nXj5CHP2DMJWo/JdU1VZLNccx4VRbKyePWkIRKfnp91VOeFw0m91FtBYfm?=
 =?iso-8859-1?Q?18jdjePfseXYKr3BvUEuakgh1WoPtgh6umfHPe9Eo2Jl9PjSIFpzWYPOCZ?=
 =?iso-8859-1?Q?7db+5QdwPV8O7OSA3CcLXB+q8gscEJzDa8dbhwCOr5bEJHeEg9KGpAseMF?=
 =?iso-8859-1?Q?352IC5z/fNbnQ=3D?=
Content-Type: multipart/alternative;
	boundary="_000_BY3PR05MB7985A889E17333E35AF6B110DB652BY3PR05MB7985namp_"
MIME-Version: 1.0
X-OriginatorOrg: esri.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-AuthSource: BY3PR05MB7985.namprd05.prod.outlook.com
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 0ac93cba-52f8-4a07-abe5-08dcd448a892
X-MS-Exchange-CrossTenant-originalarrivaltime: 13 Sep 2024 23:06:11.0620
 (UTC)
X-MS-Exchange-CrossTenant-fromentityheader: Hosted
X-MS-Exchange-CrossTenant-id: aee6e3c9-711e-4c7c-bd27-04f2307db20d
X-MS-Exchange-CrossTenant-mailboxtype: HOSTED
X-MS-Exchange-CrossTenant-userprincipalname: 
 +iKHVfR5lH2n5xXOi4Kc8ryK2179ULk8sCVNqsQE361GPwBFw9+cwGJ2TsF4khMz7QeKPntx0oGaeOai3uKJyg==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CO1PR05MB8040
X-Proofpoint-GUID: aUJhljH_XQAB49-skjjAXwfJRSSIhICl
X-Proofpoint-ORIG-GUID: aUJhljH_XQAB49-skjjAXwfJRSSIhICl
X-Proofpoint-Virus-Version: vendor=baseguard
 engine=ICAP:2.0.293,Aquarius:18.0.1039,Hydra:6.0.680,FMLib:17.12.60.29
 definitions=2024-09-06_09,2024-09-06_01,2024-09-02_01
X-Proofpoint-Spam-Details: rule=outbound_notspam policy=outbound score=0
 impostorscore=0
 priorityscore=1501 suspectscore=0 clxscore=1011 adultscore=0 spamscore=0
 bulkscore=0 phishscore=0 lowpriorityscore=0 malwarescore=0 mlxlogscore=899
 mlxscore=0 classifier=spam adjust=0 reason=mlx scancount=1
 engine=8.19.0-2408220000 definitions=main-2409130164
List-Id: <pgsql-sql.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-sql@lists.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-sql>
Archived-At: 
 <https://www.postgresql.org/message-id/BY3PR05MB7985A889E17333E35AF6B110DB652%40BY3PR05MB7985.namprd05.prod.outlook.com>
Precedence: bulk

--_000_BY3PR05MB7985A889E17333E35AF6B110DB652BY3PR05MB7985namp_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

PostgreSQL implicitly double-quoting identifier name with umlaut

I an understand this behavior, just trying to find documentation to confirm=
 it.

Server and client encoding are UTF8.

One of our internal users, using our tools, added a column called =D6rtscha=
ft. We anticipated it would be folded to lower case.
So we inserted our metadata for the column in our metadata with the name =
=F6rtschaft. With the system query for metadata, we
ended up seeing query mismatches involving this column as we found the actu=
al column name is =D6rtschaft
in the database.

I looked at the "SQL" of the table in pgadmin and saw this:

"CREATE TABLE IF NOT EXISTS map.mytable2
(
    oid bigint,
    name1 smallint,
    "=D6rtschaft" integer
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS map.mytable2
    OWNER to map;"

I thought our code might be adding the double quotes inadvertently, but the=
 statement in the Postgres log had the alter table statement with
=D6rtschaft without double quotes.

After experimenting in SQL, I was able to find that in I could reproduce th=
is in psql and Pgadmin sql.

Ex
In SQL:
alter table map.mytable2 add column =D6rtschaft7 integer;

PGADMIN interface output (I could not find the exact query statement to pro=
duce this)
CREATE TABLE IF NOT EXISTS map.mytable2
(
    oid bigint,
    name1 smallint,
    "=D6rtschaft" integer,
    ortschaft2 integer,
    "(tm)rtschaft3" integer,
    "=D6rtschaft4" integer,
    "=C3-rtschaft5" integer,
    "=C3-rtschaft6" integer,
    "=D6rtschaft7" integer
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS map.mytable2
    OWNER to map;

This behavior seems like an exception to the lower-case folding behavior ex=
pectation.

Is this expected?
Is this documented someplace?

Thanks,
Michael

--_000_BY3PR05MB7985A889E17333E35AF6B110DB652BY3PR05MB7985namp_
Content-Type: text/html; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr=
osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:=
//www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Diso-8859-=
1">
<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
	{font-family:"Cambria Math";
	panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
	{font-family:Aptos;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	font-size:11.0pt;
	font-family:"Aptos",sans-serif;
	mso-ligatures:standardcontextual;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Aptos",sans-serif;
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;
	font-size:11.0pt;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
	{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3D"EN-US" link=3D"#467886" vlink=3D"#96607D" style=3D"word-wrap:=
break-word">
<div class=3D"WordSection1">
<p class=3D"MsoNormal">PostgreSQL implicitly double-quoting identifier name=
 with umlaut<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I an understand this behavior, just trying to find d=
ocumentation to confirm it.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Server and client encoding are UTF8.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">One of our internal users, using our tools, added a =
column called =D6rtschaft. We anticipated it would be folded to lower case.=
<o:p></o:p></p>
<p class=3D"MsoNormal">So we inserted our metadata for the column in our me=
tadata with the name =F6rtschaft. With the system query for metadata, we
<o:p></o:p></p>
<p class=3D"MsoNormal">ended up seeing query mismatches involving this colu=
mn as we found the actual column name is =D6rtschaft<o:p></o:p></p>
<p class=3D"MsoNormal">in the database. <o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I looked at the &#8220;SQL&#8221; of the table in pg=
admin and saw this:<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">&#8220;CREATE TABLE IF NOT EXISTS map.mytable2<o:p><=
/o:p></p>
<p class=3D"MsoNormal">(<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; oid bigint,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; name1 smallint,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;=D6rtschaft&quot; integer<o=
:p></o:p></p>
<p class=3D"MsoNormal">)<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">TABLESPACE pg_default;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">ALTER TABLE IF EXISTS map.mytable2<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; OWNER to map;&#8221;<o:p></o:p></=
p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">I thought our code might be adding the double quotes=
 inadvertently, but the statement in the Postgres log had the alter table s=
tatement with
<o:p></o:p></p>
<p class=3D"MsoNormal">=D6rtschaft without double quotes.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">After experimenting in SQL, I was able to find that =
in I could reproduce this in psql and Pgadmin sql.
<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Ex<o:p></o:p></p>
<p class=3D"MsoNormal">In SQL:<o:p></o:p></p>
<p class=3D"MsoNormal">alter table map.mytable2 add column =D6rtschaft7 int=
eger;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">PGADMIN interface output (I could not find the exact=
 query statement to produce this)<o:p></o:p></p>
<p class=3D"MsoNormal">CREATE TABLE IF NOT EXISTS map.mytable2<o:p></o:p></=
p>
<p class=3D"MsoNormal">(<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; oid bigint,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; name1 smallint,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;=D6rtschaft&quot; integer,<=
o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; ortschaft2 integer,<o:p></o:p></p=
>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;&#8482;rtschaft3&quot; inte=
ger,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;=D6rtschaft4&quot; integer,=
<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;=C3&#8211;rtschaft5&quot; i=
nteger,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;=C3&#8211;rtschaft6&quot; i=
nteger,<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; &quot;=D6rtschaft7&quot; integer<=
o:p></o:p></p>
<p class=3D"MsoNormal">)<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">TABLESPACE pg_default;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">ALTER TABLE IF EXISTS map.mytable2<o:p></o:p></p>
<p class=3D"MsoNormal">&nbsp;&nbsp;&nbsp; OWNER to map;<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">This behavior seems like an exception to the lower-c=
ase folding behavior expectation.<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Is this expected?<o:p></o:p></p>
<p class=3D"MsoNormal">Is this documented someplace?<o:p></o:p></p>
<p class=3D"MsoNormal"><o:p>&nbsp;</o:p></p>
<p class=3D"MsoNormal">Thanks,<o:p></o:p></p>
<p class=3D"MsoNormal">Michael<o:p></o:p></p>
</div>
</body>
</html>

--_000_BY3PR05MB7985A889E17333E35AF6B110DB652BY3PR05MB7985namp_--