What is LISTEN and NOTIFY? LISTEN / NOTIFY is a feature that enables users to listen to what goes on in the database. It is one of the oldest functionalities in PostgreSQL and is still widely used. The main question is: What is the purpose of the asynchronous query interface (LISTEN / NOTIFY), and what […]

Sequences are a core feature of SQL. However, some users are tempted to implement sequences to generate invoices. That’s dangerous and should be avoided. The core question is: Why? What’s the problem with using database-side sequences to put unique invoice numbers to send to clients? Let’s dive in and find out. Getting started with CREATE […]

A join is a concept in IT which is widely used and often referred to but rarely really understood. What are the differences between inner joins, outer joins, semi joins and so on? Let’s shed some light on them and see how inner and outer joins really work. Producing sample data Before we can get […]

serial is a popular pseudo data type in PostgreSQL which is often used to generate auto-increment columns. However, this can lead to issues which are often underestimated. So what is the problem? Some of you might have already seen the following error message in real life: What happened here is that the underlying integer column […]

Running ALTER TABLE … ADD COLUMN can have side effects which have the potential to cause serious issues in production. Changing data structures is an important issue and often comes up, therefore it’s important to understand what is really going on. Let’s dive in and see how to run ALTER TABLE … ADD COLUMN in […]

Of course most people do not want to corrupt their databases. These people will profit from avoiding the techniques used in this article. But for some, it might be useful to corrupt a database, for example if you want to test a tool or procedure that will be used to detect or fix data corruption. […]

We all know and value SQL functions as a handy shortcut. PostgreSQL v14 has introduced a new, better way to write SQL functions. This article will show the advantages of the new syntax. An example of an SQL function Let’s create a simple example of an SQL function with the “classical” syntax so that we […]

I’ve recently seen some really broad tables (hundreds of columns) in a somewhat inefficiently structured database. Our PostgreSQL support customer complained about strange runtime behavior which could not be easily explained. To help other PostgreSQL users in this same situation, I decided to reveal the secrets of a fairly common performance problem many people don’t […]

Bonus cards, “Miles & more”, bonus points – don’t we all love and hate them at the same time? Recently we had an interesting use case which made me think about sharing some of the techniques we used in this area to reduce client code by writing some clever SQL. This post will show you […]

© Laurenz Albe 2022 I was recently confronted with a nice example of how adding FOR UPDATE to a query can introduce transaction anomalies. This article will explain how that happens and how you can cope with the problem. Be ready to learn some PostgreSQL implementation details! The example Setting the stage We have a […]

  © Renée Albe 2022 Case-insensitive search is a much-requested feature, partly (I suspect) to maintain compatibility with Microsoft SQL Server. There are several solutions to the problem, one of which is to use case-insensitive ICU collations. This works like a charm, except if you want to perform pattern matching. So let’s have a closer […]

© Laurenz Albe 2022 Next to character encoding, time zones are among the least-loved topics in computing. In addition, PostgreSQL’s implementation of timestamp with time zone is somewhat surprising. So I thought it might be worth to write up an introduction to time zone management and recommendations for its practical use. Time zones and the […]