Unable to use crosstab in Postgres
Solution 1
You can change the first line into:
CREATE EXTENSION IF NOT EXISTS tablefunc;
Solution 2
the problem in my case was that the 'tablefunc' extension was defined on one specific schema in my DB, and not accessible to all schemas in it.
[edit: as explained above, 'not accessible to all schemas' should read 'cannot be loaded on all schemas']
I learned that:
- the Extension can only be loaded into one schema - so load it into 'public'
- you have to manually drop the extension from one schema before you can load it in another
- you can list the loaded extensions per schema in pqsl using the command:
\df *.crosstab
[edit: 4. you can access the extension either by search_path, by loading it on public schema or by explicitly specifying a schema]
Solution 3
There's a misconception in your answer:
and not accessible to all schemas in it.
All schemas inside the same database are accessible to all sessions in that same database, (as long as privileges are given). It's a matter of setting the search_path
. Schemas work much like directories / folders in the file system.
Alternatively, you can schema-qualify the function (and even operators) to access it independently of the search_path
:
SELECT *
FROM my_extension_schema.crosstab(
$$select rowid, attribute, "value"
from ct
where attribute IN ('att2', 'att3')
order by 1,2$$
,$$VALUES ('att2'), ('att3')$$
) AS ct(row_name text, category_2 text, category_3 text);
Recent related answer with more Info:
How to use % operator from the extension pg_trgm?
Dubious crosstab()
Your query returned attributes 'att2'
and 'att3'
, but the column definition list had three categories (category_1, category_2, category_3
) that do not match the query.
I removed category_1
and added the second parameter to crosstab() - the "safe" version. More details here:
PostgreSQL Crosstab Query
Aside: Don't use value
as column name. Even if Postgres tolerates it. It's a reserved word in standard SQL.
Black
Updated on December 05, 2020Comments
-
Black over 3 years
Postgres 9.2.1 on OSX 10.9.2.
If I run the following crosstab example query:
CREATE EXTENSION tablefunc; CREATE TABLE ct(id SERIAL, rowid TEXT, attribute TEXT, value TEXT); INSERT INTO ct(rowid, attribute, value) VALUES('test1','att1','val1'); 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);
I get:
ERROR: extension "tablefunc" already exists
But if I comment out
CREATE EXTENSION
I get:
ERROR: function crosstab(unknown) does not exist
How can I get out of this vicious circle? Is it a known issue?