Seems like a slightly more convenient way than using a trigger to achieve the same...
New In Postgres 12: Generated Columns
41–50 of 199 posts
Re: New In Postgres 12: Generated Columns
#42This 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. It’s doable, but the onus is on everyone who queries to understand this.
Re: New In Postgres 12: Generated Columns
#43Earlier quoted context omitted.
> I can write a test for the db logic, too. I agree but I would add that having testing niceties like branch coverage isn't really possible for SQL queries / PGPLSQL functions.
But you don't need that. That's an issue for whoever implemented your DBMS. To test your queries all you need are unit tests of the standard form: for given inputs, assert(output).
I disagree. If my query calls a PGPLSQL function, I'd like to be able to test and branch cover it.
Re: New In Postgres 12: Generated Columns
#44Earlier quoted context omitted.
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
And if another consumer of the DB doesn't do the calculation or, even worse, does the wrong calculation?
Re: New In Postgres 12: Generated Columns
#45Earlier quoted context omitted.
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
what if you have 50 apps talking to the same DB?
Re: New In Postgres 12: Generated Columns
#46I 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…
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 with a different data model. You don't have a bunch of individual entities floating around. You got rows in tables, which you mostly process in bulk. As such it's more like an Excel spreadsheet.
Re: New In Postgres 12: Generated Columns
#47Earlier quoted context omitted.
Maybe there is a performance benefit to calculating values once at update time vs millions of times during query?
Wouldn't it be better to do the calc in your app and save it to the db? Best of both worlds
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.
Re: New In Postgres 12: Generated Columns
#48This is really nice. Acts almost as a materialised view.
I think those have to be explicitly refreshed, no?
Re: New In Postgres 12: Generated Columns
#49Earlier quoted context omitted.
But you don't need that. That's an issue for whoever implemented your DBMS. To test your queries all you need are unit tests of the standard form: for given inputs, assert(output).
I disagree. If my query calls a PGPLSQL function, I'd like to be able to test and branch cover it.
If you’re the one implementing the functions (or even if you’re not), there are tools such as pgtap that can help you with testing. I’ve used pgtap successful on a number of projects. There’s also no reason you can’t test the behavior of functions through a driver in some other language, though you’re now one step removed.
I’m not aware of any coverage tools, though it’s been a while since I’ve looked.
Re: New In Postgres 12: Generated Columns
#50At first, I thought; /hey pretty cool feature!/ But after contemplating it, is this really necessary? I fear for putting business logic and meanings into the wrong the layer; There are use and abuse, and my consideration fears the latter.
Why do you think placing business logic into the database system is a layering violation? Just because most developers use their DB as a dumb store doesn't mean it needs to be. There are also plenty of successful software systems that place the majority of their business logic and use a generic programming language and runtime only for the presentation layer. If you're comfortable fully exploiting the capabilities of…
It could just be something more pedestrian like not yet being able to handle database changes well for deployments. Things like blue/green, canary, reverting, etc. It's a bit of work to get that functioning well.