Why does providing an explicit initializer on an extern inside a function not override extern?

According to C++ Primer, we can provide an initializer on a variable defined as extern, but doing so overrides the extern.

Only partially.

Providing an initializer forces the declaration to be a definition (except in the case of some static members of classes). It thus partially counteracts the effect of extern, which usually suppresses definition. However, extern can have other effects, and the initializer doesn't nullify those.

It is also stated in the book that providing an initializer on an extern inside a function is an error.

Yes. At block scope, extern is used to force a declaration to have external or internal linkage (rather than declaring a local variable). However, the language prohibits defining such a variable in a local scope. You can only declare it there, and provide a matching definition at namespace scope. If you provide an initializer, then you're trying to define the variable there, which is not allowed.

If providing an initializer on an extern outside any function can override the extern, why can providing it on an extern inside a function not?

As I explained, this premise is false. The initializer, in both cases, makes the declaration into a definition. In both cases, it does not override the fact that extern affects the linkage of the entity being declared.

I also don't understand why someone would want to both obtain a declaration adding the extern keyword and provide an initializer. Is it not the same as defining a variable and providing an initializer on it? If it is not, why?

When we use extern together with an initializer, it is usually because we are trying to define a const object with external linkage. See below:

// At global scope:
const int c1 = 42;  // definition with internal linkage
extern const int c2;  // declaration with external linkage
extern const int c3 = 42;  // definition with external linkage