Neues vom PostgreSQL Planet

Hi, friends and colleagues in Chicago, and those who plan to attend the PG Day Chicago on April 20! We are looking for volunteers, and there are many options available. I know that the volunteer sign-up was not working before, but now it is fixed, and you can sign up here: the link to the volunteer form.

One of the most frequently reoccurring questions that we get at YugabyteDB how to interpret the information that YugabyteDB provides when people are testing (high) availability. This is a summary of that.

usql? But why?

usql is a universal command-line interface for many database(repositories). But why are we still using CLI (command line utilities) in the 21st century? And what is wrong with psql?

How to correctly detect an SQL NULL value in PL/Perl.

I know that many people are reluctant to admit they made some stupid mistakes (like accidentally dropping a table in production). Some of these mistakes are very visible to the rest of the world, like the infamous Amazon S3 rm *, but raise the hands those who never ever did anything like this?! That’s why I always try to share my “oops,” in hope that it will make many people feel better.

Now, here is my recent story.

PostgreSQL Person of the Week Interview with Miroslav Šedivý: My name is Miroslav Šedivý, but most people call me Miro, which allows them to avoid typing some letters they do not have on their keyboard. I was born in Czechoslovakia (yes, I am over 30 now), studied computer sciences in France and Germany, and now I am living in Austria.

Previously, I wrote that you should never lock tables. And you usually shouldn’t! But sometimes, there’s a good reason to. Here’s one.

When you are doing a schema-modifying operation, like adding a column to a table, PostgreSQL needs to take an ACCESS EXCLUSIVE lock on the table while it is modifying the system catalogs. Unless it needs to rewrite the table, this lock isn’t held for very long.

In PostgreSQL, NUMERIC is a variable length type of fixed precision. You can have as many digits as you want (and you want to pay the storage for). DOUBLE PRECISION is a floating point type, with variable precision.

Sometimes, the question comes up: How much slower is NUMERIC than DOUBLE PRECISION, anyway?

Here’s a quick, highly unscientific benchmark:

 

Hot on the heels of Release 15 comes Release 16.

This release deals with some issues that have been discovered with the check for update feature of Release 15 and the force_every and trigger_exclude features, so that it now works correctly with those features.

It also features these items:

Spoiler Alert!

This article will contain spoilers both on how I solved 2022 Day 12's challenge "Hill Climbing Algorithm" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language. This article is delayed from the actual puzzle's release. Also note that my solutions are seldom going to be the "best" solutions - they were solved as quickly as possible, and these articles will show my first solutions, with some minor reformatting and cleaning up.

Choosing a correct datatype mapping while migrating from Oracle to PostgreSQL is very important to avoid migration failures. Especially when we have date and time involved, it is very important to understand the behavior in both Oracle and PostgreSQL. We have documented about DATE datatype in Oracle vs SQL Server vs PostgreSQL in our previous article.

My presentation Explaining the Postgres Query Optimizer covers the details of query optimization, optimizer statistics, joins, and indexes. I have delivered it 20 times since 2011.

Many maintenance operations are based on finding particular rows, processing them (e.g. sending an email) and finally applying changes to the database. Typically this is implemented as one query to find all rows and many subsequent queries to modify them. This workflow can sometimes be simplified by using the RETURNING feature to manipulate data and get the resulting rows in one step.

Working at Crunchy Data on the spatial team, I'm always looking for new features and fun things to show on live demos. I recently started playing around with ST_Letters and wanted to jot down some quick code samples for playing around with this feature, introduced in PostGIS 3.3. These examples are super easy to use, they don't need any data!

The screenshots shown below came from pgAdmin's geometry viewer and will also work with other query GUI tools like QGIS or DBeaver.

Thank you to everybody who contributed to the January PGSQL Phriday topic! I suggested “Postgres and Software Development,” while being very well aware that this topic is not popular in the PostgreSQL community. To be completely honest, I thought that it was possible that only Ryan Booz would contribute! 

That being said – a big thank you to everyone who participated! Please find a summary of contributions below, and please let me know if I missed your post!

How to convert a digit into a sentence with the power of Perl.

On 9th of January 2023, Tom Lane committed patch: Invent random_normal() to provide normally-distributed random numbers.   There is already a version of this in contrib/tablefunc, but it seems sufficiently widely useful to justify having it in core.   Paul Ramsey   Discussion: https://postgr.es/m/CACowWR0DqHAvOKUCNxTrASFkWsDLqKMd6WiXvVvaWg4pV1BMnQ@mail.gmail.com Description tells is all, but let's see how that works.

🐘 The PostgreSQL community organizes patch reviews into “commitfests” which last for a month at a time, every other month. In this series, our very own PostgreSQL developer advocate and expert, Chris Travers, will discuss a few patches that may be of interest to PostgreSQL users after each commitfest.