How can I use crosstab functons in PostgreSQL 9.3?
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
Rob Richardson <RDRichardson@rad-con.com> writes:
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.
This looks like a search_path problem. You could try "\dx+ tablefunc"
to see which schema its functions are in, then adjust your search_path
to include that, or else schema-qualify the function names.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Looks to me like argument types possibly? The article creates various
combinations of crosstab() function but you are passing in a query. Wrap
your query in quotes (and then escape those within it). Then you'll be
passing in a "text" type not an "unknown" as the error clearly shows.
Tim Clarke
On 15/10/15 15:19, Tom Lane wrote:
Rob Richardson <RDRichardson@rad-con.com> writes:
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.
This looks like a search_path problem. You could try "\dx+ tablefunc"
to see which schema its functions are in, then adjust your search_path
to include that, or else schema-qualify the function names.regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Tim,
Thank you, but I think I already did that. The query is a dollar-quoted string, so there should be no need to do anything with the single quote marks within it, so I would have thought the query engine would already know that it's text. But after seeing the first error message, I explicitly casted it using "::text". The error message that time said that crosstab(text) was not found, so that doesn't seem to be the problem.
RobR
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tim Clarke
Sent: Thursday, October 15, 2015 10:31 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?
Looks to me like argument types possibly? The article creates various combinations of crosstab() function but you are passing in a query. Wrap your query in quotes (and then escape those within it). Then you'll be passing in a "text" type not an "unknown" as the error clearly shows.
Tim Clarke
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I should have mentioned (twice now) that I'm running under Windows 7.
RobR
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, October 15, 2015 10:19 AM
To: Rob Richardson
Cc: pgsql-general General
Subject: Re: [GENERAL] How can I use crosstab functons in PostgreSQL 9.3?
Rob Richardson <RDRichardson@rad-con.com> writes:
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.
This looks like a search_path problem. You could try "\dx+ tablefunc"
to see which schema its functions are in, then adjust your search_path to include that, or else schema-qualify the function names.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
By George, I think I've got it!
When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the crosstab methods and my sample query worked.
RobR
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, Oct 15, 2015 at 10:48 AM, Rob Richardson <RDRichardson@rad-con.com>
wrote:
By George, I think I've got it!
When I ran CREATE EXTENSION tablefunc WITH SCHEMA public, I got the
crosstab methods and my sample query worked.
I would suggest learning about search_path(s) instead of placing everything
into the one schema that happens to be in the default search_path.
Otherwise your "public" is going to be a mess to scan through.
David J.