lc_collate

Definition PG 15 manual

Reports the locale in which sorting of textual data is done. See Section 24.1 for more information. This value is determined when a database is created.

How it works

Reported the current database’s collation locale through PG15. It was an internal read-only value, not a configurable GUC, and PostgreSQL 16 removed it from pg_settings.

The collation locale is selected when a database is created and can differ between databases in one cluster. It affects ordering and comparison semantics; changing the server configuration cannot rewrite existing indexes or database locale metadata.

On PG16 and later, monitoring and applications should read the current database row in pg_database and its locale-provider-specific metadata, and use pg_collation for individual collation objects. Test collation-version changes separately from this removed reporter.

Operational considerations

Trying to remove or set lc_collate in configuration even though it was always internal/read-only.

Assuming one cluster-wide value applies to every database.

Changing operating-system or ICU locale data without checking collation versions and rebuilding affected indexes.

Comparing text behavior across providers using only a locale name.

Workload guidance

OLAP: Record per-database provider and locale beside analytical exports. A desired collation change requires a deliberate database/object migration and possible REINDEX, not a GUC edit.

OLTP: Not tunable. Replace SHOW/current_setting readers with a version-aware pg_database query, and regression-test indexed ordering and uniqueness when moving databases between locale providers or versions.

SMALL: Use the database-creation default only when it is intentional. Preserve locale metadata in inventory and backups so a rebuild does not silently choose different ordering rules.