
Selecting All Columns Except One in PostgreSQL - kawera
https://blog.jooq.org/2018/05/14/selecting-all-columns-except-one-in-postgresql/
======
paulmd
"do you really need a billiards table?"

[http://pbfcomics.com/comics/billiards-in-
heaven/](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.

~~~
slap_shot
> 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.

~~~
city41
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.

~~~
slap_shot
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.

~~~
RussianCow
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.

~~~
slap_shot
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.

~~~
mmt
> 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.

------
tejasmanohar
I suppose this is nice for adhoc queries in a repl, but I hope no one writes
it in an application. Heck, I don’t even use “select *” in code

~~~
terandle
I see this sentiment around a lot and can you explain why? I hope it’s more
than just an optimization thing. Getting rid of an extra column or two strikes
me as premature optimization in scenarios that are not “web scale”

~~~
scrollaway
The result format of select * is unpredictable unless you are guaranteed to
know your current schema. And if you know your current schema and want all
columns, for an ORM it's trivial to generate a list of all the columns you
want, so there's no need to use star.

For example: Let's say you have a piece of code that does: id, value =
query("select * from tbl").fetch_one(). Now that works great if you know you
have exactly the columns id and value in there. But let's say they swap places
(possible in mysql), or a third column is added, and you don't update your
code alongside it. Now that code breaks. Had you done "select id, value from
tbl", it wouldn't break.

PS: The sibling answers are also all correct; there's more reasons not to use
select * outside of the shell.

~~~
anderse
Do you mean your database driver doesn’t supply you with field names, that you
rely on order to map up data?

~~~
tejasmanohar
I guess I often rely on column order in Go, given Rows.Scan() [0]... so
`select _` would be scary since someone may ALTER the table.

Maybe that's the antipattern! But it's also a feature so I feel like you're
better off avoiding the _. Also, if someone adds additional big columns, you
may incur additional, unnecessary cost

[0]:
[https://golang.org/pkg/database/sql/#Rows.Scan](https://golang.org/pkg/database/sql/#Rows.Scan)

------
asah
* EXCEPT is valuable for VIEWs, e.g. VIEW of JOIN with multiple tables containing the same column. If those tables get new columns added, you want the view to automatically include those new columns.

Yes, obviously those new columns can have name conflicts and break the view
just the same -- but I'd much rather have compile-time errors than the scaling
nightmare of having to remember to explicitly name dozens of columns each
time, to make sure the new columns appear in the view.

Obviously, ORMs address these sorts of problems at the cost of portability
between programming languages and third party tools (that don't use your ORM).
Yuck.

------
jlgaddis
<rant type="off-topic">

In case the author happens to be reading the comments here:

Whatever you have done that messes with the scrolling and the ability to open
a link in a new tab is _not_ an "enhancement" at all (on Safari on iPad, at
least) and caused me to "bounce" from your web site much quicker than I would
have otherwise.

I wouldn't normally complain about this here on HN (apologies to my fellow
HN'ers) and would instead leave a comment directly on the site, but I wasn't
even able to do _that_ because of the UI hijacking that is taking place.

Great job making the web even more unusable!

</rant>

------
ianamartin
SELECT * EXCEPT syntax would be killer when you get into dynamic SQL and
especially when need to pivot on shit you don’t know about in advance.

~~~
rc_mob
Yes it should be a feature in all databases

------
jmount
The tasks of dropping columns and also of replacing one column value while
brining over all others are very desirable data transformations. Codd's
algebra had related features (project/extend and maybe an assign) and they
showed up in Tutorial D's EXTEND keyword. It is definitely something I think
is important enough to add to the rquery R SQL query generator I am working on
[https://winvector.github.io/rquery/](https://winvector.github.io/rquery/) (
drop_columns(), and extend_*se()'s overwriting behavior). And the R community
is already familiar with such piped relation style notation in the famous
dplyr package.

------
ereli1
is EXCEPT part of the SQL standard?

~~~
grzm
Yes, but not for this usage. It’s a set combiner like UNION and INTERSECT.
IIRC, Tutorial D has an operator like this use of EXCEPT for attributes called
ALL BUT.

