If you're exploring JavaScript development on the backend, Node.js and NPM (Node Package Manager) are indispensable tools. Whether you're creating web servers, APIs, automation scripts, or CLI tools, Node.js and NPM make it all possible. But before you jump into coding, you'll need a proper setup—especially if you’re using Debian 11, a highly stable and secure Linux distribution. This guide explains how to install NPM on Debian, using the most reliable steps based on Vultr’s...
jOOQ workaround for "can't specify target table for update in FROM clause"
In MySQL, you cannot do this:
create table t (i int primary key, j int);
insert into t values (1, 1);
update t
set j = (select max(j) from t) + 1;
The UPDATE statement will raise an error as follows:
SQL Error [1093] [HY000]: You can’t specify target table ‘t’ for update in FROM clause
People have considered this to be a bug in MySQL for ages, as most other RDBMS can do this without any issues,...
jOOQ 3.19 offers many new and useful path based join features
jOOQ 3.19 finally delivers on a set of features that will greatly simplify your queries further, after jOOQ 3.11 introduced implicit to-one joins:
Explicit path joins
To-many path joins
Implicit join path correlation
What are these features?
Many ORMs (e.g. JPA, Doctrine, jOOQ 3.11 and others) support “path joins” (they may have different names for this concept). A path join is a join derived from a path where...
Implicit path joins may now skip unnecessary tables in the join tree
One of jOOQ’s key features so far has always been to render pretty much exactly the SQL that users expect, without any surprises – unless some emulation is required to make a query work, of course. This means that while join elimination is a powerful feature of many RDBMS, it isn’t part of jOOQ’s feature set, so far.
This changes, to some extent, with jOOQ 3.19, and #14992, for implicit path joins only. So far, when...
What's faster, COUNT(*) or COUNT(*) with LIMIT in SQL? Let's check
In a previous blog post, we’ve advertised the use of SQL EXISTS rather than COUNT(*) to check for existence of a value in SQL.
I.e. to check if in the Sakila database, actors called WAHLBERG have played in any films, instead of:
SELECT count(*)
FROM actor a
JOIN film_actor fa USING (actor_id)
WHERE a.last_name = 'WAHLBERG'
Do this:
SELECT EXISTS (
SELECT 1 FROM actor a
JOIN film_actor fa USING (actor_id)...
Oracle's way to get multiple values in a top 1 per group query
I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog.
An Oracle specific version in that post used the arcane KEEP syntax:
SELECT
max(actor_id) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
max(last_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id),
max(c) KEEP (DENSE_RANK FIRST...
How to implement FILTER semantics with Oracle JSON aggregate functions
A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS:
ClickHouse
CockroachDB
DuckDB
Firebird
H2
HSQLDB
PostgreSQL
SQLite
Trino
YugabyteDB
The following aggregate function computes the number of rows per group which satifsy the FILTER clause:
SELECT
COUNT(*) FILTER (WHERE BOOK.TITLE LIKE 'A%'),
COUNT(*) FILTER (WHERE...
jOOQ 3.20 released with ClickHouse, Databricks, and much more DuckDB support, new modules, Oracle type hierarchies, more spatial support, decfloat and synonym support, hidden columns, Scala 3, Kotlin 2, and much more
New dialects:
jOOQ 3.20 ships with 2 new experimental dialects:
ClickHouse in all editions, including the jOOQ Open Source Edition
Databricks in the jOOQ Enterprise Edition
ClickHouse is a fast-moving SQL dialect with a historic vendor-specific syntax that is...
Why I don't document quirks of a feature on the feature itself.
Every product manager knows this situation:
A user works with feature X1.
They find a limitation / bug / quirk and want to work around it.
The perfect workaround or alternative is feature X2, but without knowing that X2 exists, the user doesn’t find it and spends a lot of time looking for it.
The user requests X2 be documented on X1, because that would have saved them a ton of time.
This is such a common...
A SQL MERGE statement performs actions based on a RIGHT JOIN
RIGHT JOIN is an esoteric feature in the SQL language, and hardly ever seen in the real world, because almost every RIGHT JOIN can just be expressed as an equivalent LEFT JOIN. The following two statements are equivalent:
-- Popular
SELECT c.first_name, c.last_name, p.amount
FROM customer AS c
LEFT JOIN payment AS p ON c.customer_id = p.customer_id
-- Esoteric
SELECT c.first_name, c.last_name, p.amount
FROM payment AS p
RIGHT...
A few modern SQL dialects have started introducing lambda expressions
ARRAY types are a part of the ISO/IEC 9075 SQL standard. The standard specifies how to:
Construct arrays
Nest data into arrays (e.g. by means of aggregation or subqueries)
Unnest data from arrays into tables
But it is very unopinionated when it comes to function support. The ISO/IEC 9075-2:2023(E) 6.47 <array value expression> specifies concatenation of arrays, whereas the 6.48 <array value...
Using Pages CMS for Static Site Content Management | CSS-Tricks
Friends, I’ve been on the hunt for a decent content management system for static sites for… well, about as long as we’ve all been calling them “static sites,” honestly.
I know, I know: there are a ton of content management system options available, and while I’ve tested several, none have really been the one, y’know? Weird pricing models, difficult customization, some even end up becoming a whole ‘nother...
About
Terms
Privacy
Advertisment
DMCA
Gmaps
Rewards
Refunds
APP
Developers
Proxy
Croatian
Arabic
French
Spanish
Portuguese
Deutsch
Turkish
Dutch
Italiano
Russian
Romaian
Portuguese (Brazil)
Greek
Chech
Afganistan
