Postgres OnLine Journal   

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 already have since things like the libcurl and PCRE are also packaged with PostGIS.

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 about it and it also seemed more personable, using "My and you" instead of a distanced "User wants to". However gpt-os did become less chatty with a bit of coaxing. So anyway personality wise I prefer phi4. That is not to say that Gpt-oss doesn't have some wow factor. It created perfect SQL each time. I did not need to correct it. One of the other neat features of this model which I haven't had time to explore is ability to have it do browser searches and other agentic like stuff.

Again for this size model, speed of answering with my crappy graphics card and processor was not bad, but it was noticeably slower than Phi4. Given the impressive accuracy, I didn't mind too much and I suspect can be improved by tweaking some of its settings.

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.

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.

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.

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 job. I also learned a couple of things.

One of my favorite uses of JSONB functionality is to unpivot data.

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 against PostgreSQL 17.2 EDB windows distribution.

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.

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.

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 backgrounds, who assume data processing in SQL is much like data processing in any language. For these two groups of folks, I think the new book written by EDB and 2nd Quadrant Author, Jimmy Angelakos, "PostgreSQL Mistakes and How To Avoid Them" will save them a lot of miss-steps.

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 EDB PostgreSQL 16beta3.

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 sweet in other databases I have used.

We are pleased to provide binaries for file_textarray_fdw and odbc_fdw for PostgreSQL 15 Windows 64-bit.

To use these, copy the files into your PostgreSQL 15 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

When you want to include a running sum for each line of data in your query, you generally use a window clause with SUM. What if you don't want to list all line items of your data. You want a report that gives you a weekly sum and another that gives you the running sum for the whole year. What do you do then? We'll demonstrate how to do that.