5 ms·

This seems like a cool feature, but would I be correct in thinking that: 1) If you're only using the generated column for filtering/sorting rows, you'd be bett

This seems like a cool feature, but would I be correct in thinking that: 1) If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? (https://www.postgresql.org/docs/current/indexes-expressional.html https://www.postgresql.org/docs/current/indexes-expressional...) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expression you're using is, because you're saving time by precomputing the column rather than computing at query time. Edit: I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases.

combatentropy 7y ago
> If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? Good point! > I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases. You can do that also with a database view or function, which is my preference. I prefer my tables to be fully normalized. Any computation or processing, I try to keep in views. This just helps my mind. Tables = hard data. Views = processed data. But maybe I'm just set in my ways. Calculated columns are in the SQL standard, after all, and have been implemented in other databases for some time. In special cases (heavy calculation + heavy reads) of course this feature makes a bit more sense.
- eropple 7y ago
  I think your approach makes more sense when thinking about databases directly. If you're using an ORM, you can use a view but it's kind of awkward. I see this as being more useful in that ORM universe--it just ends up being a read-only field on your model.
  - combatentropy 7y ago
    Can you tell me why? Not only can you select from a view, but at least in Postgres you can also insert, update, and delete against one too. I don't use an ORM. But however you specify a table name in the application code, I imagine just specifying the name of a view instead. The ORM selects, inserts, updates, or deletes with the view as the target, instead of a table. It would not know it was not a table.
  - dragonwriter 7y ago
    > If you're using an ORM, you can use a view but it's kind of awkward If it's awkward to use a view in an ORM, it's a bad ORM. Your ORM shouldn't care if a relvar is a table or a view. (It obviously might care if it's updatable or not, but updatable views—both automatically updatable and updatable via specific trigger programming—arw a common thing, as are read-only base tables.)
    - eropple 7y ago
      I dunno, I find using relations I'm not supposed to modify pretty odd in an ORM. And YMMV, but I've never seen an updatable view in the wild. I know it's doable, particularly in Postgres, but it seems like something capital-S Surprising to...probably most folks I've ever worked with?
- dragonwriter 7y ago
  > I prefer my tables to be fully normalized. Computed columns essentially make the base table into a transparent materialized view over the (noncomputed) “real” base table. But it's closer to an ideal materialized view than actual Postgres materialized views because it self-refreshes on need. But, conceptually, it fills the same role as a matview.
rpedela 7y ago
I think your edit is why I am excited about this feature. There are so many times when I just need some relatively simple text formatting, such as titlecase, but want to store the original text too. Titlecase isn't hard to do or expensive, but I only have to do it once with a simple SQL expression. Then client code can decide whether they SELECT the formatted or original text. This is especially helpful if I don't already have an ETL pipeline that includes a cleaning/formatting step, and I just need 1-2 columns to be formatted.

combatentropy 7y ago

> If you're only using the generated column for filtering/sorting rows, you'd be better off using indexes on expressions? Good point! > I can also see the benefit of removing the burden on the person performing the query to have to remember the details of the expression, or in the server case, not having to duplicate the expression across code bases. You can do that also with a database view or function, which is my preference. I prefer my tables to be fully normalized. Any computation or processing, I try to keep in views. This just helps my mind. Tables = hard data. Views = processed data. But maybe I'm just set in my ways. Calculated columns are in the SQL standard, after all, and have been implemented in other databases for some time. In special cases (heavy calculation + heavy reads) of course this feature makes a bit more sense.

eropple 7y ago
I think your approach makes more sense when thinking about databases directly. If you're using an ORM, you can use a view but it's kind of awkward. I see this as being more useful in that ORM universe--it just ends up being a read-only field on your model.
- combatentropy 7y ago
  Can you tell me why? Not only can you select from a view, but at least in Postgres you can also insert, update, and delete against one too. I don't use an ORM. But however you specify a table name in the application code, I imagine just specifying the name of a view instead. The ORM selects, inserts, updates, or deletes with the view as the target, instead of a table. It would not know it was not a table.
- dragonwriter 7y ago
  > If you're using an ORM, you can use a view but it's kind of awkward If it's awkward to use a view in an ORM, it's a bad ORM. Your ORM shouldn't care if a relvar is a table or a view. (It obviously might care if it's updatable or not, but updatable views—both automatically updatable and updatable via specific trigger programming—arw a common thing, as are read-only base tables.)
  - eropple 7y ago
    I dunno, I find using relations I'm not supposed to modify pretty odd in an ORM. And YMMV, but I've never seen an updatable view in the wild. I know it's doable, particularly in Postgres, but it seems like something capital-S Surprising to...probably most folks I've ever worked with?
dragonwriter 7y ago
> I prefer my tables to be fully normalized. Computed columns essentially make the base table into a transparent materialized view over the (noncomputed) “real” base table. But it's closer to an ideal materialized view than actual Postgres materialized views because it self-refreshes on need. But, conceptually, it fills the same role as a matview.

rpedela 7y ago

I think your edit is why I am excited about this feature. There are so many times when I just need some relatively simple text formatting, such as titlecase, but want to store the original text too. Titlecase isn't hard to do or expensive, but I only have to do it once with a simple SQL expression. Then client code can decide whether they SELECT the formatted or original text. This is especially helpful if I don't already have an ETL pipeline that includes a cleaning/formatting step, and I just need 1-2 columns to be formatted.