Collation is used to sort strings (text), for example by alphabetic order, whether or not case matters, how to deal with letters that have accents etc. COLLATE “C” tells the database not to use collation at all. One might use this if they were designing a database to hold data in different languages. Technically, COLLATE “C” will use byte order to drive text comparisons.
The first answer on https://dba.stackexchange.com/questions/94887/what-is-the-impact-of-lc-ctype-on-a-postgresql-database provides a good example of the differences between using COLLATE “C” vs. COLLATE “fr_FR” which uses the French localization.