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

51–60 of 199 posts

Re: New In Postgres 12: Generated Columns

#51

Post by yourad_io »

yourad_io wrote: post #48
hestefisk wrote: post #6

This is really nice. Acts almost as a materialised view.

I think those have to be explicitly refreshed, no?

Ah - they're are two types of generated columns

> There are two kinds of generated columns: stored and virtual. A stored generated column is computed when it is written (inserted or updated) and occupies storage as if it were a normal column. A virtual generated column occupies no storage and is computed when it is read. Thus, a virtual generated column is similar to a view and a stored generated column is similar to a materialized view (except that it is always updated automatically).

https://www.postgresql.org/docs/12/ddl-generated-columns.htm...

Re: New In Postgres 12: Generated Columns

#52

Post by ddorian43 »

aidos wrote: post #42

This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query…

Make a function ?

Re: New In Postgres 12: Generated Columns

#53

Post by luismedel »

Ididntdothis wrote: post #5
redact207 wrote: post #2

As a developer I always hated this feature in other rdbms. The idea of your app saving a row and retrieving it only to have more data in it. IMO these types of calcs are better done in your app, where you can at least write a test and assert it's doing the right thing. It also makes it a lot easier to reason about your code if the logic is in the app rather than bits of it stuck in column definitions. Perhaps there a…

Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?

I'm not a db-engine expert, but if I'd have to write this functionality myself, I'd perform the computation only when any of the involved columns change, not on every read.

EDIT: sorry, I read your comment on the opposite way. I see we are saying the exact same thing :)

Re: New In Postgres 12: Generated Columns

#54

Post by AmericanChopper »

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

I would agree. SQL is absolutely beautiful in its simplicity imo. I learned SQL along side my first imperative programming language, so I guess I’m significantly biased, but I think it’s the easiest language I’ve ever learned. The best part is, if you spend enough time writing SQL, you end up learning quite a bit about how databases, data structures, and the time complexity of query execution all works. Which is all incredibly valuable for just about any programmer.

I also love ORMs. But perhaps my knowledge of SQL has enhanced my ability to use them well. Without them you end up with numerous other problems which I’d consider worse than the occasional compromises ORMs require. Ideally you want all your business logic in one place, or at least in discrete units, so the fragmentation of stored procedures is a major downside. Embedding SQL directly into other business logic is like and uglier version of an FFI, and serializing SQL statements is even more of a security concern than marshalling FFIs (something that can already be a bit of a footgun).

I’d recommend any engineer to learn SQL, but for people who use ORMs a lot already, I’d suggest to start with understanding explain plans first.

Re: New In Postgres 12: Generated Columns

#55

Post by AhtiK »

aidos wrote: post #42

This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query…

This JSON plucking is also much easier now with the json path expressions coming up in v12!

Re: New In Postgres 12: Generated Columns

#56

Post by aidos »

ddorian43 wrote: post #52
aidos wrote: post #42

This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query…

Make a function ?

Do you mean an application layer function, or a dB one? And if a dB one, you also need to create an index to go with it. And then there are dragons that you don’t need in life. If an application one, then people need to know to call it, so, yeah.

I know there are things you can do, but having better tools available is always a win (especially when they remove later maintenance from the equation).

Re: New In Postgres 12: Generated Columns

#57

Post by ako »

ddorian43 wrote: post #52
aidos wrote: post #42

This is great news. Those weird little cases where you need to either defer the computation until read time, or precompute and store yourself always felt warty and ripe for errors. Really glad to see this addition. EDIT an example from this week: we have a json blob full of stuff and we want to pluck out a specific field to search on. You need to jump through casting hoops to hoist it out as an integer when you query…

Make a function ?

Make a trigger

Re: New In Postgres 12: Generated Columns

#58

Post by SigmundA »

Ididntdothis wrote: post #4

I tend to follow the Postgres releases and I am always impressed by the cool things SQL databases can do. But for programmers like me who are used to code in C++/C# I always find the transition from these languages to SQL too harsh. Especially if you don’t have to do SQL daily it’s really hard to remember the syntax and read complex SQL code. Also the transition from SQL results to typed languages is tedious. ORMs he…

Having done both SQL and C# for a very long time I prefer C# with Linq. Linq gives you nearly all the declarative power of SQL with all the procedural goodness of C# to get things done.

Whats needed is a really good object database for C# to eliminate the impedance mismatch.

Don't get me wrong SQL is great, but to be useful it needs to be mixed with a procedural language (pl/SQL, T-SQL, etc) I would just prefer using a more capable typed procedural language instead.

Post reply on HN