0

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)
Improve this question
4
  • Which version of postgresql are you using? Commented Jul 28, 2014 at 7:10
  • @JoachimIsaksson it's 9.2.1 bro Commented Jul 28, 2014 at 7:11
  • 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. Commented Jul 28, 2014 at 7:17
  • create extension tablefunc Commented Jul 28, 2014 at 8:14

2 Answers 2

Reset to default
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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

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 ...

Improve this answer

2 Comments

Thanks, unfortunately crosstab functions only accepts physical available tables, it doesn't find temporary or alias selects(with).
@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()!