postgresql 无法在 Postgres 中使用交叉表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23073037/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Unable to use crosstab in Postgres
提问by Black
Postgres 9.2.1 on OSX 10.9.2.
OSX 10.9.2 上的 Postgres 9.2.1。
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
我得到： ERROR: extension "tablefunc" already exists
But if I comment out CREATE EXTENSION
但如果我注释掉 CREATE EXTENSION
I get: ERROR: function crosstab(unknown) does not exist
我得到： ERROR: function crosstab(unknown) does not exist
How can I get out of this vicious circle? Is it a known issue?
我怎样才能摆脱这个恶性循环？这是一个已知问题吗？
回答by akbarbin
You can change the first line into:
您可以将第一行更改为：
CREATE EXTENSION IF NOT EXISTS tablefunc;
回答by Erwin Brandstetter
There's a misconceptionin your answer:
你的回答有一个误解：
and not accessible to all schemas in it.
并且无法访问其中的所有模式。
All schemasinside 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.
同一个数据库中的所有模式都可以被同一个数据库中的所有会话访问（只要有权限）。这是设置search_path. 模式的工作方式很像文件系统中的目录/文件夹。
Alternatively, you can schema-qualify the function (and even operators) to access it independently of the search_path:
或者，您可以模式限定函数（甚至运算符）以独立于 访问它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?
最近有更多信息的相关答案：
How to use % operator from the extension pg_trgm?
Dubious crosstab()
可疑 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_1and added the second parameter to crosstab() - the "safe" version. More details here:
PostgreSQL Crosstab Query
您的查询返回了属性'att2'和 'att3'，但列定义列表具有三个category_1, category_2, category_3与查询不匹配的类别 ( )。
我删除category_1了第二个参数并将其添加到 crosstab() - “安全”版本。此处有更多详细信息：
PostgreSQL 交叉表查询
Aside: Don't use valueas column name. Even if Postgres tolerates it. It's a reserved word in standard SQL.
回答by Black
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.
我的问题是“tablefunc”扩展是在我的数据库中的一个特定模式上定义的，并且不能被其中的所有模式访问。
[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
- 扩展只能加载到一个架构中 - 所以将它加载到“公共”中
- 您必须先从一个架构中手动删除扩展，然后才能将其加载到另一个架构中
- 您可以使用以下命令在 pqsl 中列出每个架构加载的扩展：
\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]
[编辑：4.您可以通过search_path，通过将其加载到公共架构上或通过明确指定架构来访问扩展]
