http://pbfcomics.com/comics/billiards-in-heaven/
I mean I guess it's a cute bit of syntactic sugar but are you really going to die if you have to manually specify the columns you want? That's a vastly safer/less brittle practice.
No, it's a basic piece of functionality that should have existed a long time ago.
I'm reminded of a quote, I think from pg that goes like "programming languages teach you to not want what they can't give you."
It's absolutely true. I see few language features met with comments like yours and I just wonder, why? Why can a feature only be added if the alternative is that we're "going to die?"
As somebody who does SQL for a majority of their work, this feature is so overdue. I briefly used RethinkDB from 2013-2014 and RQL supported this functionality. What a treat it would be to be able to do this on Postgres.
If skipping EXCEPT means say the postgres team can focus that much more energy on bugs and stability, then that's a good trade off in my eyes.
JSONB data type, ALTER SYSTEM statement for changing config values, ability to refresh materialized views without blocking reads, dynamic registration/start/stop of background worker processes, Logical Decoding API, GiN index improvements, Linux huge page support, database cache reloading via pg_prewarm UPSERT, row level security, TABLESAMPLE, CUBE/ROLLUP, GROUPING SETS, and new BRIN index[113] Parallel query support, PostgreSQL foreign data wrapper (FDW) improvements with sort/join pushdown, multiple synchronous standbys, faster vacuuming of large table Logical replication, declarative table partitioning, improved query parallelism
We'd all love for software to be bug-free and stable, but features have to be added.
I agree with you: all features have to have a cost and value. The market decides what features are valuable, and they get built and supported. SELECT EXCEPT is not one that the market values yet. And that's fine.
I'm curious as to what you envision as "the market" in the context of free software, or Postgres in particular, since I don't expect it's the cliche of people literally deciding which product to pay for among competing ones.
A couple possibilities came to mind, but, having no intimate familiarity with any major project like this, I didn't want to parade mere assumptions as multiple choice.
It also depends on what kind of software we are talking about. A trendy mobile app is very different from a critical database.
I personally prefer software that’s more conservative in its approach to new features. It’s becoming harder to find software that has this philosophy but not yet impossible.
Adding columns can break EXCEPT queries. This is one of those situations where adding a feature can break a lot of other stuff after the fact.
If new columns are created they should also be in the result set.
If that breaks your code except was not the right call because you specifically needed a subset of columns, not the inverse.
Your laziness and abuse of the inverse is the problem, not the feature it self.
Not a Ruby guy but the Ruby equivalent of something like:
rejected [:updated_at, :inserted_at]
to_select =
Enum.reject(fields, fn field ->
field in rejected
end) to_select = fields - [:created_at, :updated_at] cols = list(table.columns)
cols.remove(table.columns['updated_at'])
cols.remove(table.columns['inserted_at'])
query = select(cols).select_from(table)
With sqlalchemy.orm you could also define a Bundle, see https://docs.sqlalchemy.org/en/latest/orm/query.html?highlig...
http://docs.sqlalchemy.org/en/latest/orm/loading_columns.htm...
This is both at the 'object' level, and at the query level for example at the query level: from sqlalchemy.orm import defer, undefer
query = session.query(Book) query = query.options(defer('summary')) query = query.options(undefer('excerpt')) query.all()
You can also group columns for deferral/undeferral.
I use this loads.
I think if you strip columns for performance reasons for one specific query it would still be better to use Bundles or the equivalent loose select (i.e. session.query(Book.id, Book.title)) instead of query-local-deferred columns, because then you will notice if a code change leads to a down-stream access of an unwanted column for some reason.
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.
CREATE TABLE safe_users AS
SELECT *
EXCEPT users.pii
FROM users
;
"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."
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.