> you can have "listX" and then write a non-list into that same name; statically typed systems would not allow this
Well, that depends. In most SQL databases there are many cases where supplying the wrong type of value will implicitly convert to the expected type, often in unexpected ways that can result in subtle bugs.
PostgreSQL is very very good about really never doing this, and also a scalar vs. list is pretty much a PostgreSQL case since most other relational DBs dont have a native ARRAY type. I think you're mostly thinking of MySQL that has some int/string coercion cases which are to be clear bad, but not as egregious as "any arbitrary type goes right in with no checking whatsoever.
as mentioned, SQLite breaks all these rules and I think SQLite is very wrong on this.
Well, that depends. In most SQL databases there are many cases where supplying the wrong type of value will implicitly convert to the expected type, often in unexpected ways that can result in subtle bugs.