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

111–120 of 199 posts

Re: New In Postgres 12: Generated Columns

#111

Post by philliphaydon »

SigmundA wrote: post #106
philliphaydon wrote: post #101

Earlier quoted context omitted.

Well you don’t need to worry about creation, and migrations. You only need to tell the store which objects it needs to know about. You declare everything in c#. It’s stores it as jsonb. So you don’t need to worry about weird relationships you only worry about your root aggregate. You can index it. Apply full text search. And persisted columns in the map if for convince or speed. So Yeah it maps the linq to the equiv…

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.

Re: New In Postgres 12: Generated Columns

#112

Post by taffer »

another-one-off wrote: post #94
taffer wrote: post #74

Earlier quoted context omitted.

> All sorts of things are possible but it misses a fantastic opportunity to compartmentalise the data away from the implementation. That's what schemas are for. You have a schema for your code and a schema for your data. You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data. > Put complex logic…

> You can redeploy the code scheme independently of the data scheme and set up permissions so that higher layers can only use objects from the code scheme and never touch the data. That all just sounds a touch complicated compared to data in database, code somewhere else (like git). I'm circling back to the same point in a couple of different ways, but pgSQL specialises in the relational model of data. That isn't a g…

> That all just sounds a touch complicated compared to data in database, code somewhere else (like git).

You use Postgres as a deployment target and not as a replacement for git. It's not complicated at all. You even get features like transactional deployments and the ability to prohibit applications from directly touching the data.

> pgSQL specialises in the relational model of data

The relational model is SQL:92, Postgres does much more than that. Postgres has JSON support, recursive CTEs, Row Level Security, and Window functions that would require dozens of lines of procedural code to do what can be done with a single OVER in its SELECT clause.

> If I were using SQLite and someone wants to do fancy triggers then maybe I need to swap to PostgreSQL.

If you want to put your business logic into an RDBMS, you wouldn't be using an embedded DB anyway, but rather Oracle, Postgres, SQL Server or DB2, which are designed for this type of architecture.

Re: New In Postgres 12: Generated Columns

#113

Post by timrichard »

misterdoubt wrote: post #71
Ididntdothis wrote: post #31

Earlier quoted context omitted.

I think it’s not only the procedural vs declarative difference but also just the plain syntax. To me SQL is just hard on the eyes. It feels a little like FORTRAN in the good old days.

Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.

Over the years, I must have annoyed various DBAs by saying "Is that a query, or a ransom note?"

Re: New In Postgres 12: Generated Columns

#114

Post by _jal »

misterdoubt wrote: post #71
Ididntdothis wrote: post #31

Earlier quoted context omitted.

I think it’s not only the procedural vs declarative difference but also just the plain syntax. To me SQL is just hard on the eyes. It feels a little like FORTRAN in the good old days.

Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.

I so very wish this convention would die.

Also, leading a new line with the comma.

Re: New In Postgres 12: Generated Columns

#116

Post by rattray »

AhtiK wrote: post #55
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!

Can anyone share a link?

Re: New In Postgres 12: Generated Columns

#117

Post by jitans »

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…

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

Re: New In Postgres 12: Generated Columns

#118

Post by zapov »

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…

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.

Re: New In Postgres 12: Generated Columns

#120

Post by Kihashi »

AmericanChopper wrote: post #54
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,…

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…

IME knowing SQL is a prerequisite to using an ORM efficiently.
Post reply on HN