How to determine the collation of an index in PostgreSQL?

Question

In PostgreSQL 15, when an index is created with an implicit collation, the default collation used for the index is documented:

By default, the index uses the collation declared for the column to be indexed or the result collation of the expression to be indexed. Indexes with non-default collations can be useful for queries that involve expressions using non-default collations.

But we can change the collation of a column after an index has been created on it using the default collation (as described in the documentation). As per my understanding, changing the collation of a column should not change the collation of the already created index.

So, at any point in time, we should be able to query the actual collation of an index that may differ from the corresponding column collation.

How can we achieve that?

I made some tests that were not conclusive:

psql (15.3)
Type "help" for help.
postgres=# SELECT version();
 version 
------------------------------------------------------------------------------
 PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 12.2.0, 64-bit
(1 row)
postgres=# CREATE DATABASE test10
TEMPLATE = template0
LOCALE = "C"
ENCODING = 'UTF8';
CREATE DATABASE
postgres=# \c test10
You are now connected to database "test10" as user "postgres".
test10=# CREATE TABLE public.asset(
 asset_id uuid NOT NULL,
 name text NOT NULL,
 CONSTRAINT pk_asset PRIMARY KEY (asset_id)
);
CREATE TABLE
test10=# INSERT INTO public.asset VALUES
 ('100921c5-d506-4ff2-a8c4-c8250d3a2751', 'A8'),
 ('31278fae-f415-43b5-9445-4b0d8e1a7ae0', 'A9'),
 ('d8d9ff78-70d2-416c-96c8-fc40da2a9e8c', 'A10'),
 ('f0a0a526-338a-4eb6-911a-c6d077cb1e39', 'A11');
INSERT 0 4
test10=# CREATE INDEX idx_name_implicit_collation_c ON public.asset (name);
CREATE INDEX
test10=# \d public.asset
 Table "public.asset"
 Column | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 asset_id | uuid | | not null | 
 name | text | | not null | 
Indexes:
 "pk_asset" PRIMARY KEY, btree (asset_id)
 "idx_name_implicit_collation_c" btree (name)
test10=# SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid) 
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s 
 JOIN pg_collation c ON coll=c.oid
WHERE collname NOT IN ('C', 'POSIX');
 indrelid | indexrelid | collname | pg_get_indexdef 
----------+-------------------------------+----------+-------------------------------------------------------------------------------
 asset | idx_name_implicit_collation_c | default | CREATE INDEX idx_name_implicit_collation_c ON public.asset USING btree (name)
(1 row)
test10=# CREATE COLLATION "natural" (provider = icu, locale = 'und-u-kn-true');
CREATE COLLATION
test10=# ALTER TABLE public.asset ALTER COLUMN name TYPE text COLLATE "natural";
ALTER TABLE
test10=# CREATE INDEX idx_name_implicit_collation_natural ON public.asset (name);
CREATE INDEX
test10=# \d public.asset
 Table "public.asset"
 Column | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 asset_id | uuid | | not null | 
 name | text | natural | not null | 
Indexes:
 "pk_asset" PRIMARY KEY, btree (asset_id)
 "idx_name_implicit_collation_c" btree (name)
 "idx_name_implicit_collation_natural" btree (name)
test10=# SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid) 
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s 
 JOIN pg_collation c ON coll=c.oid
WHERE collname NOT IN ('C', 'POSIX');
 indrelid | indexrelid | collname | pg_get_indexdef 
----------+-------------------------------------+----------+-------------------------------------------------------------------------------------
 asset | idx_name_implicit_collation_c | natural | CREATE INDEX idx_name_implicit_collation_c ON public.asset USING btree (name)
 asset | idx_name_implicit_collation_natural | natural | CREATE INDEX idx_name_implicit_collation_natural ON public.asset USING btree (name)
(2 rows)
test10=# ALTER TABLE public.asset ALTER COLUMN name TYPE text;
ALTER TABLE
test10=# \d public.asset
 Table "public.asset"
 Column | Type | Collation | Nullable | Default 
----------+------+-----------+----------+---------
 asset_id | uuid | | not null | 
 name | text | | not null | 
Indexes:
 "pk_asset" PRIMARY KEY, btree (asset_id)
 "idx_name_implicit_collation_c" btree (name)
 "idx_name_implicit_collation_natural" btree (name)
test10=# SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid) 
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s 
 JOIN pg_collation c ON coll=c.oid
WHERE collname NOT IN ('C', 'POSIX');
 indrelid | indexrelid | collname | pg_get_indexdef 
----------+-------------------------------------+----------+-------------------------------------------------------------------------------------
 asset | idx_name_implicit_collation_c | default | CREATE INDEX idx_name_implicit_collation_c ON public.asset USING btree (name)
 asset | idx_name_implicit_collation_natural | default | CREATE INDEX idx_name_implicit_collation_natural ON public.asset USING btree (name)
(2 rows)

In my tests, the following query

SELECT indrelid::regclass::text, indexrelid::regclass::text, collname, pg_get_indexdef(indexrelid) 
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, generate_subscripts(indcollation, 1) g(i)) s 
 JOIN pg_collation c ON coll=c.oid
WHERE collname NOT IN ('C', 'POSIX');

always returns a collname which is the current collation of the column, instead of the expected collation of the indexes, as shown below:

 indexrelid | collname 
-------------------------------------+----------
 idx_name_implicit_collation_c | default
 idx_name_implicit_collation_natural | natural

I suspect this is a bug.

How can we query the actual collation of an index?

• Daniel Vérité: Suggestion: execute SET client_min_messages TO debug4; before doing the ALTER TABLE... ALTER COLUMN.... The debug output will mention that the index gets rebuilt.
• soliz: So my understanding was wrong, I found the relevant documentation in postgresql.org/docs/15/sql-altertable.html (see SET DATA TYPE ): "Indexes and simple table constraints involving the column will be automatically converted to use the new column type [...]"

Answer 1

The index is rebuilt when you change the column collation:

CREATE TABLE colltab (t text COLLATE "ar-EG-x-icu");
CREATE INDEX collind ON colltab (t);
SELECT oid, relfilenode FROM pg_class WHERE relname = 'collind';
 oid │ relfilenode 
═══════╪═════════════
 81350 │ 81350
(1 row)
ALTER TABLE colltab ALTER t TYPE text COLLATE "C";
SELECT oid, relfilenode FROM pg_class WHERE relname = 'collind';
 oid │ relfilenode 
═══════╪═════════════
 81351 │ 81351
(1 row)

The change in the name of the file behind the index (81350 ⇒ 81351) means that the index has been rebuilt.