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

131–140 of 199 posts

Re: New In Postgres 12: Generated Columns

#131

Post by ken »

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.

There's a huge list of caveats in Postgres's inheritance documentation [1], and even the obvious issues haven't been touched in decades. I've never heard of anyone using this feature.

At this point, I would say Postgres itself is a tool that doesn't support Postgres inheritance.

[1]: https://www.postgresql.org/docs/current/ddl-inherit.html

Re: New In Postgres 12: Generated Columns

#132

Post by icebraining »

nobleach wrote: post #128
bauerd wrote: post #21

Earlier quoted context omitted.

I much prefer it to arcane JS(ON) query formats someone dreamed up in a hurry, only to avoid SQL. Only exception I can think of was RethinkDB

ReQL was really nice. I miss that DB (Yes, i know it still exists - but is it still being worked on?)

> is it still being worked on?

Maybe? Seems like ReQL itself will live on, even if not the whole RethinkDB: https://github.com/rethinkdb/rethinkdb/issues/6747

Re: New In Postgres 12: Generated Columns

#133

Post by cryptonector »

Erwin wrote: post #70

An alternate way of doing this is to pass the entire current table row to a function which can be done easily: if you have a table "purchase" PG also creates a "purchase" type, so if you have this function: CREATE FUNCTION vat(a purchase) RETURNS numeric AS 'SELECT a.value * .25' LANGUAGE 'sql' Then you can run: SELECT value, vat(purchase.*) FROM purchase; And so be able to use every purchase column within the SQL fu…

This is a workaround for not having computed columns, and you can even use a VIEW to present an interface that looks a lot like a table with computed columns, so it's pretty good, but you don't get persistence (the column will be computed every time it's required) and you don't get indexing.

Another workaround is to create a proper column and then ON INSERT OR UPDATE triggers that force NEW.that_column to have a computed value. This approach gets you persistent computed columns that you can index on.

What's surprising is that the new functionality in PG 12 doesn't support non-persistent computed columns, and that the other limitations (which are good) don't quite jive with the second workaround I mentioned.

Re: New In Postgres 12: Generated Columns

#134

Post by ken »

raphaelj wrote: post #9
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…

Indeed. SQL concepts are insanely powerful, but the language itself feels a little bit old.

Or is it the other way around? C++/C# concepts are insanely powerful, but they feel to me like we're trying really hard to drag the Simula data model into the 21st century.

Today if you asked me to design an object model for software to model objects, I wouldn't end up with anything remotely like C++/C# has.

Re: New In Postgres 12: Generated Columns

#135

Post by icebraining »

jitans wrote: post #115

This is one of those features to not use ever, unless you have another problem that is an application touching directly a table instead of a view.

Why? This does something a (postgres) view cannot. It's useful even if you have a view in front of the table.

Re: New In Postgres 12: Generated Columns

#137

Post by preommr »

pletnes wrote: post #104
zo1 wrote: post #87

Earlier quoted context omitted.

I find that it helps differentiate the different components of the query. Same way we use all-caps for constants, etc. If it's lower-case, or camel-case, then it just sort of "melts" into the rest of the query.

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

Re: New In Postgres 12: Generated Columns

#138

Post by iblaine »

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.

Re: New In Postgres 12: Generated Columns

#139

Post by pythonwutang »

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.

I doubt 99% of the community is so humorously pessimistic. This functionality can be easily replicated in several databases (with triggers and the like). Generated columns l make a common pattern simpler and more explicit. Maybe it’s not a useful feature for everyone but it’s certainly not some instant tech debt like you’re implying.

Re: New In Postgres 12: Generated Columns

#140

Post by Buttons840 »

dewey wrote: post #15
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 found this book to be very helpful, I'm not done yet but so far I'm really enjoying it. "Perfectly intelligent programmers often struggle when forced to work with SQL. Why? Joe Celko believes the problem lies with their procedural programming mindset, which keeps them from taking full advantage of the power of declarative languages. The result is overly complex and inefficient code, not to mention lost productivity…

For me, it's because I've never studied my database the way I do my programming language. I'll read a thousand pages to master a new language, and I'll take it all to heart. Meanwhile, I begrudgingly read a few pages about PostgreSQL (let's say) and move on to the next task ASAP.

PostgreSQL has great documentation, and I will give it a serious read one day.

Post reply on HN