8 ms·

Selecting All Columns Except One in PostgreSQL

64 points by kawera 8y ago

paulmd 8y ago
"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.
- AznHisoka 8y ago
  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
  - _asummers 8y ago
    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)
    - anderse 8y ago
      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.
    - blattimwind 8y ago
      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?highlight=bundle#sqlalchemy.orm.query.Bundle https://docs.sqlalchemy.org/en/latest/orm/query.html?highlig...
      - djd20 8y ago
        Much simpler usint the ORM - you can defer columns: http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#deferred-column-loading 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.
        blattimwind 8y ago
        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.
      - Bishonen88 8y ago
        what sql does this produce though? I can imagine that there'd be a hit on performance
        blattimwind 8y ago
        Why would you imagine that? This compiles to SELECT col1,...,coln FROM table ...;
  - da_chicken 8y ago
    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.
    - teej 8y ago
      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.
      - jlgaddis 8y ago
        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."
    - mistermann 8y ago
      > 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.
- deleted 8y ago
  [deleted]
- slap_shot 8y ago
  > 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 8y ago
    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 8y ago
      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.
- manquer 8y ago
  You may not have all the column names for example when you are working with a crosstab query
- lugg 8y ago
  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.
  - mistermann 8y ago
    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.
tejasmanohar 8y ago
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 8y ago
  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”
  - blattimwind 8y ago
    Writing "SELECT *" makes it less clear what columns are selected exactly. Explicitly writing what columns shall be returned is more explicit and clear.
    - MichaelApproved 8y ago
      That's helpful from a code review perspective but it's the performance hit you get with a * select that is the bigger reason to avoid it.
      - dahdum 8y ago
        The column name conflicts can cause no end of trouble later on as well.
  - scrollaway 8y ago
    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.
ereli1 8y ago
is EXCEPT part of the SQL standard?
- grzm 8y ago
  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.
- blattimwind 8y ago
  Not in a column specification of a SELECT query.
ianamartin 8y ago
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 8y ago
  Yes it should be a feature in all databases
jmount 8y ago
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.
jlgaddis 8y ago
<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>
asah 8y ago
* 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.

64 points by kawera 8y ago

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

AznHisoka 8y ago
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
- _asummers 8y ago
  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)
  - anderse 8y ago
    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.
  - blattimwind 8y ago
    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?highlight=bundle#sqlalchemy.orm.query.Bundle https://docs.sqlalchemy.org/en/latest/orm/query.html?highlig...
    - djd20 8y ago
      Much simpler usint the ORM - you can defer columns: http://docs.sqlalchemy.org/en/latest/orm/loading_columns.html#deferred-column-loading 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.
      - blattimwind 8y ago
        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.
    - Bishonen88 8y ago
      what sql does this produce though? I can imagine that there'd be a hit on performance
      - blattimwind 8y ago
        Why would you imagine that? This compiles to SELECT col1,...,coln FROM table ...;
- da_chicken 8y ago
  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.
  - teej 8y ago
    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.
    - jlgaddis 8y ago
      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."
  - mistermann 8y ago
    > 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.
deleted 8y ago
[deleted]
slap_shot 8y ago
> 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 8y ago
  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 8y ago
    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.
manquer 8y ago
You may not have all the column names for example when you are working with a crosstab query
lugg 8y ago
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.
- mistermann 8y ago
  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.

tejasmanohar 8y ago

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 8y ago
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”
- blattimwind 8y ago
  Writing "SELECT *" makes it less clear what columns are selected exactly. Explicitly writing what columns shall be returned is more explicit and clear.
  - MichaelApproved 8y ago
    That's helpful from a code review perspective but it's the performance hit you get with a * select that is the bigger reason to avoid it.
    - dahdum 8y ago
      The column name conflicts can cause no end of trouble later on as well.
- scrollaway 8y ago
  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.

ereli1 8y ago

is EXCEPT part of the SQL standard?

grzm 8y ago
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.
blattimwind 8y ago
Not in a column specification of a SELECT query.

ianamartin 8y ago

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 8y ago
Yes it should be a feature in all databases

jmount 8y ago

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.

jlgaddis 8y ago

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

asah 8y ago

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