5 ms·

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

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

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.