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;
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:
tiled_imagecontains a column called “snapshot_id” that matches the “id” column in
tilecontains a column called “tiled_image_id” that matches the “id” column in
image_file_tablecontains 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).
INNER JOIN function brings all these together into the query result table of
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 (
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
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).