String Concatenation with PostgreSQL Functions

This post demonstrates using concatenation in PostgreSQL, but it’s main purpose is to remind you of the ton of PostgreSQL functions out there. We’ll be using one of PostgreSQL’s aggregate functions, string_agg(expression, delimiter).  Here is a link to PostgreSQL Functions and Operators if you want to be dazzled by a table of contents that goes on forever.

In the NixMashup Links app I’m building the tags to individual links are stored in a typical fashion, with a single entry per tag and a secondary relationship table between the tag and the links they are associated with. Before we look at that, let’s see what we want to produce.

Here’s the layout of the two tag tables I described earlier displayed in Navicat Design View.

Our aggregate function will use the following virtual table produced from a PostgreSQL view.

Now for the cool part, using the PostgreSQL string_agg() aggregate function.

In case you missed it the first time, here’s out result. This stuff is way too easy with PostgreSQL functions.