Neues vom PostgreSQL Planet

Since yesterday my sites (explain.depesz.com, paste.depesz.com, and this blog) are no longer available from IPs that are in Russia or Belarus. I know this is not huge loss for them, but, I think, every single bit counts. If you'd like to do the same thing for their site/service: You can download free Geoip2Lite database from … Continue reading "Stop the war in Ukraine"

Understanding Multiranges

Range types have been around in PostgreSQL for quite some time and are successfully used by developers to store various kinds of intervals with upper and lower bounds. However, in PostgreSQL 14 a major new feature has been added to the database which makes this feature even more powerful: multiranges. To help you understand multiranges, I have compiled a brief introduction outlining the basic concepts and most important features.

Gathering the success rate and latency of Postgres queries

PostGIS is one of the most awesome extensions for PostgreSQL and can turn a relational database into a really powerful GIS (Geographic Information System). The PostGIS community is really great about documentation and training and this post is aimed at getting you some resources on how to get started with the major components of using PostGIS as a super beginner.

PostgreSQL Person of the Week Interview with Jehan-Guillaume De Rorthais: I’m from France and living there. I work with PostgreSQL since 2009 thanks to Dalibo.

On 8th of April 2022, Andrew Dunstan committed patch: Documentation for SQL/JSON features   This documents the features added in commits f79b803dcc, f4fb45d15c, 33a377608f, 1a36bc9dba, 606948b058, 49082c2cc3, 4e34747c88, and fadb48b00e.   I have cleaned up the aggregate section of the submitted docs, but there is still a deal of copy editing required. However, I thought … Continue reading "Waiting for PostgreSQL 15 – Documentation for SQL/JSON features"

On 4th of April 2022, Andrew Dunstan committed patch: JSON_TABLE   This feature allows jsonb data to be treated as a table and thus used in a FROM clause like other tabular data. Data can be selected from the jsonb using jsonpath expressions, and hoisted out of nested structures in the jsonb to form multiple … Continue reading "Waiting for PostgreSQL 15 – JSON_TABLE"

 I released bugfix releases of plpgsql_check and Orafce.

Attention - In had to change signature of nvl2 function in Orafce. With this change, the behave is much more close to Oracle's nvl2 function.  Unfortunately, this change can break extension update when some users use nvl2 function in views.  The related views should be dropped first, and after extension update should be created again.

How great it is pgbadger?

The Postgres.ai team is happy to announce the release of version 3.1 of Database Lab Engine (DLE), the most advanced open-source software ever released that empowers development, testing, and troubleshooting environments for fast-growing projects. The use of Database Lab Engine 3.1 provides a competitive advantage to companies via implementing the "Shift-left testing" approach in software development.

Sometimes you just want to find some value, regardless of which column it's in. If the table has few columns you can easily: =$ select * from table where a ~ '...' or b ~ '...' or c ~ '...' But if there are many columns writing such query quickly becomes tedious. Luckily there is … Continue reading "DBA tips ‘n tricks – search in every field in a table"

date_bin and timestamps in PostgreSQL

Date and time are relevant to pretty much every PostgreSQL application. A new function was added to PostgreSQL 14 to solve a problem which has caused challenges for many users out there over the years: How can we map timestamps to time bins? The function is called date_bin.

Andrew Dunstan committed SQL_JSON - one of the most awaited part of remaining part of SQL/JSON standard, which our team in Postgres Professional were working since early 2017 and first version was presented in our talk at PGConf.eu - 2017. Kudos, Andrew, you helped us with committing jsonb and help now !

With the Russian invasion of Ukraine, there is renewed focus on the use of Postgres by governments, particularly uses that might further military goals.

On Thursday and Friday of this week we will be enjoying 90 degree weather and sunshine in San Jose, California. It will be quite the change from the dark and damp of NW Washington. This is also the first time I will have been on an airplane in almost 3 years. Yes, it really has been that long since the world decided to begin a pandemic. That said, I am excited for Postgres Conference Silicon Valley 2022 and in particular the following sessions:

An Event for Postgres is a free and virtual developer event organized by the Postgres team at Microsoft Azure.

PostgreSQL Person of the Week Interview with Taras Kloba: My name is Taras Kloba, I was born in Drohobych. It is a small town in Western Ukraine with a population of about 75 thousand people. Currently, I live in Lviv with my wife and our three daughters — our oldest daughter is seven years old, and the twins are two years old.

Using encrypted network connections is not really a choice in a modern environment. But: using an additional encryption phase, and using an encryption handshake will add latency and overhead to a network connection.

This writeup is more a scratchpad for myself, because there are other blogposts that guide you with enabling SSL. However, these did not exactly what I wanted to do.

My small contributions to pgagroal.

pgagroal log rotation and formatting

A few weeks ago I implemented a small contribution to [pgagroal](https://agroal.github.io/pgagroal/){:target="_blank"}, the high-performance PostgreSQL connection pooler, in order to implement log rotation and log formatting.
At last, my contribution was accepted and merged, but I did not get enough time to write on this until now.

Imagine you have something like this in Django:

class MyModel(models.Models): last_name = models.CharField(max_length=255, blank=True) ...

The most basic sorting is either: queryset.order_by('last_name') or queryset.order_by('-last_name'). But what if you want entries with a blank string last? And, you want it to be case insensitive. Here's how you do it: