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 1pOqL1-0002o7-Ht for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 01:30:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pOqL0-0006PF-F4 for pgsql-general@arkaria.postgresql.org; Mon, 06 Feb 2023 01:30:30 +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 1pOqL0-0006P6-4P for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 01:30:30 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pOqKx-0001y0-V4 for pgsql-general@lists.postgresql.org; Mon, 06 Feb 2023 01:30:29 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 3161UPsG2564483; Sun, 5 Feb 2023 20:30:25 -0500 From: Tom Lane To: Dionisis Kontominas cc: pgsql-general@lists.postgresql.org Subject: Re: Question regarding UTF-8 data and "C" collation on definition of field of table In-reply-to: References: <2550995.1675639480@sss.pgh.pa.us> <2556580.1675642741@sss.pgh.pa.us> Comments: In-reply-to Dionisis Kontominas message dated "Mon, 06 Feb 2023 01:48:15 +0100" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <2564481.1675647025.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Sun, 05 Feb 2023 20:30:25 -0500 Message-ID: <2564482.1675647025@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Dionisis Kontominas writes: > 1. Regarding the different languages in the same column, that is nor= mal > if the column is a UTF-8 one, i.e. should be able to hold for example > English, Greek and Chinese characters. In this case what is the best > approach to define the collation and lctype of the column? Either C > or ucs_basic maybe or something else or it does not matter, the chara= cters > would be stored correctly as long as the database is UTF8 encoding? The characters will be stored correctly, yes. Collation only affects sort order. Ctype affects some other functions like upper/lowercase folding and what is considered a "letter" in regexps. If you use "C" for lc_ctype then only ASCII letters will be folded or recognized as letters. > 2. Is there a real difference between C and ucs_basic collation/lctyp= e? No, at least not in Postgres. regards, tom lane