Univot and PostgreSQL

Question

Is there a non-equivalent function in PostgreSQL?

Answer 1

Create a sample table:

CREATE TEMP TABLE foo (id int, a text, b text, c text); INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil'); 

With UNION ALL, you can disable or uncrosstab:

 SELECT id, 'a' AS colname, a AS thing FROM foo UNION ALL SELECT id, 'b' AS colname, b AS thing FROM foo UNION ALL SELECT id, 'c' AS colname, c AS thing FROM foo ORDER BY id; 

This starts 3 different subqueries on foo , one for each column that we want to disable, and returns in a single table each record from each of the subqueries.

But this scans the table N times, where N is the number of columns you want to disable. This is inefficient and a big problem when, for example, you are working with a very large table, which takes a lot of time to scan.

Use instead:

 SELECT id, unnest(array['a', 'b', 'c']) AS colname, unnest(array[a, b, c]) AS thing FROM foo ORDER BY id; 

It is easier to write and it will only scan once.

array[a, b, c] returns an array object with the values ​​a, b and c as its elements. unnest(array[a, b, c]) splits the results on one line for each of the elements of the array.

Hope this helps!

Answer 2

You can use VALUES() and JOIN LATERAL to disable columns.

Sample data:

 CREATE TABLE test(id int, a INT, b INT, c INT); INSERT INTO test(id,a,b,c) VALUES (1,11,12,13),(2,21,22,23),(3,31,32,33); 

inquiry:

 SELECT t.id, s.col_name, s.col_value FROM test t JOIN LATERAL(VALUES('a',ta),('b',tb),('c',tc)) s(col_name, col_value) ON TRUE; 

DBFiddle Demo

Using this approach, you can disable multiple column groups at the same time.

EDIT

Using Zach's suggestion:

 SELECT t.id, col_name, col_value FROM test t CROSS JOIN LATERAL (VALUES('a', ta),('b', tb),('c',tc)) s(col_name, col_value); <=> SELECT t.id, col_name, col_value FROM test t ,LATERAL (VALUES('a', ta),('b', tb),('c',tc)) s(col_name, col_value); 

db & lt;> fiddle demonstration

Answer 3

FYI is for those of us who are looking for how to disconnect in RedShift.

The long form solution given by Stew seems to be the only way to achieve this.

https://forums.aws.amazon.com/thread.jspa?threadID=126369

---

For those who do not see it, here is the text inserted below ...

We do not have built-in functions that will perform a turn or a turn. However, you can always write SQL for this.

 create table sales (regionid integer, q1 integer, q2 integer, q3 integer, q4 integer); insert into sales values (1,10,12,14,16), (2,20,22,24,26); select * from sales order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows) 

summary request

 create table sales_pivoted (regionid, quarter, sales) as select regionid, 'Q1', q1 from sales UNION ALL select regionid, 'Q2', q2 from sales UNION ALL select regionid, 'Q3', q3 from sales UNION ALL select regionid, 'Q4', q4 from sales ; select * from sales_pivoted order by regionid, quarter; regionid | quarter | sales ----------+---------+------- 1 | Q1 | 10 1 | Q2 | 12 1 | Q3 | 14 1 | Q4 | 16 2 | Q1 | 20 2 | Q2 | 22 2 | Q3 | 24 2 | Q4 | 26 (8 rows) 

originality request

 select regionid, sum(Q1) as Q1, sum(Q2) as Q2, sum(Q3) as Q3, sum(Q4) as Q4 from (select regionid, case quarter when 'Q1' then sales else 0 end as Q1, case quarter when 'Q2' then sales else 0 end as Q2, case quarter when 'Q3' then sales else 0 end as Q3, case quarter when 'Q4' then sales else 0 end as Q4 from sales_pivoted) group by regionid order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows) 

Hope this helps, Neil

Answer 4

I wrote a terrible univot function for PostgreSQL. It is pretty slow, but it at least returns results as you would expect from a no operation.

https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/

Hope you can find this helpful.

Answer 5

Retrieving slightly modified content from a link in a comment from @a_horse_with_no_name in response, because this works:

Install Hstore
If you do not have hstore installed and you are using PostgreSQL hstore , you can use the convenient

CREATE EXTENSION hstore;

For lower versions, find the hstore.sql file in share/contrib and run it in your database.

Assuming your source table (for example, wide data) has one id column named id_field and any number of value columns of the same type, an unconverted representation of this table will be created from the following.

 CREATE VIEW vw_unpivot AS SELECT id_field, (h).key AS column_name, (h).value AS column_value FROM (SELECT id_field, each(hstore(foo) - 'id_field'::text) AS h FROM zcta5 as foo ) AS unpiv ; 

This works with any number of 'value' columns. All received values ​​will be textual if you did not cast, for example, (h).value::numeric .