If you're running on PostgreSQL, you could try the following cool query: WITH RECURSIVE r (r, i) AS ( SELECT random(), i FROM generate_series(1, 1000000) AS t (i) ), s (ri, s, i) AS ( SELECT i, r, i FROM r UNION ALL SELECT s.ri, r.r + s.s, s.i + 1 FROM r JOIN s … Continue reading Approximating e With SQL
Tag: sql
A Rarely Seen, but Useful SQL Feature: CORRESPONDING
I recently stumbled upon a standard SQL feature that was implemented, to my surprise, in HSQLDB. The keyword is CORRESPONDING, and it can be used with all set operations, including UNION, INTERSECT, and EXCEPT. Let's look at the sakila database. It has 3 tables with people in it: CREATE TABLE actor ( actor_id integer NOT … Continue reading A Rarely Seen, but Useful SQL Feature: CORRESPONDING
Using jOOQ’s DiagnosticsConnection to detect N+1 Queries
N+1 queries are a popular problem in many applications that run SQL queries. The problem can be described easily as follows: 1 query fetching a parent value is runN queries fetching each individual child values are run This problem isn't limited to SQL, it can happen with any poorly designed API that does not allow … Continue reading Using jOOQ’s DiagnosticsConnection to detect N+1 Queries
The Useful BigQuery * EXCEPT Syntax
One of the coolest things about using and making jOOQ is that we get to discover the best extensions to the standard SQL language by vendors, and add support for those clauses in jOOQ via emulations. One of these syntaxes is BigQuery's * EXCEPT syntax. Everyone who ever wrote ad-hoc SQL queries would have liked … Continue reading The Useful BigQuery * EXCEPT Syntax
Why You Should Use jOOQ With Code Generation
I'm answering many jOOQ questions on Stack Overflow, and a lot of times. The problem has the same cause: People not using jOOQ's code generator. The main reason people seem not to be using it, is because it takes some extra time to set up, but as with anything well designed, the initial investment will … Continue reading Why You Should Use jOOQ With Code Generation
Fun with PostGIS: Mandelbrot Set, Game of Life, and More
The upcoming jOOQ 3.16 will finally offer support for the various RDBMS GIS extensions via issue #982. This is great news per se, and will be covered in a future blog post, when the integration is ready. This post here is about something else. Adding support for such a feature is a great source of … Continue reading Fun with PostGIS: Mandelbrot Set, Game of Life, and More
PostgreSQL 14’s enable_memoize For Improved Performance of Nested Loop Joins
I've recently discovered a pleasant new addition to PostgreSQL 14, the new enable_memoize flag that improves the performance of some nested loop joins where statistics hint at this being appropriate. I mean, who can resist this temptation: https://twitter.com/RPorsager/status/1455660236375826436 Improving query speed by 1000x hints at something very suboptimal having been going on before, and a … Continue reading PostgreSQL 14’s enable_memoize For Improved Performance of Nested Loop Joins
Functional Dependencies in SQL GROUP BY
The SQL standard knows an interesting feature where you can project any functional dependencies of a primary (or unique) key that is listed in the GROUP BY clause without having to add that functional dependency to the GROUP BY clause explicitly. What does this mean? Consider this simple schema: CREATE TABLE author ( id INT … Continue reading Functional Dependencies in SQL GROUP BY
The jOOQ Parser Ignore Comment Syntax
jOOQ's parser can't parse every possible SQL syntax. Try this random PostgreSQL syntax: ALTER SYSTEM RESET ALL And the jOOQ parser will complain: DOMAIN, INDEX, SCHEMA, SEQUENCE, SESSION, TABLE, TYPE, or VIEW expected: [1:7] ALTER [*]SYSTEM RESET ALL That's perfectly fine. The goal of the jOOQ parser isn't to understand all vendor specific syntax. The … Continue reading The jOOQ Parser Ignore Comment Syntax
Vendor Agnostic, Dynamic Procedural Logic with jOOQ
One of the strengths of modern RDBMS is the capability to mix the powerful SQL language with procedural code. SQL is a 4th generation programming language (4GL), and as such, extremely well suited for querying and bulk data manipulation. Its functional-declarative nature allows for it to be optimised in highly efficient ways using cost-based optimisation, … Continue reading Vendor Agnostic, Dynamic Procedural Logic with jOOQ