Friday, June 6, 2008

Re: [SQL] crosstab functions in postgres 8.1

"Chris Preston" <chris@thetrafalgartravel.com> writes:
> I am almost a newbie to postgres and only recently found out about one of
> the greatest function crosstab in postgres. The problem is that I am using
> 8.1 and when I run the function I am getting an error relating to
> $libdir/tablefunc.. someone mentioned that it might be because I am using
> ver 8.1 and not 8.3

Well, since you didn't supply the exact error message, this is just a
guess; but it sounds like you haven't got the contrib/tablefunc module
installed in your 8.1 database, or at least haven't got it installed
correctly. You need a tablefunc.so file that is compiled for 8.1
(copying the 8.3 version will almost certainly NOT work).

regards, tom lane

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

1 comment:

Markus said...

I love the crosstab functionality in postgres (8.4) and have used it quite a bit but I am encountering a puzzling duplication of row headers. I have created the following small example that illustrates the problem:

CREATE TABLE day10
(
userid double precision,
sType integer
);
INSERT INTO day10(userid, sType) VALUES(1234, 0);
INSERT INTO day10(userid, sType) VALUES(1234, 0);
INSERT INTO day10(userid, sType) VALUES(1234, 0);
INSERT INTO day10(userid, sType) VALUES(1234, 0);
INSERT INTO day10(userid, sType) VALUES(1234, 1);
INSERT INTO day10(userid, sType) VALUES(1234, 0);
INSERT INTO day10(userid, sType) VALUES(1234, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 1);
INSERT INTO day10(userid, sType) VALUES(4321, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 1);
INSERT INTO day10(userid, sType) VALUES(4321, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 0);
INSERT INTO day10(userid, sType) VALUES(4321, 0);

SELECT sTypereport.* FROM crosstab('SELECT userid, sType, count(*) as counts from day10 GROUP BY userid, sType;', 'select * from generate_series(0,2);') As sTypereport(userid double precision, type1 integer, type2 integer, type3 integer);

userid | type1 | type2 | type3
--------+-------+-------+-------
1234 | 6 | |
4321 | 7 | 2 |
1234 | | 1 |

I expected crosstab to merge the counts for userid 1234 into one !

Any help would be greatly apreciated,
Thanks,
Markus