Neues vom PostgreSQL Planet

Geocoding is the process of taking addresses or location information and getting the coordinates for that location. Anytime you route a new location or look up a zip code, the back end is geocoding the location and then using the geocode inside other PostGIS functions to give you the routes, locations, and other data you asked for.

Over the course of the last few versions, PostgreSQL has introduces all kinds of background worker processes, including workers to do various kinds of things in parallel. There are enough now that it’s getting kind of confusing. Let’s sort them all out.

You can think of each setting as creating a pool of potential workers. Each setting draws its workers from a “parent” pool. We can visualize this as a Venn diagram:

Dear Chicago PostgreSQL Community! I have an important update on our March 8 meetup. In honor of International Women’s Day, we are giving away three tickets to PG Day Chicago to the first three females who will RSVP and come – courtesy of Postgres Women.

Please register here.

Invitation from Grant Fritchey

Grant is someone I’ve looked up to in the SQL Server and #SQLFamily community for a long time. It’s an honor to now work alongside him at Redgate.

Stored procedures are a core concept which can be found in most relational database systems. They have proven to be useful in many areas and have been widely adopted by developers and DBA’s alike.

Stored procedures basics

In PostgreSQL stored procedures have been around for a number of years. The syntax of this important command is defined as follows:

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

On 11st of February 2023, Andres Freund committed patch: Add pg_stat_io view, providing more detailed IO statistics   Builds on 28e626bde00 and f30d62c2fc6. See the former for motivation.   Rows of the view show IO operations for a particular backend type, IO target object, IO context combination (e.g.

PostgreSQL Person of the Week Interview with Thom Brown: I was born and grew up in a town called Crawley, which is about half way between London and the south coast of England, and it’s probably best known for the band The Cure, or the comedian Romesh Ranganathan.

Introduction In Adjust we are creating new data types for PostgreSQL, in order to make handling of data easier for us, but also sometimes to save some space. Many of them are written using the C language. Here are some of the more important extensions for us: istore: it is an integer based hstore. In istore both keys and values are represented and stored as integers. pg-base36: a base36 extension, which implements a base36 binary-to-text encoding algorithm.

Thank you to everybody who contributed to the discussion about grants and permissions in PostgreSQL. I understand that my post from two weeks ago was more emotional than factual. Today I will present specific cases of how the flexibility of setting permissions in PostgreSQL might cause problems in production settings.

1. Overview

In my previous blog posts, I explained how the amazing buffer tag works in PostgreSQL and how to set up a shared storage using the Lustre network file system. In this blog, I will explain the storage interface provided by PostgreSQL and an idea to experience the storage interface, namely, smgr, the storage manager.

I will not even attempt to hide it, I’m very much in the learning phase of my PostgreSQL journey. As such, I’m constantly picking up new facts. However, I’m interested in that one thing that you wish someone had told you. That tiny bit of “AH HA!!!” information that you have now, but it took […]

In geospatial terminology, a "raster" is a cover of an area divided into a uniform gridding, with one or more values assigned to each grid cell.

A "raster" in which the values are associated with red, green and blue bands might be a visual image. The rasters that come off the Landsat 7 earth observation satellite have eight bands: red, green, blue, near infrared, shortwave infrared, thermal, mid-infrared and panchromatic.

You can use the lesser-known materialized path model as a more straightforward approach to store trees than the more complex known nested set and adjacency list models. Every row merely stores the materialized path within the tree to itself, making queries for tree searching relatively easy. With PostgreSQL, you'll get a wide range of querying and manipulation functionality from the label tree extension. While for MySQL, you'll have to fallback to text-searching functionalities.

If you upgrade to Django 4.2, you may see system check warnings like:

example.User.email: (fields.W906) django.contrib.postgres.fields.CIEmailField is deprecated. Support for it (except in historical migrations) will be removed in Django 5.1. HINT: Use EmailField(db_collation="…") with a case-insensitive non-deterministic collation instead.

In this post we’ll cover why this has changed, and how to perform the necessary migration. You can actually migrate from Django 3.2, and may want to, because the alternative uses more accurate and flexible Unicode rules.

In the past month at Crunchy Data, we have talked to a steady stream of customers & community folks wanting to know how to augment their data platforms for AI. Fortunately, Postgres is equipped, nearly out of the box, and ready for the task of storing and querying this data. Through the magic of OpenAI’s API we can easily send data for classification and return the values.

Normally, when you drop a column from PostgreSQL, it doesn’t have to do anything to the data in the table. It just marks the column as no longer alive in the system catalogs, and gets on with business.

There is, however, a big exception to this: ALTER TABLE … SET WITHOUT OIDS. This pops up when using pg_upgrade to upgrade a database to a version of PostgreSQL that doesn’t support table OIDs (if you don’t know what and why user tables in PostgreSQL had OIDs, that’s a topic for a different time).

pgec is an Apache licensed real-time in memory PostgreSQL logical replication cache with Redis, Memcached and REST APIs.

In the first security article, PostgreSQL Basics: Roles and Privileges, I discussed how roles (users and groups) are created and managed in PostgreSQL Depending on your background with permissions, particularly in other database products, some of those nuances in how permissions work may have been surprising.

PgManage is a Postgres centered multi-database management Open Source project. It is a fork of the previously well received project OmniDB that had been abandoned. Command Prompt has taken the helm of this project to ensure a quality project focused on the Management of PostgreSQL and related technologies.