There seems to be a problem with the order of creation and using a collation in a Postgresql dump. I defined a collation for my database a long time ago. This is used for exactly one column in a table.

With this constellation everything has worked so far, the dump contained the creation of the collation before it was used in the table.

...
CREATE COLLATION public.de_pos (provider = icu, locale = 'de-DE-u-kn-true');
...
CREATE TABLE public.kalkulation_position (
    pos character varying COLLATE public.de_pos,
...

Now I have defined a DB view that also uses the column that contains the collation. When I now create a dump, there is an error during the restore due to a non-existent collation. The view (with the column that uses the collation) should be created before the collation is created. This leads to the error.

...
CREATE OR REPLACE VIEW public."mengenübersicht_global" AS
SELECT
    NULL::character varying COLLATE public.de_pos AS lv_position,
...
CREATE COLLATION public.de_pos (provider = icu, locale = 'de-DE-u-kn-true');
...
CREATE TABLE public.kalkulation_position (
    pos character varying COLLATE public.de_pos,
...

Now I can certainly do without the collation in the view, but isn’t this a fundamental problem and may have to be solved differently?

Here is some additional info as requested:

1. with

   ON_ERROR_STOP=off

   The view is created and populated and the whole restore process finished successfully. Otherwise, the process stops with the following error:

    ERROR:  collation "public.de_pos" for encoding "UTF8" does not exist
    ZEILE 12:     NULL::character varying COLLATE public.de_pos AS lv_posi...
   

2. versions:

   source system: pg_dump (PostgreSQL) 16.3 (Debian 16.3-1.pgdg110+1)
   target system: psql (PostgreSQL) 16.2 (Homebrew)

3. commands:

   pg_dump --host=localhost --port=5432 --username=steel42 --file=steel42prod_2024-09-05.sql --clean --no-owner --superuser=steel42 --if-exists --role=steel42 --exclude-table-data=document_storage --verbose steel42prod
   
   psql --echo-all --host=localhost --port=5432 --username=steel42 --set ON_ERROR_STOP=on steel42 <steel42prod_2024-09-05.sql