Apologies if this is a basic question, but I'd really like to clarify the exact meaning of what a Cartesian coordinate system is.
Heuristically, is it correct to say that a Cartesian coordinate system is "Euclidean geometry with coordinates" and so by definition the geometry that we are studying has to be Euclidean (i.e. Euclidean metric, parallel postulate, etc. must hold) in order to use this coordinate system. Also, more mathematically, is it correct to say that a Cartesian coordinate system is a special kind of mapping between points of Euclidean space $\mathbb{E}^{n}$ and real space $\mathbb{R}^{n}$. Such a coordinate map makes the intrinsic spatial distance between two points in $\mathbb{E}^{n}$ be directly reflected by the ‘numerical distance’ between their numerical coordinates in $\mathbb{R}^{n}$.
If this is correct then I can understand why Cartesian coordinate maps cannot generally be used for patches on manifolds as the geometry will generally be non-Euclidean. As such the homeomorphisms (coordinate maps) from the patch on the manifold to $\mathbb{R}^{n}$ will necessarily deform the patch, consequently intrinsic distances between points, angles, etc. will not preserved and so Cartesian coordinates cannot be used.
Best Answer
In the mathematical literature, the term "Cartesian coordinates" is used most frequently to refer simply to the standard coordinate functions on $\mathbb R^n$, namely the functions $x^1,\dots,x^n\colon \mathbb R^n\to \mathbb R$ defined by $x^i(a^1,\dots,a^n) = a^i$. Somewhat less frequently, I've also seen the term used to refer to any coordinate system on $\mathbb R^n$ obtained by composing the standard coordinates with a rigid motion, which can also be characterized as those coordinates for which the standard coordinate vectors $\partial/\partial x^1,\dots,\partial/\partial x^n$ are orthonormal.
The point is that it only makes sense to talk about "Cartesian coordinates" on $\mathbb R^n$ itself, or on an open subset of $\mathbb R^n$. On an arbitrary smooth manifold, the term has no meaning. Of course, on any smooth manifold $M$, each point has a neighborhood $U$ on which we can find a smooth coordinate chart, and such a chart allows us to identify each point $p\in U$ with its coordinate values $(x^1(p),\dots,x^n(p))\in\mathbb R^n$, and thus to temporarily identify $U$ with an open subset of $\mathbb R^n$; but we would not call these coordinates "Cartesian coordinates on $M$."
If your manifold $M$ is endowed with a Riemannian metric $g$, then there is more that can be said. For example, one could ask whether it's possible to find a coordinate chart in which the given Riemannian metric has the same coordinate expression as the Euclidean metric: $g= (dx^1)^2 + \dots + (dx^n)^2$. If this is the case, then geodesics and distances within this coordinate neighborhood are given by the same formulas as they are in Euclidean space; but that might not hold true elsewhere on the manifold. I think this might be the question you're getting at in your last paragraph, although I would not call these "Cartesian coordinates" because they don't have an open subset of $\mathbb R^n$ as their domain. Off the top of my head, I don't know of any standard nomenclature for such coordinates, but it wouldn't be inconsistent to call them "Euclidean coordinates" or "flat coordinates."
It's a basic theorem of Riemannian geometry that it is impossible to find such coordinates unless the curvature tensor of the Riemannian metric is identically zero on the open subset $U$. You'll find a proof of this fact in virtually any book on Riemannian geometry, such as my Riemannian Manifolds: An Introduction to Curvature (Theorem 7.3). If you want a treatment that doesn't use so much of the machinery of Riemannian manifolds, my Introduction to Smooth Manifolds has a proof that it's impossible to find Euclidean coordinates for the ordinary $2$-sphere in $\mathbb R^3$ (Proposition 13.19 and Corollary 13.20).