CoCs on their own solve exactly nothing, they're just a tool that's supposed to enable project leadership to solve certain issues more effectively. Whether they actually serve that function is an open question since the practice is still fairly new. I haven't seen any meaningful evidence one way or the other (feel free to provide some).
While I'm sympathetic to the argument that sufficiently large projects should, on average, benefit from some degree of formalization of behavioral norms (whether you call it a code of conduct or something else), the recent trend of insisting (often abusively) that every open source project, however small, should adopt this practice is silly, annoying and has obviously caused a backlash in some parts of the open source community, hence this thread.
"in tech". The CoC we're discussing here exists - as CoC - for Millenia.
An even older example would be the https://en.wikipedia.org/wiki/Hippocratic_Oath, which might have been more appropriate as an example of a "code of work conduct", but "I swear by Apollo the Healer, by Asclepius, by Hygieia, by Panacea, and by all the gods and goddesses" might rub some people the wrong way, including, in this case, sqlite's author.
I'm specifically talking about open source communities. I think the context of these social structures (spontaneous organization, lack of traditional coercive motivators (money, enforcement mechanisms, social pressure), the fact that many people are pseudonymous, the fact that most projects are effectively tyrannies, etc.) makes them sufficiently distinct from the classical examples you list to make any direct comparison very difficult.
From an administrative side: being able to point explicitly to rules that are being broken to shut down an endless "why is X a big deal" and "but he's doing Y and he's not banned".
From a user side: being able to see where the lines are, and being able to see an explicit reason for a punishment. Being able to point out when others are breaking explicit rules. Being able to see and point out hypocrisy.
This does require admins to actually follow the CoCs and to act on them, which I think a lot of people in this thread are actually bitching about. Everyone who says "CoCs don't do anything by themselves" are right, but seatbelts also don't do anything unless they're plugged in, but we should still have 'em in cars.