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 1pVC1i-0001yK-CJ for pgsql-admin@arkaria.postgresql.org; Thu, 23 Feb 2023 13:52:50 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pVC1h-00005m-48 for pgsql-admin@arkaria.postgresql.org; Thu, 23 Feb 2023 13:52:49 +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 1pVC1g-00005c-P6 for pgsql-admin@lists.postgresql.org; Thu, 23 Feb 2023 13:52:48 +0000 Received: from mail.ibu.de ([136.243.18.157] helo=ibu.de) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pVC1e-0001jU-CI for pgsql-admin@lists.postgresql.org; Thu, 23 Feb 2023 13:52:48 +0000 Received: from mail.ibu.de (localhost [127.0.0.1]) by ibu.de (8.16.1/8.16.1) with ESMTPS id 31NDqip3039245 (version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NO); Thu, 23 Feb 2023 14:52:44 +0100 (CET) (envelope-from np@ibu.de) Received: (from np@localhost) by mail.ibu.de (8.16.1/8.16.1/Submit) id 31NDqio7039244; Thu, 23 Feb 2023 14:52:44 +0100 (CET) (envelope-from np@ibu.de) X-Authentication-Warning: mail.your-server.de: np set sender to np@ibu.de using -f Date: Thu, 23 Feb 2023 14:52:44 +0100 From: Norbert Poellmann To: Holger Jakobs Cc: pgsql-admin@lists.postgresql.org Subject: Re: PostgreSQL, change encoding preserving data Message-ID: References: <2af47a85-0004-fcb3-80c3-467e0468d07f@jakobs.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <2af47a85-0004-fcb3-80c3-467e0468d07f@jakobs.com> "From: np@ibu.de" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Hi, I think, it should be possible without changing template0/template1, simply by using template0 and -E parameter to (command line) createdb. Examples: createdb -T template0 -E latin1 foo createdb -T template0 -E sql_ascii foo2 createdb -T template0 -E EUC_CN foo3 createdb -T template0 -E utf8 foo4 np ->psql -l |grep foo foo | np | LATIN1 | C | C | foo2 | np | SQL_ASCII | C | C | foo3 | np | EUC_CN | C | C | foo4 | np | UTF8 | C | C | cheers Norbert -- Norbert Poellmann EDV-Beratung email : np@ibu.de Severinstrasse 5 telefon: 089 38469995 81541 Muenchen, Germany telefon: 0179 2133436 On Thu, Feb 23, 2023 at 02:18:54PM +0100, Holger Jakobs wrote: > Am 23.02.23 um 14:00 schrieb info@denisgottardello.it: > > > > Hi, my database is setup with encoding= win1252. In order to go to > > utf8 I run the followed query: > > > > update pg_database set encoding= pg_char_to_encoding('utf8') where > > datname= 'Handyman' > > > > The problem is that, the database has already got data with special > > characters such as "атий". After having executed the query to change > > the encoding to utf8, I can't read the special caracters. > > Is there a way to change the encoding and to migtrate the data? > > > > Best regards. > > > > > > +39.347.4070897 > > > > http://www.labcsp.com > > > > http://www.denisgottardello.it > > > > GMT+1 > > > > Skype: mrdebug > > > This might help: > https://www.shubhamdipt.com/blog/how-to-change-postgresql-database-encoding-to-utf8/ > > Kind Regards, > > Holger > > > -- > Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012 >