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 1pQ7JR-0002AD-8g for pgsql-general@arkaria.postgresql.org; Thu, 09 Feb 2023 13:50:09 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pQ7JP-0007Hk-Oc for pgsql-general@arkaria.postgresql.org; Thu, 09 Feb 2023 13:50:07 +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 1pQ7JP-0007Ha-C5 for pgsql-general@lists.postgresql.org; Thu, 09 Feb 2023 13:50:07 +0000 Received: from mail1.dalibo.net ([51.159.93.128] helo=mail.dalibo.com) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pQ7JI-00043c-HX for pgsql-general@lists.postgresql.org; Thu, 09 Feb 2023 13:50:06 +0000 Received: from karst (larco.ioguix.net [78.202.0.6]) by mail.dalibo.com (Postfix) with ESMTPSA id D760B1F828; Thu, 9 Feb 2023 14:49:47 +0100 (CET) DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=dalibo.com; s=a; t=1675950588; bh=qU/QZQB3Xgk3IauNppFRvDlK+i8OghqtAVvWPJiQxG0=; h=Date:From:To:Cc:Subject:In-Reply-To:References:From; b=NTyKhbmW+pERPMc9bD6uVvpt/XyMSvMEgVpWt5bNuAmQiKWtwZbvnaKS6Nkr4ajJI SXuYVfK9+2zcW6oc+N2faeStQOkjGh4m0mhuQCQWci9GNxKirQ6MqNYuhXdqw/68+w yCDKpuxDlZNY3c8TWDUs7lLcDFLCgoVAhAmX+Zz8= Date: Thu, 9 Feb 2023 14:49:47 +0100 From: Jehan-Guillaume de Rorthais To: Peter Geoghegan Cc: Tom Lane , Dionisis Kontominas , pgsql-general@lists.postgresql.org Subject: Re: Question regarding UTF-8 data and "C" collation on definition of field of table Message-ID: <20230209144947.1dfad6c0@karst> In-Reply-To: References: <2550995.1675639480@sss.pgh.pa.us> <2556580.1675642741@sss.pgh.pa.us> Organization: Dalibo MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Sun, 5 Feb 2023 17:14:44 -0800 Peter Geoghegan wrote: ... > The OP should see the Postgres ICU docs for hints on how to use these > facilities to make a custom collation that matches whatever their > requirements are: > > https://www.postgresql.org/docs/current/collation.html#COLLATION-MANAGING As you are talking about ICU customization to match whatever the requirement we want, we were wondering if this would be that easy with ICU to build/create such custom and odd collation (and not just move numbers after latin)? Even being able to order letter by letter? For the record, I helped on an issue last week to sort data using the ebcdic order. ICU was just a pain, especially with this buggy and annoying bug when sorting numbers after letters[1] and the fact that it takes whatever we feed it with without complaining for badly formed or impossible collation. We just gave up. One of our recommendation was to write a glibc collation file, built/installed it using localedef and "import" it in PostgreSQL using pg_import_system_collations(). The customer actually did it and it works like a charm. Regards, [1] remember ? https://www.postgresql.org/message-id/flat/20200903105727.064665ce%40firost#0a6e89e58eec7679391c829231a7b3ea