How to Migrate Oracle's UNPIVOT to PostgreSQL

January 19, 2023

This article looks at the UNPIVOT function in Oracle and how to reproduce its result using PostgreSQL.

1. UNPIVOT in Oracle

2. Replicating UNPIVOT in PostgreSQL

 

Oracle has an UNPIVOT directive, which transposes columns into rows. PostgreSQL doesn't support this syntax or functionality, but we can easily get the same result.

UNPIVOT in Oracle

First, let's look at an example in Oracle. We will create a table with some simple sales data.  We will record the department year and sales amount for each quarter.

CREATE TABLE sales (

    id int PRIMARY KEY,

    department varchar(32),

    year int,

    q1 numeric(10,2),

    q2 numeric(10,2),

    q3 numeric(10,2),

    q4 numeric(10,2)

);



INSERT INTO sales (id, department, year, q1, q2, q3, q4)

VALUES (1, 'fashion', 2019, 43221.33, 22925.39, 51392.90, 79121.65);



INSERT INTO sales (id, department, year, q1, q2, q3, q4)

VALUES (2, 'tech', 2019, 131932.87, 101932.69, 184938.78, 198211.18);

 

Now we will use UNPIVOT to convert the quarterly columns into row values:

SELECT * FROM sales

UNPIVOT (

    amount

    FOR quarter

    IN (

        q1 AS 'Q1',

        q2 AS 'Q2',

        q3 AS 'Q3',

        q4 AS 'Q4'

    )

);



| ID | DEPARTMENT | YEAR | QUARTER |    AMOUNT |

|----|------------|------|---------|-----------|

|  1 |    fashion | 2019 |      Q1 |  43221.33 |

|  1 |    fashion | 2019 |      Q2 |  22925.39 |

|  1 |    fashion | 2019 |      Q3 |   51392.9 |

|  1 |    fashion | 2019 |      Q4 |  79121.65 |

|  2 |       tech | 2019 |      Q1 | 131932.87 |

|  2 |       tech | 2019 |      Q2 | 101932.69 |

|  2 |       tech | 2019 |      Q3 | 184938.78 |

|  2 |       tech | 2019 |      Q4 | 198211.18 |

 

Replicating UNPIVOT in PostgreSQL

To achieve this result in PostgreSQL, we will define an array of values that will act as the values that represent each quarterly column, followed by an array that specifies each of the columns that correspond to those quarters. We will unnest both of those arrays, and that will give us paired values for each quarter and the amount of that quarter.

SELECT

    id,

    department,

    year,

    unnest(array['Q1', 'Q2', 'Q3', 'Q4']) AS quarter,

    unnest(array[q1, q2, q3, q4]) AS amount

FROM

    sales;



| id | department | year | quarter |    amount |

|----|------------|------|---------|-----------|

|  1 |    fashion | 2019 |      Q1 |  43221.33 |

|  1 |    fashion | 2019 |      Q2 |  22925.39 |

|  1 |    fashion | 2019 |      Q3 |   51392.9 |

|  1 |    fashion | 2019 |      Q4 |  79121.65 |

|  2 |       tech | 2019 |      Q1 | 131932.87 |

|  2 |       tech | 2019 |      Q2 | 101932.69 |

|  2 |       tech | 2019 |      Q3 | 184938.78 |

|  2 |       tech | 2019 |      Q4 | 198211.18 |

 

As you can see, we end up with exactly the same result.

An alternative is to use a LATERAL join, providing rows that pair up the value to represent the quarter with the quarterly column:

SELECT

    id,

    department,

    year,

    quarter,

    amount

FROM

    sales,

    LATERAL (VALUES('Q1', q1),('Q2', q2),('Q3', q3),('Q4', q4)) AS unpivot(quarter, amount);



| id | department | year | quarter |    amount |

|----|------------|------|---------|-----------|

|  1 |    fashion | 2019 |      Q1 |  43221.33 |

|  1 |    fashion | 2019 |      Q2 |  22925.39 |

|  1 |    fashion | 2019 |      Q3 |   51392.9 |

|  1 |    fashion | 2019 |      Q4 |  79121.65 |

|  2 |       tech | 2019 |      Q1 | 131932.87 |

|  2 |       tech | 2019 |      Q2 | 101932.69 |

|  2 |       tech | 2019 |      Q3 | 184938.78 |

|  2 |       tech | 2019 |      Q4 | 198211.18 |

 

Note that the term "unpivot" shown in the query text above isn't a function; it's just an arbitrary label to give to the false table. To clarify, the quarter and amount in the SELECT part of the query could be explicitly stated as “unpivot.quarter” and “unpivot.amount.”

There are further ways to achieve equivalent functionality to Oracle's UNPIVOT, but as you can see, PostgreSQL is flexible enough to achieve the same functionality and in various ways.

 

 

Share this

Relevant Blogs

How to run hierarchical queries in Oracle and PostgreSQL

.summary{ background:#f3f7f9; padding:20px; } SUMMARY: This article introduces hierarchical queries and shows the differences in their usage between Oracle and PostgreSQL. 1. Hierarchical query 2. Hierarchical query in Oracle 3. Displaying...
January 24, 2023

More Blogs

Switchover/Failover and Session Migration

I have already covered switchover and failover. In the case of failover, the old primary is offline so there are no options for migrating clients from the old primary to...
January 23, 2023