Transpose PostgreSQL table or aggregation function doing nothing

Question

I've got a PostgreSQL table which I have to transpose.
The original data looks like:

user_id role_id assigned
P0001   SA      t
P0001   Guest   t
P0002   SA      f
P0002   Guest   t

After transposing, it should look like as follows:

user_id SA  Guest
P0001   t   t
P0002   f   t

The following is what I used to generate the transposed table:

SELECT user_id, 
CAST(CAST(CASE role_id WHEN 'SA' THEN 1 ELSE 0 END) AS int) AS boolean) AS "SA",
CAST(CAST((CASE role_id WHEN 'Guest' THEN 1 ELSE 0 END) AS int) AS boolean) AS "Guest" 
FROM user_to_roles GROUP BY user_id

But it looks ugly. Is there a way to write an custom aggregation function doing nothing but returning the original value. So the above statement can be re-written as:

SELECT user_id, 
Do_Nothing(CASE role_id WHEN 'SA' THEN true ELSE false END) AS "SA",
Do_Nothing(CASE role_id WHEN 'Guest' THEN true ELSE false END) AS "Guest" 
FROM user_to_roles GROUP BY user_id

Edit:

Actually, I need a dynamic generated columns and implemented it using the cursor by the help of this article. And I looked into the documentation and write a custom aggregation like this:

CREATE AGGREGATE do_nothing(anyelement) (
  SFUNC=donothing,
  STYPE=anyelement,
  INITCOND=''
);

CREATE OR REPLACE FUNCTION donothing(anyelement var)
  RETURNS anyelement AS
$BODY$
    RETURN var;
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

Since I want to make it generic, anyelment was used here.
But the above code doesn't work at all.

"*Is there a way to write an custom aggregation*" - yes that should be possible: http://www.postgresql.org/docs/current/static/sql-createaggregate.html Search the Postgres Wiki. I think there are some examples there. You also might want to look into the `crosstab` function: http://www.postgresql.org/docs/current/static/tablefunc.html — , Aug 21 '14 at 07:06
This is a table pivot, or crosstab, and it's a bit of an FAQ. Partly because it's annoyingly ugly and hard to do in a general way in PostgreSQL. — Craig Ringer, Aug 21 '14 at 07:10
Cross-tabulation / Pivoting is a completely different concept than aggregation. — Erwin Brandstetter, Aug 21 '14 at 15:14

score 1 · Answer 1 · answered Aug 21 '14 at 07:43

1

In your particular case you can get away with using a built-in aggregate bool_or. Something like:

SELECT user_id, 
bool_or(CASE role_id WHEN 'SA'    THEN assigned ELSE false END) AS "SA",
bool_or(CASE role_id WHEN 'Guest' THEN assigned ELSE false END) AS "Guest" 
FROM user_to_roles GROUP BY user_id

answered Aug 21 '14 at 07:43

Richard Huxton

21,516
3
39
51

Thanks for your help. Since I want to do it in generic way, is there a similar aggregation function applied to string and int? – user3496167 Aug 21 '14 at 07:54
1

For int, you could use `sum` and an alternate value of 0. For text `string_agg` and the empty string. – Richard Huxton Aug 21 '14 at 10:22
1

@user3496167: Use `ELSE NULL`, then either of the aggregate functions (min, max, sum) works with *any* type. I added a code example to my answer. – Erwin Brandstetter Aug 21 '14 at 16:36
@user3496167: I was promising too much. `min()` and `max()` work for *most* basic types, but not for `boolean`. I added more to my answer. – Erwin Brandstetter Aug 22 '14 at 05:32

score 1 · Accepted Answer · edited May 23 '17 at 12:21

`crosstab()`

SELECT * FROM crosstab(
       'SELECT user_id, role_id, assigned
        FROM   tbl
        ORDER  BY 1,2'

      ,$$VALUES ('SA'::text), ('Guest')$$)
AS ct (user_id text, "SA" bool, "Guest" bool);

That's as fast as it gets with current versions of Postgres. (Faster than alternatives below.)

PostgreSQL Crosstab Query

But neither of these solutions is completely "dynamic", which is not completely possible:

Dynamic alternative to pivot with CASE and GROUP BY

For a completely dynamic solution, you would have to output arrays or some other container type to return a variable number of columns. Recent related question with answers:

Looping in select query

Poor man's crosstab with aggregated `CASE` expressions

For just a few columns: simple CASE statements combined with an aggregate function.

min() or max() work for most basic types ... per documentation:

any array, numeric, string, or date/time type

But not for boolean. Use bool_or() like @Richard suggested.
FALSE OR NULL would yield NULL, but all mentioned aggregate functions (incl. bool_or()) ignore NULL, so it works with NULL as well.

SELECT user_id
      ,bool_or(CASE WHEN role_id = 'SA'    THEN assigned ELSE NULL END) AS "SA"
      ,bool_or(CASE WHEN role_id = 'Guest' THEN assigned ELSE NULL END) AS "Guest"
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

You have to adapt to the data type expected.

Custom aggregate for any type

There is no standard aggregate function that works for any data type in the way you need it. You can create one:

CREATE OR REPLACE FUNCTION f_echo1(anyelement,anyelement)
  RETURNS anyelement AS 'SELECT $1' LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE first_notnull(anyelement) (
   SFUNC = f_echo1
 , STYPE = anyelement
);

This is a simple, working version of what you outlined in your edit:
Starting with NULL (no INITCOND), using a STRICT transition function, it takes the first not-null value and the transition function just keeps that one. So we get the first not-null value back the aggregate function encounters.

This works for any input type:

SELECT user_id
      ,first_notnull(CASE WHEN role_id = 'SA'    THEN assigned ELSE NULL END) AS "SA"
      ,first_notnull(CASE WHEN role_id = 'Guest' THEN assigned ELSE NULL END) AS "Guest"
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

Copy `bool_or()` as `max()`

Custom aggregate functions in sql or plpgsql are considerably slower than the built-in ones, which are written in C and optimized. You could write one in C yourself. Wouldn't be that hard. Or you can copy the definition of bool_or under the name of max in pg_proc and reference it in another copy in pg_aggregate to make max() work for boolean as well. Messing with catalog tables is not recommended for the casual user, though. If you break something your system might go belly-up ...

It seems that MAX doesn't work with boolean. It throws an exception stating that function max(boolean) does not exist. It works fine with string and null. Any suggestions? — user3496167, Aug 22 '14 at 03:28
I tried the custom aggregation function, now it works a like a charm. Thanks a lot. — user3496167, Aug 22 '14 at 06:36

Transpose PostgreSQL table or aggregation function doing nothing

Edit:

2 Answers2

crosstab()

Poor man's crosstab with aggregated CASE expressions

Custom aggregate for any type

Copy bool_or() as max()

`crosstab()`

Poor man's crosstab with aggregated `CASE` expressions

Copy `bool_or()` as `max()`