News for hackers. Stuff that matters.
Re:Selecting All Columns Except One in PostgreSQL by paulmd on Saturday May 19, 2018 @10:00AM (#17107923)
"do you really need a billiards table?"

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.

Re:Selecting All Columns Except One in PostgreSQL by slap_shot on Saturday May 19, 2018 @11:04AM (#17108148)
> 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.

Re:Selecting All Columns Except One in PostgreSQL by city41 on Saturday May 19, 2018 @12:06PM (#17108440)
I'm in the opposite camp. Even small features are very expensive. You need to support them for the life of the product. You need to think about them every time a new feature, bug fix, anything comes along. I prefer products that have smaller feature sets and those features are rock solid and very well thought out than just throwing every possibility in because you can.

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.

Re:Selecting All Columns Except One in PostgreSQL by slap_shot on Saturday May 19, 2018 @12:24PM (#17108522)
Except that's not how real software is built. Real software has to add new features to remain competitive. Just look at a few of the features for Postgres 9.4 to 10:

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.

Re:Selecting All Columns Except One in PostgreSQL by city41 on Saturday May 19, 2018 @01:06PM (#17108705)
For sure, that is the reality we live in. Doesn’t mean it’s a good thing though. There’s even been some push back to this trend, such as Apple reconsidering how they do release cycles due to iOS becoming so buggy.

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.

Re:Selecting All Columns Except One in PostgreSQL by RussianCow on Saturday May 19, 2018 @01:03PM (#17108685)
Nobody is arguing that products shouldn't have features. But there is a real cost to each feature that you add, and so you have to weigh the tradeoffs before blindly building everything anyone asks for. Selecting all columns but one seems like it would bring much less value than JSONB.
Re:Selecting All Columns Except One in PostgreSQL by slap_shot on Saturday May 19, 2018 @02:15PM (#17109015)
The parent comment said he preferred software with less features, and favored bug fixing and stability over feature X. Feature X could be swapped with any request in Postgres's backlog.

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.

Re:Selecting All Columns Except One in PostgreSQL by mmt on Saturday May 19, 2018 @10:19PM (#17110817)
> The market decides what features are valuable, and they get built and supported.

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.

Re:Selecting All Columns Except One in PostgreSQL by paulmd on Sunday May 20, 2018 @03:28AM (#17111668)
What happens if you ALTER TABLE ADD COLUMNs? The fact that you can add Oracle triggers that fail after the fact is one of the things I hate most.

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.

Re:Selecting All Columns Except One in PostgreSQL by asah on Sunday May 20, 2018 @09:50AM (#17112655)
That house left the barn with SELECT *, which is sensitive to the addition of new columns.
Re:Selecting All Columns Except One in PostgreSQL by lugg on Saturday May 19, 2018 @11:30PM (#17111036)
The feature would be useful when you want all columns except one or two specific columns.

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.

Re:Selecting All Columns Except One in PostgreSQL by mistermann on Wednesday May 23, 2018 @01:17AM (#17131607)
Thank you.....this mentality of don't add any new feature that might cause a problem, on a hacking website of all places, is bizarre.
Re:Selecting All Columns Except One in PostgreSQL by AznHisoka on Saturday May 19, 2018 @10:09AM (#17107955)
Actually what I would really find useful is the ability to select all columns except 2. In rails I sometimes dont need to see created_at and updated_at
Re:Selecting All Columns Except One in PostgreSQL by da_chicken on Saturday May 19, 2018 @10:39AM (#17108052)
Right, but why do you want to force the query planner to have to do an enumeration? And what happens if a column is added to the table? 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? Does your RDBMS or the SQL standard even guarantee the order of columns returned by a *? The fact that you don't know for sure should be reason enough to give you pause.

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.

Re:Selecting All Columns Except One in PostgreSQL by mistermann on Wednesday May 23, 2018 @01:23AM (#17131630)
> but why do you want to force the query planner to have to do an enumeration?

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.

Re:Selecting All Columns Except One in PostgreSQL by teej on Saturday May 19, 2018 @10:43AM (#17108073)

    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.
Re:Selecting All Columns Except One in PostgreSQL by jlgaddis on Saturday May 19, 2018 @05:21PM (#17109800)
I suppose it could be useful for keeping your "breach notification team" busy, after somebody (for whatever reason) adds a "users.hiv_positive" (boolean) column or some such and that column ends up being included somewhere you didn't intend it to be.

"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."

Re:Selecting All Columns Except One in PostgreSQL by _asummers on Saturday May 19, 2018 @10:29AM (#17108009)
If you had a data structure with your fields, you could do that much more explicitly.

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)
Re:Selecting All Columns Except One in PostgreSQL by blattimwind on Saturday May 19, 2018 @10:50AM (#17108094)
In SQLAlchemy it could look something like this

  cols = list(table.columns)
  cols.remove(table.columns['updated_at'])
  cols.remove(table.columns['inserted_at'])
  query = select(cols).select_from(table)
(You can abbreviate table.columns with table.c)

With sqlalchemy.orm you could also define a Bundle, see https://docs.sqlalchemy.org/en/latest/orm/query.html?highlig...

Re:Selecting All Columns Except One in PostgreSQL by djd20 on Saturday May 19, 2018 @12:21PM (#17108511)
Much simpler usint the ORM - you can defer columns:

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.

Re:Selecting All Columns Except One in PostgreSQL by blattimwind on Saturday May 19, 2018 @01:08PM (#17108721)
Interesting, I knew about defer and used it in the past for pretty much the purpose the first example illustrates, but didn't remember/realize it could be used on a per-query basis.

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.

Re:Selecting All Columns Except One in PostgreSQL by Bishonen88 on Saturday May 19, 2018 @02:10PM (#17108994)
what sql does this produce though? I can imagine that there'd be a hit on performance
Re:Selecting All Columns Except One in PostgreSQL by blattimwind on Saturday May 19, 2018 @03:04PM (#17109223)
Why would you imagine that? This compiles to SELECT col1,...,coln FROM table ...;
Re:Selecting All Columns Except One in PostgreSQL by anderse on Saturday May 19, 2018 @10:41AM (#17108063)
Probably something like:

  to_select = fields - [:created_at, :updated_at]
Though there’s always a slight uncomfort in not being 100% sure the array elements aren’t strings, so might throw in a cast here just to be sure.
Re:Selecting All Columns Except One in PostgreSQL by manquer on Saturday May 19, 2018 @10:02PM (#17110767)
You may not have all the column names for example when you are working with a crosstab query