query to find indexes affected by locales upgrade does not work

Question

While upgrading a system from Debian stretch to Debian buster, I got a warning about collation changes with a link to https://wiki.postgresql.org/wiki/Locale_data_changes the database is currently running postgresql 9.6 from Debian stretch.

That page tells me I can find out which indexes in my database are affected by using the 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 
    collprovider IN ('d', 'c') 
    AND collname NOT IN ('C', 'POSIX');

However when I connect to my database and run the query I get.

ERROR: column "collprovider" does not exist LINE 4: WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', '...

How can I find out what if-any indexes in my databases are affected?

Answer 1

I got an answer in #postgresql on irc.

<Myon> PostgreSQL too old for that query
<Myon> just remove "collprovider and"

After removing "collprovider IN ('d', 'c') AND" from the query it ran successfully.