need help postgresql crosstab query

Question

I have two simple queries as following, which I fetch theme as two record(1 column) from PostgreSQL.

select count(*) from root.str
union all
select count(*) from back.str0

which returns something like this:

+===+
|103|
+===+
|98 |
+===+

But I'd like to have something like this:

+===+===+
|103|98 |
+===+===+

I've tried this, but Postgres raises error at crosstab() function and says the function is not exist.

select * from crosstab(
   'select count(*) from root.str
    union all
    select count(*) from back.str0'::text) as (int r,int e)

Then I suspect you've not installed the `tablefunc` module...? I can't remember from the top of my head how to do it, but I suspect there are answers on how to do it if you search the site. — Joachim Isaksson, Jul 28 '14 at 07:17

score 2 · Accepted Answer · answered Jul 28 '14 at 07:52

2

I don't see why you would need the crosstab module for that:

select (select count(*) from root.str) as str_count,
       (select count(*) from back.str0) as str0_count;

answered Jul 28 '14 at 07:52

score 1 · Answer 2 · edited May 23 '17 at 10:24

1

You need to install the additional module tablefunc which provides the crosstab() function.

Consider this related answer with instructions and more advice:

Also, what @a_horse wrote ...

edited May 23 '17 at 10:24

Community

answered Jul 28 '14 at 07:48

Thanks, unfortunately crosstab functions only accepts physical available tables, it doesn't find temporary or alias selects(`with`). – Jul 28 '14 at 08:02
@user2889419: A **CTE** (`WITH` clause) is only visible in the scope of the query it is attached to, so it has to be part of the query string passed to `crosstab()`. **Temporary tables** are visible in the current session and work just fine with `crosstab()`! – Erwin Brandstetter Jul 28 '14 at 08:42

2 Answers2