I write Lisp professionally and love s-expressions, but the comparison with ALTER TABLE's grammar is pretty unfair. Postgres can detect many invalid ALTER TABLE formulations by checking the grammar, whereas in Lisp it's much easier to have grammatically-correct but semantically-wrong formulations.
A major saving grace here (which was not covered in the section on macros) is that you can write clever macros that do appropriate checking at macroexpansion time, not runtime. This lets you use Lisp to write checkers for your Lisp code, which gives you a Turing-complete language you already know to do the checking instead of the (formally and practically) more limited checking of a grammar.
I think of specialized language syntax on a spectrum similar to "Dynamic types <-> Static types". Very general syntax like S-expressions provides you maximum flexibility, but the minimum amount of guidance on semantics and protection against mistakes, along with the opportunity for nicer, more-detailed error messages.
:= (ALTER-TABLE (:IF-EXISTS? :ONLY?) :*? +)
| ...
:= ...
I think Coalton [1] is a good example of this. The language is embedded as S-expressions in Common Lisp, but you get Rust-like error messages, showing exact source lines (with line numbers) and embedded underlines showing the location of the offending code.
With that said, what is true is that if you're using Common-Lisp-style macros and you're manipulating S-expressions programmatically, lexical information may get thrown out. This is not unlike doing a bunch of string manipulation in an ORM to generate SQL, which will have similarly poor error messages.