Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pwcIU-0008Ld-Fv for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 05:23:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pwcIS-00088H-Ee for pgsql-general@arkaria.postgresql.org; Wed, 10 May 2023 05:23:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pwcIR-000887-GO for pgsql-general@lists.postgresql.org; Wed, 10 May 2023 05:23:28 +0000 Received: from wnew3-smtp.messagingengine.com ([64.147.123.17]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pwcIM-001i1v-75 for pgsql-general@lists.postgresql.org; Wed, 10 May 2023 05:23:26 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailnew.west.internal (Postfix) with ESMTP id 4F4162B0695C; Wed, 10 May 2023 01:23:17 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute4.internal (MEProxy); Wed, 10 May 2023 01:23:17 -0400 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:sender:subject:subject:to:to:x-me-proxy :x-me-proxy:x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t= 1683696196; x=1683703396; bh=I+FGbfYvfps8hk6uw3+qdfNddSqP9wZWrS0 hdBO+fTI=; b=SvaTwFiEprEohOwyxjL52ZxBFrOB0Kccou0GYgZY3q25obIksdE q57npz1E17eEwxMrbJALHwVz7Vs4Qge/HosGaYxLxjqMhPK8OZWfQciJviN92qcN b+gCfwZWWAQ+ktGb0GGp1u/y7ZM9Qhsd4oPq2VHAHyoqtP8ijtNOwnBfXvrDy0t6 zR1M/pH37XtMytc2TleXvb+rYmB/aKwLk79Mw0Oooq4wbrySrIFNzOGdFyeMOqo5 TO6lAJqbLV4pFbLBgpVXf64925l0RnczZ7BJ4f8Z/uusMlEgVo1mrbsq5hHmjlN2 CEQNR6nUlOoyDhO9oYee1XMp/bBeCUkPMpQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrfeegvddgleehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtfeejnecuhfhrohhmpefrvght vghrucfgihhsvghnthhrrghuthcuoehpvghtvghrrdgvihhsvghnthhrrghuthesvghnth gvrhhprhhishgvuggsrdgtohhmqeenucggtffrrghtthgvrhhnpefhjeduueelfeehuedt udefteejveegieeiueejueehfeektddtueeiieeitefhveenucevlhhushhtvghrufhiii gvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpehpvghtvghrrdgvihhsvghnthhrrghu thesvghnthgvrhhprhhishgvuggsrdgtohhm X-ME-Proxy: Feedback-ID: i131946ab:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 10 May 2023 01:23:15 -0400 (EDT) Message-ID: Date: Wed, 10 May 2023 07:23:13 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Thunderbird/102.10.1 Subject: Re: ICU, locale and collation question Content-Language: en-US To: Kirk Wolak Cc: Oscar Carlberg , Laurenz Albe , pgsql-general@lists.postgresql.org References: <2a1de395-c69a-3dae-4459-dbdb633df637@fortnox.se> <4210aaef-06ff-628e-a2c8-a1a4bf28d278@fortnox.se> From: Peter Eisentraut In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 10.05.23 07:02, Kirk Wolak wrote: > On Tue, May 9, 2023 at 11:24 AM Peter Eisentraut > > wrote: > > On 09.05.23 08:54, Oscar Carlberg wrote: > > Our initdb setup would then look like this for compatibility; > > -E 'UTF-8' > > --locale-provider=icu > > --icu-locale=sv-SE-x-icu > > --lc_monetary=sv_SE.UTF-8 > > --lc-numeric=sv_SE.UTF-8 > > --lc-time=sv_SE.UTF-8 > > --lc-messages=en_US.UTF-8 > > > > Should we still provide createdb with --lc-collate=C and > --lc-ctype=C, > > or should we set those to sv_SE.UTF-8 as well? > > You should set those to something other than C.  It doesn't matter much > what exactly, so what you have there is fine. > > Setting it to C would for example affect the ability of the text search > functionality to detect words containing non-ASCII characters. > > Doesn't searching LIKE 'abc%'  behave much better for C than others. > This was the driving force for choosing C for us. > [EXPLAIN made it clear that it was range bound until 'abd'] For that use, I would recommend making an index specifically on the tables you need, instead of switching the whole database. Also, if you are using the ICU provider for the database, then setting lc_collation=C wouldn't even affect LIKE optimization, because the ICU locale would be used.