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

151–160 of 199 posts

Re: New In Postgres 12: Generated Columns

#151

Post by purplerabbit »

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…

I've been excited about this idea of json-blob-field-to-computed-column for a solid year... I'm so stoked that it's coming to fruition!

Relating json/xml API response blobs to queryable columns is a huge and inefficient burden which I hope that these featuers can help to alleviate.

Re: New In Postgres 12: Generated Columns

#152

Post by SigmundA »

zapov wrote: post #118
SigmundA wrote: post #58

Earlier quoted context omitted.

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…

Postgres is object-relational database, so it can certainly be used in such a way (and I use it like that most of the time). But this is quite a niche usage of PG and there are little to zero tools supporting it.

Yeah but it doesn't support .Net types directly everything must be translated between the two type systems. Also doesn't support executing .Net code in the PG process. These are the main mismatches that ORM's try to hide with significant overhead.

Re: New In Postgres 12: Generated Columns

#153

Post by fleetside72 »

Mister_Snuggles wrote: post #85

The first database I used professionally was called UniVerse[0], currently owned by Rocket Software. It's a Pick-style, non-relational database. In the data dictionary for a file you could create I-descriptors, which were computed columns much like this feature allows. The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. These were co…

I've also used this product but it's more of an operating environment than just a database. Super convenient on the front end. On the down side, getting data out of it is extremely expensive if you aren't targeting a single record ID. It's also not ACID compliant.

Re: New In Postgres 12: Generated Columns

#154

Post by SigmundA »

DougBTX wrote: post #84
skrebbel wrote: post #69

Earlier quoted context omitted.

I love what you're saying, but running a normal modern development and deployment cycle on code stored in databases is hell . Until db vendors start taking developer happiness seriously, stored procedures and triggers are a total non starter for any serious work. I mean, in most databases you can't even rename a column without causing an enormous blocking migration. What? Why can't this happen in the background? Why…

Any DB that requires moving large amounts of data on disk to do a column rename sounds pretty lame. I’d expect most DBs to be like this: https://dba.stackexchange.com/questions/189794/performance-i... A rename should be just a metadata change.

unless your using jsonb to store data ;)

Re: New In Postgres 12: Generated Columns

#155

Post by atombender »

est wrote: post #126

https://dev.mysql.com/doc/refman/5.7/en/create-table-generat... I am a bit surprised nobody mentioned MySQL 5.7 had Generated Columns yet. Any difference between these two?

I've not studied the documentation in detail, but MySQL looks pretty good here. It allows partitioning on a generated column, and Postgres currently does not.

Re: New In Postgres 12: Generated Columns

#156

Post by modernista »

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…

I wish it was easier for regular programmers to use advanced SQL features...

It's a fundamentally different way of thinking.

With imperative programming, the mindset is: "I have this certain outcome in mind. What is the sequence of steps I need to take to make it happen?"

In a relational database, the mindset you need to be in is: "How can I define the relational structure of my world (i.e. my data) so that I can get the outcomes I want - and end up saving a great deal of imperative programming effort in the longer run?"

Quite different. And yes, it takes a while to get used to.

Re: New In Postgres 12: Generated Columns

#157

Post by bobwaycott »

mcny wrote: post #82
pnako wrote: post #37

Earlier quoted context omitted.

Clearly the feature should not be abused, but calculated columns are great for stuff that's obvious and always true (i.e. independent from applications), e.g. a trade value computed from a trade unit price and a trade quantity. This way I can select the top N trades for a given key without having to do the computation in the application, or storing redundant information in the DB.

Not postgresql but I saw a start date and an age in days columns in MS SQL server. The age gets updated daily. It didn't sound right to me. I'm pretty sure I'd fail my database class in college if I did that. What is different in real life and why didn't they teach me this in college?

I obviously can't answer for this particular case, but my first thought on why I would consider doing such a thing is if I had an app in which number of days old was something that had to be queried, displayed, and/or used in other functions/queries a massive number of times per day in the course of normal application usage. If the application had low usage, or number of days old was infrequently queried/displayed/used, I wouldn't consider it. The moment I found that significant time & resources were spent calculating the value in normal/regular usage, I'd start looking at ways to reduce that time & resource usage. How to go about it varies, but the win of that value being immediately available without computation could mean a lot to an app/business and its users.

Re: New In Postgres 12: Generated Columns

#158

Post by ako »

SigmundA wrote: post #96
philliphaydon wrote: post #81

Earlier quoted context omitted.

MartenDB. :)

Right or Entity Framework, but they are just ORM's which is just hiding the impedance mismatch. I am thinking more along the lines of an embedded db like SQLite but does native serialization, indexing and query optimization against .Net objects and types. Then to make a "database server" is really just an app server running your c#. If you really wanted to make it like pg then you would have an app server that accept…

This is basically what most rdbmses have been offering for the last 20 years. Oracle rdbms is in fact an application server, and best practice is to have an app layer built using plsql package through which you access all the data.

More than 20 years ago I actually used it as a true application server, where the database application layer would even generate all the HTML code required to displayed your app ui.

And with database schemas you have proper sandboxing.

Re: New In Postgres 12: Generated Columns

#159

Post by taffer »

skrebbel wrote: post #69
stupidcar wrote: post #13

Earlier quoted context omitted.

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…

I love what you're saying, but running a normal modern development and deployment cycle on code stored in databases is hell . Until db vendors start taking developer happiness seriously, stored procedures and triggers are a total non starter for any serious work. I mean, in most databases you can't even rename a column without causing an enormous blocking migration. What? Why can't this happen in the background? Why…

> I love what you're saying, but running a normal modern development and deployment cycle on code stored in databases is hell.

Can you be more specific? What exactly are you missing?

As long as you put your code and data into separate schemes and follow good technical practices, it shouldn't be too different from other technologies.

> I mean, in most databases you can't even rename a column without causing an enormous blocking migration.

Changing a column name is just a metadata change, so it shouldn't take too long in Postgres.

> The very basics of developer happiness aren't covered, let alone the harder bits, like versioning stored procedures, switching schema and code when switching to a new git branch, and so on.

Does Tomcat or any other application server version your WAR files for you or does it manage git for you?

Re: New In Postgres 12: Generated Columns

#160

Post by GordonS »

LunaSea wrote: post #43
pnako wrote: post #39

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

Or you could test behaviour, and not code. For example, have a test as part of your app's general test suite - insert a row, read it back and check the generated column value is what you expect.
Post reply on HN