Over the past few years, I have been working on assessing the strengths and
weaknesses of PostgreSQL migration tools. Several articles published by yours
truly led to the ambitious project I have been pursuing since 2024 with my
colleagues Étienne Bersac and Pierre-Louis Gonon.
… And the fisrt stable version of PostgreSQL Migrator was released on September
4th. This is an opportunity to showcase features I use daily and what
advantages they offer over other tools. In this article, I want to focus on one
of them, particularly valuable when preparing a migration: the offline
catalog.
PostgreSQL comes with a variety of functions that allow you to group rows into a
“window” and perform calculations on that window. By using these functions, you
can create more advanced and efficient queries for analyzing your database.
In early 2023, I contributed to a project that converts data models to
PostgreSQL, called db_migrator. On this occasion, I (re)discovered the
power of these window functions with the SQL language. In this article, I
revisit a specific case of transforming the upper bounds of a partitioned
table into an array of boundaries.
In a world where we constantly seek to automate repetitive tasks, it is common
to write down a query in a script, make it more convenient, and eventually
integrate the whole thing into a project’s codebase. Tools like SQL*Plus and
psql can be powerful allies in this game, as relevant as Bash or Python
interpreters.
In several projects I have been involved in, I have come across a large number
of those kinds of scripts. Some of them have the particularity of offering input
parameters, processed by SQL*Plus with the very comfortable mechanism named
variable substitution. In this article, I share some tips to convert them to an
equivalent syntax that PostgreSQL’s psql tool can parse and manage.
The SQL standard defines a set of rules so that database systems can be interchangeable,
but there are small singularities in the wild. In this regard, the hierarchyid data type
provided by SQL Server is a striking example. If you are switching to PostgreSQL, two
solutions are available to you.
A first and simpler solution consists in linking each node to its parent using a new
parentid column and applying a foreign key constraint. Another, more complete approach
consists in using the ltree extension. This article deals with the latter case.
The PG Day France took place on June 11th and 12th in Lille, my hometown.
It is the event of the French PostgreSQL community that settles in a different city each year.
The opportunity was too good for me and I met many people from all over France and its surroundings,
to discuss PostgreSQL during two days of workshops and conferences.
For this edition, I had the pleasure of speaking and sharing my experience on the animation of
the local Meetup group that I took over four years ago. In this article, I want to write down the
main points discussed during this presentation, while waiting for the video of the conference to be
posted online.