How can I use crosstab functons in PostgreSQL 9.3?

From: Rob Richardson <RDRichardson(at)rad-con(dot)com>
To: pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: How can I use crosstab functons in PostgreSQL 9.3?
Date: 2015-10-15 14:14:02
Message-ID: 67D108EDFAD3C148A593E6ED7DCB4BBD014F1A14EA@RADCONWIN2K8PDC.radcon.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am trying to learn about crosstab functions in ProgreSQL 9.3, but none of the examples I’ve found are working. I get errors claiming the functions are unknown, but when I try running CREATE EXTENSION tablefunc, I am told that its methods already exist.

For example, I am trying to run the code contained on this page: https://learnerspeak.wordpress.com/2012/09/02/97/ . After adjusting quotation marks, my crosstab query from that example is:

SELECT *
FROM crosstab(
$$select rowid, attribute, value
from ct
where attribute = 'att2' or attribute = 'att3'
order by 1,2$$)
AS ct(row_name text, category_1 text, category_2 text, category_3 text);

That query gives me the following error message:
ERROR: function crosstab(unknown) does not exist
LINE 2: FROM crosstab(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function crosstab(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

I don’t know why it thinks the argument’s type is unknown. But if I explicitly cast it to text, I get:
ERROR: function crosstab(text) does not exist
LINE 2: FROM crosstab(
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
********** Error **********

ERROR: function crosstab(text) 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

Thank you for your help.

RobR

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2015-10-15 14:16:00 Re: Installing plpython3u
Previous Message Dario Beraldi 2015-10-15 14:11:43 Re: Installing plpython3u