ALTER_COLLATION(7pg15)

💅 ALTER COLLATION (7pg15) 💅

okay so bestie, welcome to the ALTER COLLATION page — that's the PostgreSQL 15 docs, section 7, and honestly? it's giving "small page, big feelings" 🎀 because collations are the whole vibe of how your text sorts and compares, and when their version drifts you get warnings and corrupt indexes and we're simply not doing that 🙈

---

✨ NAME ✨

ALTER_COLLATION — change the definition of a collation

like, glow-up a collation's definition 💅

---

🌸 SYNOPSIS 🌸

ALTER COLLATION name REFRESH VERSION

ALTER COLLATION name RENAME TO new_name
ALTER COLLATION name OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER COLLATION name SET SCHEMA new_schema

three whole moods, pick your fighter 🫶

---

🦋 DESCRIPTION 🦋

ALTER COLLATION changes the definition of a collation. that's it. that's the sentence. iconic brevity ✨

The rules, no cap: you must own the collation to use ALTER COLLATION. if you're trying to alter the owner, you also have to be a direct or indirect member of the new owning role, and that role has to have CREATE privilege on the collation's schema. (these restrictions are literally just there to enforce that altering the owner doesn't let you do anything you couldn't already do by dropping and recreating the collation — so, no sneaky shortcuts 💀)

but fr fr, a superuser can alter ownership of any collation anyway 👑 so there's always a main character.

---

💖 PARAMETERS 💖

• name — the name of an existing collation. optionally schema-qualified, because we love a fully-qualified moment 🎀
• new_name — the new name of the collation. fresh identity, same energy 💫
• new_owner — the new owner of the collation. handing over the aux cord 🔥
• new_schema — the new schema for the collation. moving house 🏠
• REFRESH VERSION — update the collation's version. see Notes below 👇 (spoiler: it's the whole plot)

---

🧚 NOTES 🧚

okay so here's the tea ☕ when a collation object gets created, the provider-specific version of the collation is recorded in the system catalog. then, whenever the collation is actually used, the current version gets checked against that recorded version — and if there's a mismatch, you get a warning. for example:

WARNING:  collation "xx-x-icu" has version mismatch
DETAIL:  The collation in the database was created using version 1.2.3.4, but the operating system provides version 2.3.4.5.
HINT:  Rebuild all objects affected by this collation and run ALTER COLLATION pg_catalog."xx-x-icu" REFRESH VERSION, or build PostgreSQL with the right library version.

bestie. read that DETAIL again. 😭 that's a whole situation.

why this is scary, lowkey: a change in collation definitions can lead to corrupt indexes and other problems, because the database system relies on stored objects having a certain sort order. generally, this should be avoided 🙅‍♀️ — but it can happen in totally legitimate circumstances, such as when you upgrade the operating system to a new major version, or when you use pg_upgrade to upgrade to server binaries linked with a newer version of ICU. so like, not your fault, but still your problem 💀

when that happens? all objects depending on the collation should be rebuilt, for example using REINDEX. once that's done, you can refresh the collation version with the command ALTER COLLATION ... REFRESH VERSION. this updates the system catalog to record the current collation version and makes the warning go away 🎉

⚠️ but heads up — it does not actually check whether all affected objects have been rebuilt correctly. it just trusts you. so, y'know, be trustworthy 🫶

where does version info even come from?

• collations provided by libc: version information is recorded on systems using the GNU C library (most Linux systems), FreeBSD and Windows 🌈
• collations provided by ICU: the version information is provided by the ICU library and is available on all platforms 🌍

---

🍓 Note (yes, a whole note)

• when using the GNU C library for collations, the C library's version is used as a proxy for the collation version. many Linux distributions change collation definitions only when upgrading the C library, but this approach is imperfect — because maintainers are free to back-port newer collation definitions to older C library releases 🙈 iykyk
• when using Windows for collations, version information is only available for collations defined with BCP 47 language tags such as en-US. so like, specific much? ✨

---

for the database default collation there's an analogous command, ALTER DATABASE ... REFRESH COLLATION VERSION 💜 — same energy, different scope.

the following query can be used to identify all collations in the current database that need to be refreshed and the objects that depend on them. run it, she eats every time 👑

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)
  ORDER BY 1, 2;

---

🌟 EXAMPLES 🌟

to rename the collation de_DE to german:

ALTER COLLATION "de_DE" RENAME TO german;

she said "ich heiße jetzt german" 💅

to change the owner of the collation en_US to joe:

ALTER COLLATION "en_US" OWNER TO joe;

joe's collation now. slay 🫶

---

⭐ COMPATIBILITY ⭐

there is no ALTER COLLATION statement in the SQL standard. we're built different. no cap 🔥

---

💀 SEE ALSO 💀

• CREATE COLLATION (CREATE_COLLATION(7))
• DROP COLLATION (DROP_COLLATION(7))

---

and that's the page, bestie 🎀✨ this was PostgreSQL 15.19 — documented on the 2026 date stamp, straight from the official ALTER COLLATION(7) man page. go refresh those versions responsibly, mwah 💖🦋