Thread: BUG #14024: problem in Creating pivot table

The following bug has been logged on the website:

Bug reference:      14024
Logged by:          AmrFadly
Email address:      amr.fadly@vodafone.com
PostgreSQL version: 9.5.0
Operating system:   Windows 7
Description:

we are trying to do the example in the documentation to create pivot table
using crosstab function, it is not working and gives the below error:

ERROR: function crosstab(unknown, unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need
to add explicit type casts.
Character: 15

the example is:

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March
2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab('SELECT rowid, rowdt, attribute, val FROM cth ORDER
BY 1','SELECT DISTINCT attribute FROM cth ORDER BY 1')
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);

amr.fadly@vodafone.com writes:
> we are trying to do the example in the documentation to create pivot table
> using crosstab function, it is not working and gives the below error:

> ERROR: function crosstab(unknown, unknown) does not exist

crosstab() is in the tablefunc extension, which you evidently haven't
installed (basically "CREATE EXTENSION tablefunc").  See the general
installation instructions at

http://www.postgresql.org/docs/9.5/static/contrib.html

            regards, tom lane

On 03/16/2016 06:56 AM, amr.fadly@vodafone.com wrote:
> The following bug has been logged on the website:
>=20
> Bug reference:      14024
> Logged by:          AmrFadly
> Email address:      amr.fadly@vodafone.com
> PostgreSQL version: 9.5.0
> Operating system:   Windows 7
> Description:=20=20=20=20=20=20=20=20
>=20
> we are trying to do the example in the documentation to create pivot table
> using crosstab function, it is not working and gives the below error:
>=20
> ERROR: function crosstab(unknown, unknown) does not exist
> SQL state: 42883
> Hint: No function matches the given name and argument types. You might ne=
ed
> to add explicit type casts.
> Character: 15

You should not assume this is a bug until proven it is not
misconfiguration. Next time, unless you are sure, please ask for help on
the general or novice list first.

I'll bet the extension is not installed. Please run \dx from psql. If
you don't see tablefunc among the listed installed extensions, then you
need to create it.

HTH,

Joe

--=20
Crunchy Data - http://crunchydata.com
PostgreSQL Support for Secure Enterprises
Consulting, Training, & Open Source Development