Neues vom PostgreSQL Planet
Jacob Coblentz: Geocoding with Web APIs in Postgres
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.
Christophe Pettus: Workers of the World, Unite!
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:
Henrietta Dombrovskaya: March Chicago PUG and other updates
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.
Ryan Booz: PGSQL Phriday #006
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.
Hans-Juergen Schoenig: Stored procedures in PostgreSQL: getting started
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 basicsIn PostgreSQL stored procedures have been around for a number of years. The syntax of this important command is defined as follows:
Paolo Melchiorre: Citus Con 2023
An Event for Postgres is a free and virtual developer event organized by the Postgres team at Microsoft Azure.
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 16 – Add pg_stat_io view, providing more detailed IO statistics
Andreas 'ads' Scherbaum: Thom Brown
Artur Zakirov: Creating new PostgreSQL enum data types using pg_type_template
Henrietta Dombrovskaya: Can we make permissions management more user-friendly?
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.
David Z: The smgr interface in PostgreSQL
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.
Grant Fritchey: One Thing You Wish You Knew While Learning PostgreSQL: PGSqlPhriday #006
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 […]
Paul Ramsey: Postgres Raster Query Basics
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.
Tobias Petry: Store Trees As Materialized Paths
Adam Johnson: How to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation
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.
Christopher Winslett: What's Postgres Got To Do With AI?
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.
Christophe Pettus: ALTER TABLE … SET WITHOUT OIDS big gotcha
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).
Peter Morgan: Consistent caching with PostgreSQL logical replication and a Redis API
pgec is an Apache licensed real-time in memory PostgreSQL logical replication cache with Redis, Memcached and REST APIs.
Ryan Booz: PostgreSQL Basics: Object Ownership and Default Privileges
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.
Joshua Drake: Announcing PgManage 1.0a
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.