Same as anything else in software: I considered the pros and cons, and made a choice to go a particular route.
> And what happens if a column is added to the table?
It will show up in the query, as noted in the documentation.
> What if you use positional indexing instead of column names in your application (it performs better in many systems) and the column order is changed?
Then you will have a bug. Don't use both at the same time.
> Does your RDBMS or the SQL standard even guarantee the order of columns returned by a ?
Read the documentation. If order is not guaranteed, do not assume it will always be the same.
> Syntax like this is fine for ad hoc queries, but not in a query that's part of an application.
It's my money, I'll* decide what belongs in my application and what doesn't.
> You're writing those queries once to execute hundreds, thousands, millions of times.
You are speculating.
All of the above arguments could be made about 50% of functions existing in software, yet life goes on. The world isn't that fragile.
CREATE TABLE safe_users AS
SELECT *
EXCEPT users.pii
FROM users
;
Lots and lots of uses for this feature. I often wish I had it in Redshift."Root cause analysis: The {database administrators|programmers} were too lazy to explicitly define the column names they wanted, resulting in the unintended disclosure of HIV positive status of $bignum individuals."
Syntax like this is fine for ad hoc queries, but not in a query that's part of an application. You're writing those queries once to execute hundreds, thousands, millions of times. Making your query concrete is probably wise.