Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

New In Postgres 12: Generated Columns

pgdash.io

Post reply on HN
Print view View original

181–190 of 199 posts

Re: New In Postgres 12: Generated Columns

#181

Post by Someone »

comboy wrote: post #18
raphaelj wrote: post #9

Earlier quoted context omitted.

Indeed. SQL concepts are insanely powerful, but the language itself feels a little bit old.

I think it aged insanely well. DBs are often a bottleneck and need to be heavily optimized so you need to spend some time explaining to your database what it is that you really need, but in general, SQL is all about telling what do you want and not worrying about how it's going to get done. That's how programming languages should look like. We have so many ORMs and most often they look more ugly than pure SQL to me,…

IMO, a disadvantage of SQL is that it was designed for batch processing, not for modern editors. If you type

  select foo, bar from baz
auto-complete cannot help you type the field names because it doesn’t know what table(s) you’ll be querying. On the other hand, LinQ’s

  from baz select foo, bar
allows your editor to auto-complete the field names for you.

Re: New In Postgres 12: Generated Columns

#182

Post by eropple »

combatentropy wrote: post #174
mopierotti wrote: post #171

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... ) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expre…

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

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.

Re: New In Postgres 12: Generated Columns

#184

Post by combatentropy »

eropple wrote: post #182
combatentropy wrote: post #174

Earlier quoted context omitted.

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

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.

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.

Re: New In Postgres 12: Generated Columns

#185

Post by magicalhippo »

pizza234 wrote: post #179
magicalhippo wrote: post #46

Earlier quoted context omitted.

SQL is a bit verbose at times, but it's not much different from going to other programming languages. In fact I'd say it's easier given that it's so verbose. I have no idea what all those modifier symbols do in Rust, say, but I find "case when InvoiceNo is null then" or "select Name, list(distinct Title) as Titles from" pretty transparent in comparison. The biggest mental difference I think is that you're dealing wit…

What specifically do you find verbose? The only thing I find redundant (based on the queries I handle) is that theoretically join arguments could be deduced from foreign keys (maybe extending this behavior, having joins automatically deduced). I also wonder if the perceived verbosity is caused by some programmers cramming logic into the queries, rather than keeping them simple and processing the results in the client…

It's verbose compared to a lot of other languages simply by using words rather than symbols or abbreviations for most things. Though I tried to convey that I did not think this was a bad thing.

The only case where things get really verbose for me is when I need to do subqueries for columns. Often I might need to run basically the same subquery but return aggregates over different fields, ie sum of net weight, sum of gross weight, sum of value. For performance reasons I can't write that as a join, at least on our SQL server.

I agree there should be a shortcut syntax for joining using foreign keys ("join X on foreign key"?) though.

Re: New In Postgres 12: Generated Columns

#186

Post by dragonwriter »

combatentropy wrote: post #174
mopierotti wrote: post #171

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... ) 2) Therefore, if you're instead interested in returning the generated columns' values, this feature would be useful in proportion to how expensive the expre…

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

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

Re: New In Postgres 12: Generated Columns

#187

Post by dragonwriter »

eropple wrote: post #182
combatentropy wrote: post #174

Earlier quoted context omitted.

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

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.

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

Re: New In Postgres 12: Generated Columns

#188

Post by [deleted] »

Someone wrote: post #181
comboy wrote: post #18

Earlier quoted context omitted.

I think it aged insanely well. DBs are often a bottleneck and need to be heavily optimized so you need to spend some time explaining to your database what it is that you really need, but in general, SQL is all about telling what do you want and not worrying about how it's going to get done. That's how programming languages should look like. We have so many ORMs and most often they look more ugly than pure SQL to me,…

IMO, a disadvantage of SQL is that it was designed for batch processing, not for modern editors. If you type select foo, bar from baz auto-complete cannot help you type the field names because it doesn’t know what table(s) you’ll be querying. On the other hand, LinQ’s from baz select foo, bar allows your editor to auto-complete the field names for you.

[deleted]

Re: New In Postgres 12: Generated Columns

#189

Post by eropple »

dragonwriter wrote: post #187
eropple wrote: post #182

Earlier quoted context omitted.

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.

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

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?

Re: New In Postgres 12: Generated Columns

#190

Post by magicalhippo »

GordonS wrote: post #162
yourad_io wrote: post #47

Earlier quoted context omitted.

So long as everyone, everywhere, always remembers to calculate it in the same way for all inserts and updates. This seems a lot more robust to me.

You can use abstractions that push such code out of the developers concern, into the "code infrastructure" layer. But there are also plenty times where I'd rather compute at the database side; there are no silver bullets.

And then someone at support comes along and updates the data via SQL and forgets to update the derivative field because he got woken up at 2am for an emergency.

Using a computed column ensures the data is consistent.

Post reply on HN