Projection Tables

Jim Walker
Sep 22, 2015 · 4 min read



This post will walk through a simple scenario where creating a projection table might be beneficial. It will discuss the challenges and propose relative solutions.


For starters, let’s suppose there are multiple satellites that are sending down telemetry every day to a ground system on Earth. The ground system stores each of those states in a log.

The log data is useful for analyzing trends over time. Successfully monitoring these trends can prevent equipment damage. So being able to query effectively is important.

Example Data

Here is an example log containing hypothetical telemetry data.

The desired pivot table would have the Mnemonic column as the table’s headers while its data is populated from the associated Values to the Satellite_id and Mnemonic. The desired pivot table is shown below.

Initial Thought

An initial thought might be to start with creating a table with the Mnemonics as column headers. Then going through each row and constructing an insert statement.

Although this method works, Postgres has a built-in function that can build our table for us.

Postgres’ Crosstab function

Postgres has a convenient function called crosstab which is contained in the TableFunc module.

crosstab(text sql)

This is a good start, but it would be nice to find the Satellite that has a TMP_1 > 550. However, attempting to do so would result in the following error:

No operator matches the given name and argument type(s). You might need to add explicit type casts.

The “No operator” error occurs because you cannot do a comparison on an integer type with a text type. To explicitly change the column types from text to integer, the following query can be executed:

Unfortunately, setting some of the column types to integers results in another error:

return and sql tuple descriptions are incompatible

However this error can be resolved if we examine alternative TableFunc functions. Accordingly, crosstab(text sql, text sql) documentation states:

The main limitation of the single-parameter form of crosstab is that it treats all values in a group alike, inserting each value into the first available column. If you want the value columns to correspond to specific categories of data, and some groups might not have data for some of the categories, that doesn’t work well. The two-parameter form of crosstab handles this case by providing an explicit list of the categories corresponding to the output columns.

Therefore, the correct crosstab function to use is crosstab(text source_sql, text category_sql). The source_sql argument is a query for all the source data. The category_sql argument is a query for all the column headers. To make sure that the correct order of columns and data are preserved, `ORDER BY 1` is used in each statement.

The query normalizes the data and allows for additional queries or aggregations. As a result, we can now allow for more advanced queries on log data. Below is an example of such a query.


There are many different applications that can take advantage of crosstabs. This post offers a simple example on how we can apply advanced logged data queries through the medium of a real world scenario.

Namely is hiring

Namely Labs

Namely Engineering + Design

Namely Labs

Namely Engineering + Design

Jim Walker

Written by


Namely Labs

Namely Engineering + Design