Greenplum Computed Column Support and Alternative

  • Post author:
  • Post last modified:March 22, 2018
  • Post category:Greenplum
  • Reading time:3 mins read

Derived or computed columns in Greenplum are columns that are derived from the previously computed columns in same table. These computed columns are virtual columns that are not physically stored in the Greenplum table. Their values are re-calculated every time they are referenced in a query. Many relational databases such as Netezza supports derived or computed columns but Greenplum does not support as of now. Development work is going on to include this feature in upcoming ProsgreSQL release. In this article, we will check Greenplum Computed Column Support and alternative method that you can use to derive columns.

What are Computed Columns?

Before going in details into computed columns, first, understand what computed column is and how they are derived in Greenplum. To understand this concept, let us consider an SQL example.

Consider below SQL query:

select col1,
col2*20 as col2_1,
Col2_1*200 as col3_1
from your_table;

As you can see in above SQL query example, col3_1 uses previously computed column col2_1 for its calculations. Col3_1 is called computed columns and this type of column reference is not supported in Grenplum as of now. This feature may be added to future PostgreSQL release.

Greenplum Computed Column Alternative

If you get requirement to create derive the columns from the previously computed column then you will have to find alternate method to derive those columns. As an alternative to computed columns, you can use Greenplum WITH clause or Common Table Expressions (CTE). This method is easy and fastest way to derive or compute columns in Greenplum.

Greenplum Computed Column Alternative Examples

As mentioned in previous section, you can use the Greenplum WITH clause or common table expression as an alternative method to compute or derive column in same table.

Below is the example that uses WITH clause to compute or derive columns:

with CTE1 as (
select col1,
col1*10 as col2_1
from table1)
select a.col1,
b.col2_1,
b.col2_1*100 as col3_1
from table1 a
join CTE1 b
on(a.col1 = b.col1);

This is the easy and acceptable method till computed column feature is available in PostgreSQL.

Related reading:

Tags: