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?
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0