Postgres中无法使用交叉表

Question

Postgres 9.2.1 在 OSX 10.9.2 上。

如果我运行以下的交叉表查询示例:

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);

我得到了：错误：扩展“tablefunc”已经存在

但如果我注释掉CREATE EXTENSION，我会得到：错误：函数crosstab（未知类型）不存在

我怎样才能打破这个恶性循环？这是一个已知的问题吗？

- Black

Answer 1

你可以将第一行改为：

CREATE EXTENSION IF NOT EXISTS tablefunc;

- akbarbin

---

同时确保在执行此操作时连接到正确的数据库，否则它仍然无法正常工作。 - Iruku Kagika

Answer 2

在我的情况中，问题是'tablefunc'扩展只在数据库中的一个特定模式上定义，而不是在所有模式上都可用。

我得知：

1. 扩展只能加载到一个模式中 - 所以加载到'public'模式中。 2. 必须先手动从一个模式中删除扩展，然后才能在另一个模式中加载它。 3. 可以使用命令\df *.crosstab在pqsl中按模式列出已加载的扩展。 4. 你可以通过搜索路径、在public模式上加载或显式指定模式来访问扩展。

- Black

---

请注意，public架构只是另一个架构。如果您想默认访问它，则需要像任何其他架构一样在search_path中。 - Erwin Brandstetter

Answer 3

你的回答中存在一个误解:

而不是所有架构都可以访问。

同一数据库中的所有架构都可以被该数据库中的所有会话访问(只要已授予权限)。这是设置search_path的问题。架构的工作方式类似于文件系统中的目录/文件夹。

或者，您可以使用模式限定函数(甚至运算符)来独立访问它，而不受search_path的影响:

SELECT *
FROM <b>my_extension_schema.</b>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);

更多:

• 如何使用扩展程序pg_trgm中的%运算符？

可疑的crosstab()

您的查询返回了属性'att2'和'att3'，但列定义列表有三个类别（category_1、category_2、category_3），与查询不匹配。
我删除了category_1并添加了crosstab()的第二个参数-“安全”版本。更多详情请参见：

• PostgreSQL Crosstab Query

另外：
即使Postgres允许，也不要将value作为列名。它是标准SQL中的保留字。

- Erwin Brandstetter

---

这个（错误的？）查询直接来自文档http://www.postgresql.org/docs/9.1/static/tablefunc.html。 - Black

@Francis：嗯，这个例子试图演示使用一个参数的 crosstab() 函数的一个缺陷。如您在结果中所见，att2 最终出现在 category1，att3 出现在 category2 中，而 category3 为空。这可能不是人们通常想要的... - Erwin Brandstetter

好的。我只是用它来说明tablefunc扩展的奇怪缺失。 - Black