121–130 of 199 posts
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…
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 isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday) You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is sto…
[1] https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
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…
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 it's generally not optimal (I've seen people do it, though not recently.) There's other uses for correlated subqueries besides being the inefficient way to do joins, and they, plus normal functions, let you calculate just about anything you might want in a columns in a view.
> The results of the I-descriptor don't have to be stable - they can be calculated based on the current date/time, random numbers, data in other files, etc. This leads to some interesting possibilities that I don't think PostgreSQL's implementation can touch.
The calculated columns one that supports only immutable functions because the values are materialized which doesn't make sense otherwise can't, but then on-the-fly calculation makes reads expensive. If you need that, though, Postgres supports it in views since (approximately) forever, which are the classic SQL approach to the problem.
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 isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday) You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is sto…
CREATE INDEX myindex ON mytable (myfunc(mytable));Earlier quoted context omitted.
This isn’t a good example because you wouldn’t pre calculate vat and store it for product listing, as vat doesn’t apply to all countries and it’s subject to change, and differ between countries. (Japan just changed gst yesterday) You also wouldn’t want to run a function on something you need to filter against. To give you an example we have the concept of a “deadline” date which is based on the time the record is sto…
I'm not sure if I understand you correctly, but logically the WHERE clause happens before the SELECT clause[1], so it only calculates this value for the rows you're interested in. It is also possible to index functions without generated columns. [1] https://blog.jooq.org/2016/12/09/a-beginners-guide-to-the-tr...
The second part I’m giving an example where doing:
where created + period > now() - '3 days'::interval
Having to calculate the value in a where clause is inefficient.
Making a calculated column adding created and period then indexing it is more efficient.
I am a bit surprised nobody mentioned MySQL 5.7 had Generated Columns yet. Any difference between these two?
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?
Earlier quoted context omitted.
Indeed. SQL concepts are insanely powerful, but the language itself feels a little bit old.
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
Earlier quoted context omitted.
Well it doesn't help that people continue using ALL CAPS for SQL keywords as a preferred style. In this century.
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.
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…
In all the algebra classes I've taken, one nice thing about sets is you can arrange them in any order you like, and you'll get the same answer any way you slice it. There's no inherently correct orientation of mathematics. That's kind of the point. It's fully generic, by default. "x" can mean anything (at least, in the algebraic structure we've assumed).
With SQL, the experts drone "think in sets!" as a way to mean "you happened to pick a different order than me, so your query will run 1000 times slower". Well, mine is still sets. I'm not writing a for-loop here (and I'm pretty sure my SQL dialect has those by now).
I've actually had the most luck with SQL by thinking about it in terms of looping first. Figure out the most efficient loop over your biggest table, and then write a query in a way that makes it easy for the optimizer to loop over that.
Then again, nobody ever accused me of being "perfectly intelligent".