A function for adding collations to the system catalog

pg_import_system_collations() is a system function for adding collations to the system catalog from those available in the operating system.

pg_import_system_collations() was added in PostgreSQL 10.


pg_import_system_collations ( schema regnamespace ) → integer

pg_import_system_collations() is typically used after installing new locales at operating system.

The provided schema is usually pg_catalog, but may also be a user-defined schema.

The number of new collation objects added is returned.

Change history


Determine that a required locale is missing:

postgres=# CREATE TABLE foo (id INT NOT NULL, val TEXT COLLATE "de_DE.utf8");
ERROR:  collation "de_DE.utf8" for encoding "UTF8" does not exist

Install missing locale(s), here on a Debian-based system:

# apt-get install -y  language-pack-de
Generating locales (this might take a while)...
  de_AT.UTF-8... done
  de_BE.UTF-8... done
  de_CH.UTF-8... done
  de_DE.UTF-8... done
  de_IT.UTF-8... done
  de_LI.UTF-8... done
  de_LU.UTF-8... done
Generation complete.

Execute pg_import_system_collations():

postgres=# SELECT pg_import_system_collations('pg_catalog');
(1 row)

The table can now be created with the desired collation:

postgres=# CREATE TABLE foo (id INT NOT NULL, val TEXT COLLATE "de_DE.utf8");

0 will be returned if no new collations are available for import:

postgres=# SELECT pg_import_system_collations('pg_catalog');
(1 row)

A target schema must always be explicitly specified:

postgres=# SELECT pg_import_system_collations();
ERROR:  function pg_import_system_collations() does not exist
LINE 1: SELECT pg_import_system_collations();
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.


Collation, Locale and character set handling, System function

See also

pg_collation, pg_collation_actual_version(), pg_database_collation_actual_version()