In reply to Bernd Huber. I think this is what you're looking for: https://...
In reply to Bernd Huber. Why would you want to use MULTISET for 1:1 relati...
In reply to Bernd Huber. I think i got it to work now for my example, by m...
i love this approach, and i would like to make use of this for all kind of table relationships (1:N, N:N, 1:1), but while i have no problem making it work for 1:N, N:N with help of your examples,...
In reply to Raju Angani. It doesn't matter if you project * or specific value...
SELECT * FROM actor a JOIN film_actor fa USING (actor_id) WHERE a.last_name = 'WAHLBERG' AND ROWNUM <= 2; Why not limit the SELECT clause when you only need the count? SELECT a.last_name FROM act...
I went with the style of solution mentioned above: "simply iterate over all the positions and possible lengths and check for palindromes with REVERSE()." This code works in SQL Server 2022. decla...
https://blog.jooq.org/finding-all-palindromes-contained-in-strings-with-sql/#comment-429360
In reply to Daniel. ExecuteListener is the right place. Query::$replace will always...
https://blog.jooq.org/traversing-jooq-expression-trees-with-the-new-traverser-api/#comment-429355
Maybe I'm just not seeing the wood for the trees ;-) Your examples all use print to console. How can this be used to replace elements before executing an actual query? I would have thought placin...
https://blog.jooq.org/traversing-jooq-expression-trees-with-the-new-traverser-api/#comment-429353
https://blog.jooq.org/whats-faster-count-or-count1/#comment-429346
Is this still the case for recent versions of PG ?
https://blog.jooq.org/whats-faster-count-or-count1/#comment-429345
In reply to Renan. Thanks for your message. Have you read the article? Why do you prefer the impera...
https://blog.jooq.org/create-empty-optional-sql-clauses-with-jooq/#comment-429342
I think jOOQ should have some interfaces we can reference safely. For example: Is pretty common to have a "Base Query", which is dinamic based on filters and sorts that the user inputs, and using...
https://blog.jooq.org/create-empty-optional-sql-clauses-with-jooq/#comment-429337
I think that I'm heading towards the merged in memory approach, but using a Hibernate subselect style query in order to be able to use virtual threads to execute the two concurrently. It's a sham...
https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/#comment-429336
In reply to Simon Hartley. Thanks, I did the same here: https://gist.github.com/lukaseder...
https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/#comment-429335
I noticed that you commented on this article: https://spring.io/blog/2023/08/31/this-is-the-beginning-of-the-end-of-the-n-1-problem-introducing-single-query I gave it a go and generated some SQL,...
https://blog.jooq.org/the-performance-of-various-to-many-nesting-algorithms/#comment-429331
As a developer (generally of trading systems), I've never once used a piece of software and thought to myself "Gee, this program runs too fast; I wish the developer had spent time making the sour...
Snowflake supports this with SELECT * EXCLUDE ...
. It's a
shame Postgres still doesn't include this useful feature.
https://blog.jooq.org/selecting-all-columns-except-one-in-postgresql/#comment-429311
In reply to Jeff. Sure, it's a bit more work, but I've done it e.g. for Informix
https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/#comment-429304
Can this be done for a database that does not provide a testcontainer out of the box? I am using testcontainers for my integration tests, but I must use a custom GenericContainer because none is ...
https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/#comment-429299
In reply to Alexis Manin. Yes, kotlin's internal visibility is supported as a vi...
https://blog.jooq.org/how-to-generate-package-private-code-with-jooqs-code-generator/#comment-429298
Then, is it correct to assume that we can also use
visibilityModifier
with Kotlin Generator to use
'internal' scope ? This would be even more practical than java package
private visi...
https://blog.jooq.org/how-to-generate-package-private-code-with-jooqs-code-generator/#comment-429297
In reply to Vadym. Just be sure you don't actually run into this limit on the server!
https://blog.jooq.org/mysqls-allowmultiqueries-flag-with-jdbc-and-jooq/#comment-429296
In reply to romchellis. I helped in the making. Do you have any specific questions?
https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/#comment-429295
For everyone, who is looking how to use latest jooq version and
without allowMultiQueries=true
, you can set a
Setting.renderGroupConcatMaxLenSessionVariable = false
https://blog.jooq.org/mysqls-allowmultiqueries-flag-with-jdbc-and-jooq/#comment-429294
Hi Lukas Eder! What do you think about updating this article referring to this plugin https://github.com/testcontainers/testcontainers-jooq-codegen-maven-plugin ?
https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/#comment-429291
In reply to Mikko Markus Torni. Why not just ANALSE DUM?
CREATE TABLE dum(); ANALYSE dum; EXPLA...https://blog.jooq.org/creating-tables-dum-and-dee-in-postgresql/#comment-429290
In reply to Morris de Oryx. Yeah, nice. They seem to imply LEFT SEMI and LEFT ANTI, t...
https://blog.jooq.org/semi-join-and-anti-join-should-have-its-own-syntax-in-sql/#comment-429289
I suggest the following enhancement. The PostgreSQL 13.0 query planner doesn't like DUM very much, it seems to think it's got thousands of rows. And analyzing the table doesn't help, maybe becaus...
https://blog.jooq.org/creating-tables-dum-and-dee-in-postgresql/#comment-429287
I just noticed today that DuckDB has added keyword/syntactic support for several useful JOIN operations, including SEMI and ANTI. https://duckdb.org/docs/sql/query_syntax/from
https://blog.jooq.org/semi-join-and-anti-join-should-have-its-own-syntax-in-sql/#comment-429286
In reply to Christian Lewold. Some RDBMS have computed columns (ideally virtual, as opposed to stored),...
https://blog.jooq.org/having-constant-columns-in-foreign-keys/#comment-429284
The solution helps, but introduces some smell I don't like. E.g. table T2 always shows a set of totally useless columns for each "constant" used. (when doing a select * or something). Imho the "r...
https://blog.jooq.org/having-constant-columns-in-foreign-keys/#comment-429281
In reply to Dirk. Thanks for your comment. The query was flawed indeed...
I think there is a problem with the postgres CTE. When you insert a new level under the "readme_eclipse.html" node, you get two tree´s with the CTE code. is there a solution?
In reply to Alf. Maybe this helps? SELECT * FROM (VALUES (1)) AS t (a) LEFT JO...
My understanding is that a predicate on the right relation after a left join is equivalent with an inner join unless the predicate allows for null-values. Your examples seem to prove me wrong, un...
In reply to Pelle P. See here: https://blog.jooq.org/the-performance-of-va...
This is awesome! One question though: how do these queries with nested collections perform? You save on round-trips, you save on data that needs to be transferred. But will the queries perform?
In reply to Simon
Hartley. Thanks for the pointers. Indeed, jOOQ's
There is a mistake in the example of using the copy-and-paste non-Jooq collector. It refers to a non existent "l". That line should probably be: e -> new Hierarchical(e.id(), e.name(), new Array...
The SO answer you are quoting, in turn received it's inspiration from this SO user's answer: https://stackoverflow.com/questions/25678509/postgres-recursive-query-with-row-to-json/43917048#439170...
In reply to Bernd Huber. You can always export any Meta representation in jOOQ back to DDL. Just c...
https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/#comment-429259
i love to work with jOOQ, and want to ask a question about why jOOQ does not have (maybe in addition to the Schema-To-Model Codegen it already has) also a "Model-To-Schema" Approach. I currently ...
https://blog.jooq.org/why-you-should-use-jooq-with-code-generation/#comment-429258
In reply to Shaun. Why wouldn't it work? What's your specific question?
https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/#comment-429254
Is it possible to make this work with Java based migrations for Flyway? My suspicion is no, due to the where things are happening in the various phases, but if anyone has gotten this working I'd ...
https://blog.jooq.org/using-testcontainers-to-generate-jooq-code/#comment-429252
The equivalent of LEAD/LAG IGNORE NULLS
by using windowed
ARRAY_AGG function, skipping NULL values and accessing last element of
cumulative ARRAY Below PostgreSQL version: WITH
sour...
https://blog.jooq.org/using-ignore-nulls-with-sql-window-functions-to-fill-gaps/#comment-429250
In reply to johnny willer. Thanks for your suggestion. There's a feature request to add Table.select(...), wh...
https://blog.jooq.org/the-useful-bigquery-except-syntax/#comment-429244
Could jooq improve on readability and have a except for the natural join clause itself? Something like ctx.select( .from(a) .naturalLeftOuterJoin(fa).except(a.LAST_UPDATE)) .groupBy(a.ACTOR_ID, a...
https://blog.jooq.org/the-useful-bigquery-except-syntax/#comment-429243
In reply to Bernd. Sorry for the delay, Bernd. The notification for yo...
is the Scala-Support of jooq still an important topic in 2022 ? are there play for the future? will this be improved further or does it just work good ? i'm asking because i would really like to ...