6 ms·

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.

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