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

141–150 of 199 posts

Re: New In Postgres 12: Generated Columns

#141

Post by viblo »

SigmundA wrote: post #58
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…

You might want to take a look at starcounter. https://starcounter.io/ I haven't used it myself, but saw a demo in their office a couple of years ago.

Re: New In Postgres 12: Generated Columns

#142

Post by dewey »

preommr wrote: post #137
pletnes wrote: post #104

Earlier quoted context omitted.

Not with syntax highlighting.

The tooling around sql is pretty awful. I was looking for a vscode plugin to get some syntax highlighting and linting for postgres yesterday, and just gave up. There are plenty of administrative tools that help with connecting to dbs and showing the results in a nice table, but nothing that really helps with writing it (or at least nothing of high quality that I could find).

https://www.jetbrains.com/datagrip/ and Postico are pretty great for PG I think. I'm using them every day and having the option to attach DataGrip to a directory of queries in your code and directly executing them from there with set variables is pretty sweet.

It's helping you write queries by auto formatting and good auto completion.

Re: New In Postgres 12: Generated Columns

#143

Post by PeterisP »

tirumaraiselvan wrote: post #60

Why do they call it Generated Columns and not Computed Columns like most other databases already do?

IMHO "computed columns" generally implies columns that are computed on-demand when read, as opposed to this case when they're computed when the row is altered and persisted in the table.

Re: New In Postgres 12: Generated Columns

#144

Post by Mister_Snuggles »

uhoh-itsmaciek wrote: post #121
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…

Is that different from adding such columns in a view enriching the table?

For most things no, but there are some things you can do that are difficult or impossible to do in an SQL view.

I-Descriptors can call subroutines, which opens them up to doing anything that the underlying UniVerse BASIC runtime can do.

I once made an I-Descriptor which called a subroutine to feed the postal code into a web service that returned the geographic location of that postal code. It also implemented caching so it would only call the service for a postal code once. You could simply list out the file and find customers in a particular geographic region just using the query language.

I'm sure this is do-able using something like PL/Python, but I suspect it would be much more difficult to do it all in-database than how I did it in UniVerse.

Whether doing it this way is a good idea is, of course, up for debate. In a modern application I wouldn't dream of doing this on the fly in a view. Just because I could build it the way I did doesn't mean it was a good idea.

Re: New In Postgres 12: Generated Columns

#145

Post by Mister_Snuggles »

dragonwriter wrote: post #123
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…

> The difference is that I-descriptors were always calculated on the fly and they could do a LOT more than PostgreSQL's generated columns. So, just like columns in a view. > These were commonly used to accomplish things that SQL would use a JOIN to do, mainly because the query language didn't have joins. Yeah, in SQL you could do that with a correlated subquery in a column definition in a view, instead of a join, but…

Yes, for the common case a view does the trick.

However, I-Descriptors have the power of the underlying UniVerse BASIC runtime at their disposal, so you can do a LOT of things. I mention one of these in another comment[0].

However, as I mentioned, doing it this way isn't necessarily a good idea.

[0] https://news.ycombinator.com/item?id=21137935

Re: New In Postgres 12: Generated Columns

#146

Post by dragonwriter »

iblaine wrote: post #138

1% of the people that want this feature are surely thrilled. It is a cool feature. The other 99% of us are looking at this like a potential land mine that will show up unsuspectingly when trying to refactor old code, or migrating from postgres.

Generated/computed columns are a fairly common RDBMS feature; MySQL has it since 5.7; SQL Server, Oracle, and DB2 have them. So, Postgres having them makes migrating into Postgres easier and migrating out no more difficult except maybe if you are migrating to SQLite or a nonrelational store.

Re: New In Postgres 12: Generated Columns

#147

Post by tabtab »

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…

ORM's tend to lock one into a particular architecture. SQL is more standardized across platforms. It's my opinion that ORM's should assist with SQL generation or usage rather than outright hide it. ORM's job should be to reduce the grunt-work of preparing typical SQL, not wrapping it fully so you can ignore it.

You can take your SQL knowledge to another platform, but less so with say Entity Framework or LINQ. Don't get locked in. Plus, if the ORM doesn't do your query correctly or as intended, someone will need to understand SQL anyhow to debug it.

Re: New In Postgres 12: Generated Columns

#148

Post by scarlac »

makach wrote: post #3

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

I agree that it should be used sparingly. But for more than 5 years I've wished I had a computed column feature because you are often thrown into code bases that need refactoring and refactoring 100% in 1 commit is not always the safest way to go. Sometimes you want to migrate a codebase in several steps. Computed/virtual columns are an absolutely fantastic feature and it allows refactoring and migrations at your own pace, or as a fallback.

Re: New In Postgres 12: Generated Columns

#149

Post by durkie »

icebraining wrote: post #127
jitans wrote: post #117

Earlier quoted context omitted.

Why your application doesn't use a view? Or even better a table function?

At least in PG, a regular view must calculate everything at read time, which might mean a lot of duplicated calculations, and a materialized view has to be refreshed "manually", it doesn't auto-update piecemeal when the underlying data changes.

I've had pretty good luck with using table triggers to update materialized views and make everything "automatic". A little more work up front, but pretty easy to forget about it once it's in place.

Re: New In Postgres 12: Generated Columns

#150

Post by SigmundA »

philliphaydon wrote: post #111
SigmundA wrote: post #106

Earlier quoted context omitted.

Again that's great, its an ORM, that's what they do, try to abstract the DB from your language, and do their best to hide the impedance mismatch. However the mismatch is always there, the overhead of mapping types, inefficient serialization (key names repeated over and over in json vs a real schema), certain statements cannot be transpiled to sql so you have pathologic cases at unexpected times, in ability to run any…

It’s jsonb so you can’t repeat key names. Serialization is what ever you want to use for json serialization. Other than some report type queries I wanted to write I haven’t found anything that I couldn’t do in linq.

jsonb doesn't do key interning as far as I know. Every field name will be repeated in every record vs a normal column which will only have the column name once in the db.

This really adds up on a large database. Mongo used to recommend short key names and I even think some client would translate them based on some definition to try and save disk/memory/io

https://github.com/postgrespro/zson tries to do key compression.

Also its not that Linq can't do what you want, its that if you do certain things that can't be translated to SQL the whole intermediate result will be retrieved from the db, deserialized then the linq statement will be run in memory on the client. Its like putting ToList in the middle of your linq statement where a maybe a few million rows might need to be processed to give the very small result.

If the linq statement was being run in process on the db server at least it would happen local to the data and would be more akin to a table scan vs index, still not great but much better then sending all the results to the client for processing.

Post reply on HN