Contents
pg_import_system_collations()
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.
Usage
pg_import_system_collations (schemaregnamespace) →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
- PostgreSQL 16
- Windows support added (commit bf03cfd1)
- PostgreSQL 10
- added (commit aa17c06f)
Examples
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');
pg_import_system_collations
-----------------------------
14
(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"); CREATE TABLE
0 will be returned if no new collations are available for import:
postgres=# SELECT pg_import_system_collations('pg_catalog');
pg_import_system_collations
-----------------------------
0
(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.
References
- PostgreSQL documentation: Collation Management Functions
