For AI agents: llms.txt is a short guide that sends you to the rules your problem needs, and rules.json holds every rule of this playbook as JSON.

How to do it

A text index stores rows in the order its collation library sets. The PostgreSQL docs warn that changed collation definitions can corrupt indexes, such as after an upgrade to a new major OS version. Then lookups miss rows and unique indexes let duplicates in. PostgreSQL warns of a collation version mismatch, but keeps running. After each upgrade, run the queries below, REINDEX every object they list, then run ALTER COLLATION ... REFRESH VERSION. For the database default, run ALTER DATABASE ... REFRESH COLLATION VERSION. A new database can use the builtin provider with C.UTF-8 or PG_UNICODE_FAST, which compare by code point. Sort for people with a locale collation in the query instead, as Sort and compare text by locale says.

Examples

Using these examples: they show one way to follow this rule. Keep the behaviour and the check, not the syntax. Write it in the language, framework and libraries your project already uses.

SQL example

-- Objects that depend on a collation whose version changed, expect no rows
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS collation,
       pg_describe_object(classid, objid, objsubid) AS object
FROM pg_depend d JOIN pg_collation c ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid);
-- Databases whose default collation changed, expect no rows
SELECT datname FROM pg_database WHERE datcollversion <> pg_database_collation_actual_version(oid);

Shell example

# Rebuild, then record the new version
psql shop -c 'REINDEX DATABASE CONCURRENTLY shop'
psql shop -c 'ALTER DATABASE shop REFRESH COLLATION VERSION'

How to check

After each operating system or database upgrade, both queries below return no rows on every database.

Sources

• PostgreSQL ALTER COLLATION
• PostgreSQL locale support

Related rules

• Sort and compare text by locale in Website