PostgreSQL: Add columns to a query table with variables

Generate a new column in your PostgreSQL database query from data in other columns or other tables.

I recently had the need to add a column to a PostgreSQL database query so that I could return the file path of an image associated with each row of data.

The table that I was querying was called snapshot. Part of the file path for the associated image related to each row of data was in a separate table called image_file_table. But to link that data, I had to also make queries from two other tables; tile and tiled_image. In effect, I was linking four tables together in a single query.

This sounds tricky but isn’t. It requires three INNER JOIN functions (one for each table) and a qualified SELECT function. Let’s take a look:

SELECT *, '/path/to/files/' || image_file_table.path AS FilePath 
FROM snapshot
INNER JOIN tiled_image ON snapshot.id = tiled_image.snapshot_id
INNER JOIN tile ON tiled_image.id = tile.tiled_image_id
INNER JOIN image_file_table ON tile.raw_image_oid = image_file_table.id
WHERE experiment_name = 'Experiment10';

We’ll look at the INNER JOIN functions first:

• Table tiled_image contains a column called “snapshot_id” that matches the “id” column in snapshot.
• Table tile contains a column called “tiled_image_id” that matches the “id” column in tiled_image.
• Table image_file_table contains a column called “id” that matches the “raw_image_oid” column in tile. This also returns a column called “path” that contains the information that I want (ie a partial file path).

The INNER JOIN function brings all these together into the query result table of snapshot.

The problem for me was that the resultant column “path” (that came from image_file_table) contained ‘incomplete’ data: it only contained a relative file path. What I needed to do was append a directory path to the front of it to give me the full result.

This is achieved with the SELECT... AS functions.

In PostgreSQL, it’s possible to use a double-pipe to link variables and text strings together. In this case, I wanted to concatenate a file path (a constant string) to the content in the “path” column in a new column called “FilePath”.

SELECT *, '/path/to/files/' || image_file_table.path as FilePath 

Here, we are saying that we want to select all columns (*) from snapshot and in a new “FilePath” column, add a text string (“/path/to/files/”) to the result in the “Path” column. The double pipe (||) separates these. Since the “path” column comes from the image_file_table table, it needs to be expressed as image_file_table.path.

Now when I run my query, I have a result table that contains a “FilePath” column with a full image path (eg: “/path/to/files/78246382/38401.JPG”). Easy!

You can use this technique to concatenate all sorts of data from all sorts of tables in PostgreSQL without needing to use complex code to declare variables (which is messy in PostgreSQL).

Have Your Say

Δ