Tips and tricks for PostgreSQL
As stated in Part 1, pgAgent is going away , so I m focussing on setting up pg_timetable as similar to pgAgent that I can. For this second part, I m going to go over how to configure pg_timetable as a service on Linux. A heads up, i really loved the pgAgent UI in pgAdmin so I m working on one for pg_timetable which is mostly patterned after the pgAgent one. You can see the pull request work in…
Updated June 21st, 2026 64-bit package for PostgreSQL 19 http extension v1.7.1 release . For those folks on windows who want to do http gets and posts directly from your PostgreSQL server, we ve made binaries for the http extension for PostgreSQL Windows. These are designed to work with PostgreSQL EDB windows distributions. If you have PostGIS already installed, many of these files you will also…
pgAgent has been my go to scheduling solution for quite some time. Sadly in 6 months it will be completely retired and the pgAgent UI in pgAdmin will be gone. The main reasons I liked pgAgent were: Cross Platform: I have a lot of windows and linux customers, so this was important. Nice UI in pgAdmin, so I could do all work with PostgreSQL and schedule things at the same time as well as check…
This is the second part of the series I started on Learning PostgreSQL from AI and JSON exploration: Part 1 . For this 2nd part, I decided to try gpt-oss the 14GB model which was just released in the past week. My first impression, "When will this ai shut up about its thinking process?". Phi4 on the other hand gave a bit of a narrative about how it came up with answers but it did not write a novel…
When I have set returning functions and even non-set returning functions, I love putting them in the FROM clause instead of the SELECT part. I often take it for granted that the results are usually the same, but in some important situations, they are different. It s not guaranteed to be the same when your function is not immutable. Continue reading "FROM function or SELECT function"
JSON is one of the most popular ways of disseminating data between systems. It is probably the most common offered by webservices. PostgreSQL is a database perfectly suited for grabbing that data and transforming it into a more structured relational format. All this can be done directly in the database. We ll go over some ways to load and restructure json data. Continue reading "Converting JSON…
Have you ever wanted to get a random integer between 1 and 10 and been a little annoyed the slightly cryptic code you had to write in PostgreSQL? PostgreSQL 17 random functions make that simpler. Sometimes it s the small changes that bring the most joy. Continue reading "PG 17 new random functions"
After reading Paul s teasing article on Accessing Large Language Models with AI and testing out his code, I decided I should probably stop fighting this AI smothering. I still have a distrust of AI services but downloading an AI model and using on my own local desktop or server is nice and I can break bread with that. One use I tried is using AI to generate fake data and it did a pretty decent…
One of my favorite uses of JSONB functionality is to unpivot data. Continue reading "Unpivoting data using JSONB"
We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 17 Windows 64-bit. To use these, copy the files into your PostgreSQL 17 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt These were compiled against PostgreSQL 17.2 using msys2 / mingw64 and tested…
I was reviewing some old code when I stumbled across something I must have completely forgotten or someone else some time ago knew. That is that it is possible to use the function substring for regular expression work. Continue reading "Substring function Regex style"
When I work with other relational databases I am reminded about how I can t use my favorite PostgreSQL hacks in them. I call these hacks PostgreSQLisms. A PostgreSQLism is a pattern of SQL unique to PostgreSQL or descendants of PostgreSQL. In this post I m going to cover some of my favorite ones. Continue reading "Some of my favorite PostgreSQLisms"
The adoption of PostgreSQL is growing each year. Many people coming to PostgreSQL are often coming from other relational databases, with assumptions of how relational databases work. Although PostgreSQL may feel very familiar to these people, it is different enough to cause some misunderstandings which lead to bad and slow queries. There are also people coming often from only programming…
We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 16 Windows 64-bit. To use these, copy the files into your PostgreSQL 16 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt These were compiled against PostgreSQL 16rc1, but should work fine against…
PostgreSQL keeps on adding new great stuff. It s hard to remember all the good stuff that has been added over the years. One of the neat ones from the past is the variadic Unnest function which I believe was introduced in PostgreSQL 9.4. It s rare that I ever had to use it, but today I was handed some data where this function was just literally what the doctor ordered. I can t do anything this…