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 happens often, 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 a […]

Why migrate to pg_timetable from pgAgent? There are many reasons, actually, why people might want to export pgAgent jobs to pg_timetable. Most of them may be spotted by taking a look at the PostgreSQL schedulers comparison table I introduced in one of my previous posts and the supported platform table from the official readme. In […]

What are aliases in SQL? SQL aliases in FROM clauses are used to give a result set a temporary name. A result set may be produced by a table or view name, sub-SELECT and/or VALUES clause. An alias is created with the AS keyword, but the grammar allows us to omit it. An alias only […]

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 […]

A free tool to migrate Oracle databases to PostgreSQL We at CYBERTEC are proud to release the CYBERTEC Migrator Standard Edition, the fastest tool to migrate a database to PostgreSQL. And the best part of it is- The CYBERTEC Migrator Standard Edition is available free of charge What? Another free (as in beer) tool to migrate […]

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 […]