Postgres OnLine Journal

http extension for windows updated to include PostgreSQL18 64-bit

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 16 Oct 2025 23:03:00 -0400

Updated October 16th, 2025 64-bit package for PostgreSQL 18 http extension v1.7.0 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 already have since things like the libcurl and PCRE are also packaged with PostGIS.

Continue reading "http extension for windows updated to include PostgreSQL18 64-bit"

Learning PostgreSQL from AI and JSON exploration: Part 2

nospam@example.com (Leo Hsu and Regina Obe) — Thu, 21 Aug 2025 02:05:00 -0400

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.

Continue reading "Learning PostgreSQL from AI and JSON exploration: Part 2"

FROM function or SELECT function

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 25 Apr 2025 20:47:00 -0400

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"

Converting JSON documents to relational tables

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 28 Mar 2025 00:03:00 -0400

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 documents to relational tables"

PG 17 new random functions

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 05 Mar 2025 22:45:00 -0500

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"

Learning PostgreSQL from AI and JSON exploration

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 10 Feb 2025 20:48:00 -0500

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.

Continue reading "Learning PostgreSQL from AI and JSON exploration"

Unpivoting data using JSONB

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 19 Jan 2025 22:57:00 -0500

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

Continue reading "Unpivoting data using JSONB"

PostgreSQL 17 64-bit for Windows FDWs

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 23 Dec 2024 03:50:00 -0500

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.

Continue reading "PostgreSQL 17 64-bit for Windows FDWs"

Substring function Regex style

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 01 Dec 2024 16:37:00 -0500

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"

Some of my favorite PostgreSQLisms

nospam@example.com (Leo Hsu and Regina Obe) — Mon, 25 Nov 2024 23:01:00 -0500

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"

PostgreSQL Mistakes and How To Avoid them

nospam@example.com (Leo Hsu and Regina Obe) — Tue, 05 Mar 2024 12:43:00 -0500

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.

Continue reading "PostgreSQL Mistakes and How To Avoid them"

PostgreSQL 16 64-bit for Windows FDWs

nospam@example.com (Leo Hsu and Regina Obe) — Sun, 03 Sep 2023 00:13:00 -0400

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.

Continue reading "PostgreSQL 16 64-bit for Windows FDWs"

VARIADIC Unnest

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 23 Nov 2022 00:26:00 -0500

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.

Continue reading "VARIADIC Unnest"

PostgreSQL 15 64-bit for Windows FDWs

nospam@example.com (Leo Hsu and Regina Obe) — Fri, 28 Oct 2022 22:44:00 -0400

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

Continue reading "PostgreSQL 15 64-bit for Windows FDWs"

Creating cumulative sums by combining aggregation with windowing

nospam@example.com (Leo Hsu and Regina Obe) — Wed, 30 Mar 2022 15:14:00 -0400

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.

Continue reading "Creating cumulative sums by combining aggregation with windowing"