I stumbled upon an interesting question on Stack Overflow recently. A user wanted to query a table for a given predicate. If that predicate returns no rows, they wanted to run another query using a different predicate. Preferably in a single query. Challenge accepted! Canonical Idea: Use a Common Table Expression We're querying the Sakila … Continue reading How to Execute a SQL Query Only if Another SQL Query has no Results
Tag: PostgreSQL
How to Benchmark Alternative SQL Queries to Find the Fastest Query
Tuning SQL isn't always easy, and it takes a lot of practice to recognise how any given query can be optimised. One of the most important slides of my SQL training is the one summarising "how to be fast": Some of these bullets were already covered on this blog. For instance avoiding needless, mandatory work, … Continue reading How to Benchmark Alternative SQL Queries to Find the Fastest Query
Creating Tables Dum and Dee in PostgreSQL
I was nerd-sniped: https://twitter.com/benjiweber/status/842725600280428545 So tables dee and dum are two theoretical tables in SQL, and they can be characterised as such: [Dee] is the relation that has no attributes and a single tuple. It plays the role of True. [Dum] is the relation that has no attributes and no tuples. It plays the role … Continue reading Creating Tables Dum and Dee in PostgreSQL
Beautiful SQL: Lateral Unnesting of Array Columns
Sometimes, SQL can just be so beautiful. One of the less mainstream features in SQL is the array type (or nested collections). In fact, it's so not mainstream that only 2 major databases actually support it: Oracle and PostgreSQL (and HSQLDB and H2 in the Java ecosystem). In PostgreSQL, you can write: CREATE TABLE blogs … Continue reading Beautiful SQL: Lateral Unnesting of Array Columns
How to Emulate Partial Indexes in Oracle
A very interesting feature of the SQL Server and PostgreSQL databases (and some others, including SQLite) is the partial index (sometimes also called "filtered index"). That's an index that contains only "parts" of the table data. For instance, we can write the following index in SQL Server and PostgreSQL: CREATE INDEX i ON message WHERE … Continue reading How to Emulate Partial Indexes in Oracle
Avoid Using COUNT() in SQL When You Could Use EXISTS()
A while ago, I blogged about the importance of avoiding unnecessary COUNT(*) queries:https://blog.jooq.org/sql-tip-of-the-day-be-wary-of-select-count ... and how to replace them with equivalent EXISTS queries As I'm updating the SQL training to show also PostgreSQL performance characteristics in addition to Oracle, I really have to reiterate this topic. Please repeat after me: Thou shalt not use COUNT(*) … Continue reading Avoid Using COUNT() in SQL When You Could Use EXISTS()
The 10 Most Popular DB Engines (SQL and NoSQL) in 2015
About two years ago, we've published this post about the 10 most popular DB engines, where we analyzed the data published by Solid IT on their DB Ranking website. In the meantime, the Solid IT measurement system has found to be a credible source, such that the website has also been cited at Gartner, InfoWorld, … Continue reading The 10 Most Popular DB Engines (SQL and NoSQL) in 2015
Implementing Client-Side Row-Level Security with jOOQ
Before you read on, please note that since jOOQ 3.19, policies are supported out of the box, to implement the same functionality in a much simpler way than what this article suggests! Some time ago, we've promised to follow up on our Constraints on Views article with a sequel showing how to implement client-side row-level … Continue reading Implementing Client-Side Row-Level Security with jOOQ
Use this Neat Window Function Trick to Calculate Time Differences in a Time Series
Whenever you feel that itch... Can't I calculate this with SQL? The answer is: Yes you can! And you should! Let's see how... Calculating time differences between rows Let's consider the following database containing timestamps (e.g. in a log database). We're using PostgreSQL syntax for this: CREATE TABLE timestamps ( ts timestamp ); INSERT INTO … Continue reading Use this Neat Window Function Trick to Calculate Time Differences in a Time Series
PostgreSQL’s Best-Kept Secret, and how to Use it with jOOQ
PostgreSQL has a lot of secret data types. In recent times, PostgreSQL's JSON and JSONB support was hyped as being the NoSQL on SQL secret (e.g. as advertised by ToroDB) that allows you to get the best out of both worlds. But there are many other useful data types, among which the range type. How … Continue reading PostgreSQL’s Best-Kept Secret, and how to Use it with jOOQ