Feedback-ID: i76984098:Fastmail
Message-ID: <30eb1a5a-794c-4ee6-b305-9bdaaa9e56ff@aklaver.com>
Date: Tue, 18 Jun 2024 07:44:23 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: DROP COLLATION vs pg_collation question
To: Karsten Hilbert <Karsten.Hilbert@gmx.net>,
 pgsql-general@lists.postgresql.org
References: <Zmi-gljgKdjQmY8B@hermes.hilbert.loc>
 <d3384b9f1e870eda5f6a01b008209398b18b36d4.camel@cybertec.at>
 <ZmnzY3uqoiBrFOoO@hermes.hilbert.loc>
 <26f717988471c4d672cce8c495a2c8e68446486a.camel@cybertec.at>
 <ZmyjJA0jGU5TL64I@hermes.hilbert.loc>
 <56da532b44bc02a7648796041ffa5da0a85c06df.camel@cybertec.at>
 <Zm6wB76XFH2Za1SC@hermes.hilbert.loc>
 <740fb9086af71f864ddb526cad1e55888385bedd.camel@cybertec.at>
 <3031360.1718570329@sss.pgh.pa.us> <ZnGFgH9oKiynIbY6@hermes.hilbert.loc>
 <ZnGMh-MkQ6aG5tF2@hermes.hilbert.loc>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <ZnGMh-MkQ6aG5tF2@hermes.hilbert.loc>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/30eb1a5a-794c-4ee6-b305-9bdaaa9e56ff%40aklaver.com>
Precedence: bulk

On 6/18/24 06:32, Karsten Hilbert wrote:
> Am Tue, Jun 18, 2024 at 03:02:56PM +0200 schrieb Karsten Hilbert:
> 
>> I see, and since any database can be used as a template for
>> more databases, which can be create with an encoding
>> different from the template,
> 
> Proving myself wrong:
> 
> 
> root@hermes:~/tmp# sudo -u postgres psql -e -f /tmp/db.sql
> Output format is wrapped.
> drop database if exists new_tmpl;
> DROP DATABASE
> create database new_tmpl with encoding = 'utf8';
> CREATE DATABASE
> You are now connected to database "new_tmpl" as user "postgres".
> select current_database();
>   current_database
> ------------------
>   new_tmpl
> (1 row)
> 
> drop database if exists new_latin1;
> psql:/tmp/db.sql:8: HINWEIS:  Datenbank »new_latin1« existiert nicht, wird übersprungen
> DROP DATABASE
> create database new_latin1 with template = new_tmpl encoding = 'latin1' locale = 'de_DE@latin1';
> psql:/tmp/db.sql:9: FEHLER:  neue Kodierung (LATIN1) ist inkompatibel mit der Kodierung der Template-Datenbank (UTF8)
> TIP:  Verwenden Sie die gleiche Kodierung wie die Template-Datenbank oder verwenden Sie template0 als Template.

That is covered here:

https://www.postgresql.org/docs/16/manage-ag-templatedbs.html

"Another common reason for copying template0 instead of template1 is 
that new encoding and locale settings can be specified when copying 
template0, whereas a copy of template1 must use the same settings it 
does. This is because template1 might contain encoding-specific or 
locale-specific data, while template0 is known not to."

Substitute <db_name other then template0> for template1 and you can get 
the error you received.

> 
> Karsten
> --
> GPG  40BE 5B0E C98E 1713 AFA6  5BC0 3BEA AC80 7D4F C89B
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com